Problem with Split Function in SQL...

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...

Re: Problem with Split Function in SQL...

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..

Re: Problem with Split Function in SQL...

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.

Re: Problem with Split Function in SQL...

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

Re: Problem with Split Function in SQL...

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...

Re: Problem with Split Function in SQL...

jeeten09
Nope, Same Error...

can you show Res_Date column data?

Re: Problem with Split Function in SQL...

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;

Re: Problem with Split Function in SQL...

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

Re: Problem with Split Function in SQL...

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...

Re: Problem with Split Function in SQL...

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

Re: Problem with Split Function in SQL...

yrb.yogi

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..

I tried it but not able to solve same error...

Re: Problem with Split Function in SQL...

jeeten09
I tried it but not able to solve same error...

You must need to cast your varchar column to datetime. Check weather your varchar column contain valid datetime value..like MM/DD/YYYY etc.

for better, you must check in where cluase IsDate(yourColumn)=1 to get proper datetime..

Re: Problem with Split Function in SQL...

yrb.yogi

You must need to cast your varchar column to datetime. Check weather your varchar column contain valid datetime value..like MM/DD/YYYY etc.

for better, you must check in where cluase IsDate(yourColumn)=1 to get proper datetime..

Im using Date Format as dd-MMM-yyyy & You can see it in the data as i gave u before...

Re: Problem with Split Function in SQL...

jeeten09
Im using Date Format as dd-MMM-yyyy & You can see it in the data as i gave u before...

That is the reason you got error..

Set Dateformat to dd-mm-yyyy

like below..

SET DATEFORMAT DMY;

--rest of query will come here

SET DATEFORMAT MDY;

Re: Problem with Split Function in SQL...

yrb.yogi

Set Dateformat to dd-mm-yyyy

like below..
SET DATEFORMAT DMY;

--rest of query will come here

SET DATEFORMAT MDY;

Can i set dateformat in Query only ???

How ???

Or anyother easy way to do it ???

Re: Problem with Split Function in SQL...

jeeten09

Can i set dateformat in Query only ???

How ???

Or anyother easy way to do it ???

You can set dateformat in query too.

i think this is easy way..

Re: Problem with Split Function in SQL...

yrb.yogi

You can set dateformat in query too.

i think this is easy way..

huh ?

still same here...

Re: Problem with Split Function in SQL...

jeeten09

huh ?

still same here...

Now you must need to show your entire query here...and where & which column you are getting error

Re: Problem with Split Function in SQL...

yrb.yogi
Now you must need to show your entire query here...and where & which column you are getting error

msg me ur email id

Re: Problem with Split Function in SQL...

Hi jeeten09,

Have you solved your problem? If not, please post your whole query, it will provide us more information about your issue. Thanks for your understanding.

Thanks.