ASP.NET

Problem with Split Function in SQL... RSS

19 replies

Last post Mar 07, 2012 06:34 AM by Chen Yu - MSFT

‹ Previous Thread|Next Thread ›

- Reply
jeeten09

Member

122 Points

150 Posts

Problem with Split Function in SQL...

Mar 02, 2012 06:03 AM|LINK

Hello All,

I have database column values like ('1-Jan-11;29-Sep-11','22-Jun-11')...

So I have used Split function for ';' & Then trying to pass it in Stored Procedure...

Actually I am displaying Gridview by this Stored Procedure, So getting errors ::

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Anyone Please help me...

Thank you...
- Marked as answer by jeeten09 on Mar 27, 2012 03:55 AM

Reply

yrb.yogi

Star

14460 Points

Re: Problem with Split Function in SQL...

Mar 02, 2012 06:05 AM|LINK

To Split data use below split function..

CREATE FUNCTION [dbo].[SplitString](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
	declare @idx int
	declare @slice varchar(8000)
    
	select @idx = 1
		if len(@String)<1 or @String is null  return
    
	while @idx!= 0
	begin
		set @idx = charindex(@Delimiter,@String)
		if @idx!=0
			set @slice = left(@String,@idx - 1)
		else
			set @slice = @String
		
		if(len(@slice)>0)
			insert into @temptable(Items) values(@slice)

		set @String = right(@String,len(@String) - @idx)
		if len(@String) = 0 break
	end
return
end

GO
--now call your parameter as below

select * From dbo.SplitString('1-Jan-11;29-Sep-11;22-Jun-11',';')

--output
--items
--1-Jan-11
--29-Sep-11
--22-Jun-11

for error resolution, post your store procedure query..

Yogesh Bhadauriya
.Net All About
MCC

- Reply
jeeten09

Member

122 Points

150 Posts

Re: Problem with Split Function in SQL...

Mar 02, 2012 06:58 AM|LINK

yrb.yogi
To Split data use below split function..

I have this Split Function Already...

My Stored Procedure :::

ALTER PROCEDURE [dbo].[proc_SelectProjectDetails]

    @PS_Due_dateFrom as datetime,
   @PS_Due_dateTo as datetime,

AS
BEGIN
   SELECT

    Question, Q_Attach_File, Q_Date, Response, R_Attach_File, R_Date, Status, S_Date From tblClarification Where (R_Date >= @Response_dateFrom) AND (R_Date <= @Response_dateTo)

END

R_Date is the column about which im talking...

Instead of R_Date, I used (SELECT Res_Date FROM tblClarification CROSS APPLY dbo.SplitForDate(R_Date))

But got error; Conversion failed when converting date and/or time from character string.
- Edited by jeeten09 on Mar 02, 2012 07:00 AM

Reply

yrb.yogi

Star

14460 Points

Re: Problem with Split Function in SQL...

Mar 02, 2012 07:01 AM|LINK

SELECT Res_Date
FROM tblClarification
CROSS APPLY dbo.SplitForDate(CAST(R_Date AS VARCHAR(100))) --pass delimiter
--if your function require

Yogesh Bhadauriya
.Net All About
MCC

- Reply
jeeten09

Member

122 Points

150 Posts

Re: Problem with Split Function in SQL...

Mar 02, 2012 07:04 AM|LINK

yrb.yogi
SELECT Res_Date FROM tblClarification CROSS APPLY dbo.SplitForDate(CAST(R_Date AS VARCHAR(100))) --pass delimiter --if your function require

Nope, Same Error...
- Reply
yrb.yogi

Star

14460 Points

2402 Posts

Re: Problem with Split Function in SQL...

Mar 02, 2012 07:07 AM|LINK

jeeten09
Nope, Same Error...

can you show Res_Date column data?

Yogesh Bhadauriya
.Net All About
- Reply
jeeten09

Member

122 Points

150 Posts

Re: Problem with Split Function in SQL...

Mar 02, 2012 07:11 AM|LINK

yrb.yogi
can you show Res_Date column data?

Res_Date data is coming from the Split Function...

But R_Date are shown as below :::

   Unique_Number             R_date

        GJ_1                  1-Sep-11;9-Sep-11;

      GJ_2                  10-Dec-11;12-Dec-11;

      GJ_3                   9-Jan-12;

Reply

yrb.yogi

Star

14460 Points

Re: Problem with Split Function in SQL...

Mar 02, 2012 07:31 AM|LINK

declare @table table
(
	Unique_Number varchar(100),R_date VARCHAR(100)
)
INSERT INTO @table
select 'GJ_1','1-Sep-11;9-Sep-11;' union all
select 'GJ_2','10-Dec-11;12-Dec-11;' union all
select 'GJ_3','9-Jan-12;'

 
select Unique_Number,R_date,items As SplitedR_date
From @table
CROSS APPLY dbo.SplitString(R_date,';')

--output
--Unique_Number R_date SplitedR_date
--GJ_1 1-Sep-11;9-Sep-11; 1-Sep-11
--GJ_1 1-Sep-11;9-Sep-11; 9-Sep-11
--GJ_2 10-Dec-11;12-Dec-11; 10-Dec-11
--GJ_2 10-Dec-11;12-Dec-11; 12-Dec-11
--GJ_3 9-Jan-12; 9-Jan-12

Yogesh Bhadauriya
.Net All About
MCC

- Reply
jeeten09

Member

122 Points

150 Posts

Re: Problem with Split Function in SQL...

Mar 02, 2012 07:34 AM|LINK

yrb.yogi
declare @table table ( Unique_Number varchar(100),R_date VARCHAR(100) ) INSERT INTO @table select 'GJ_1','1-Sep-11;9-Sep-11;' union all select 'GJ_2','10-Dec-11;12-Dec-11;' union all select 'GJ_3','9-Jan-12;' select Unique_Number,R_date,items As SplitedR_date From @table CROSS APPLY dbo.SplitString(R_date,';') --output --Unique_Number R_date SplitedR_date --GJ_1 1-Sep-11;9-Sep-11; 1-Sep-11 --GJ_1 1-Sep-11;9-Sep-11; 9-Sep-11 --GJ_2 10-Dec-11;12-Dec-11; 10-Dec-11 --GJ_2 10-Dec-11;12-Dec-11; 12-Dec-11 --GJ_3 9-Jan-12; 9-Jan-12

Thats I know But I am doing Inner Join of 6 Tables & There are very large number of Parameters So Its not possible for me to do like that...

Even I use code like which u told though I am getting error of conversion...
- Edited by jeeten09 on Mar 02, 2012 07:37 AM

Reply

yrb.yogi

Star

14460 Points

Re: Problem with Split Function in SQL...

Mar 02, 2012 07:38 AM|LINK

I am showing you a simple demo of how to use split data...

you just need to cast your splitdata to datetime as follow..and use that column for join..

declare @table table
(
	Unique_Number varchar(100),R_date VARCHAR(100)
)
INSERT INTO @table
select 'GJ_1','1-Sep-11;9-Sep-11;' union all
select 'GJ_2','10-Dec-11;12-Dec-11;' union all
select 'GJ_3','9-Jan-12;'

 
select Unique_Number,R_date,CAST(items AS DATETIME) As DataInDate
From @table
CROSS APPLY dbo.SplitString(R_date,';')

--output
--Unique_Number R_date DataInDate
--GJ_1 1-Sep-11;9-Sep-11; 1-Sep-11
--GJ_1 1-Sep-11;9-Sep-11; 9-Sep-11
--GJ_2 10-Dec-11;12-Dec-11; 10-Dec-11
--GJ_2 10-Dec-11;12-Dec-11; 12-Dec-11
--GJ_3 9-Jan-12; 9-Jan-12

Yogesh Bhadauriya
.Net All About
MCC

Prev Next

1 2 Last

‹ Previous Thread|Next Thread ›