Click here to Skip to main content
14,265,748 members
Rate this:
Please Sign up or sign in to vote.
How to use split function in SP..Bleow is my sp i need to split the value of complexion column where the value of complexion is (2,5,) in my table i need to split as 2 and 5 seperately...find any soln

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [C244570_marriage].[sp_fillexpectation]
@CustomerId nvarchar(max)
as
begin
--select * from customerExpectation where CustomerId='d14a96ac-0527-41c6-9ecb-eaeb7545561e'
select * from  customerexpectation ce
left outer join familystatus fs on ce.familystatus1=fs.id
left outer join complexion cp on ce.complexion1=cp.id
left outer join familytype ft on ce.familytype1=ft.id
left outer join foodhabit fh on ce.foodhabits=fh.id
left outer join education e on ce.qualification=e.id

where  ce.CustomerId=@CustomerId
end
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 1

Use below function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
    DECLARE @Value varchar(100)
    WHILE @String is not null
    BEGIN
        SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
        INSERT INTO @RESULTS (Val)
        SELECT @Value
    END
RETURN
END
------------------------------------------------------------------------------
--function call
select * from [dbo].[ParseValues]('2,5,',',') where val <>''


use query as below...
with custexp as 
(
select c.*, pv.val as Comp1 from customerexpectation c
cross apply dbo.ParseValues(complexion1,',') as pv
where len(pv.val)>0
)
select * from CustExp ce
left outer join familystatus fs on ce.familystatus1=fs.id
left outer join complexion cp on ce.complexion1=cp.id
left outer join familytype ft on ce.familytype1=ft.id
left outer join foodhabit fh on ce.foodhabits=fh.id
left outer join education e on ce.qualification=e.id

Happy Coding!
:)
   
v3
Comments
Member 7767311 20-Dec-12 4:24am
   
Hi Aarti Meswania.. How to call this above function to stored procedure
Aarti Meswania 20-Dec-12 4:26am
   
this is your Query

select * from customerexpectation ce
left outer join familystatus fs on ce.familystatus1=fs.id
left outer join complexion cp on ce.complexion1=cp.id
left outer join familytype ft on ce.familytype1=ft.id
left outer join foodhabit fh on ce.foodhabits=fh.id
left outer join education e on ce.qualification=e.id

but which field and in which table you want to split???
Member 7767311 20-Dec-12 4:30am
   
fields are complexion id,complexion from complexion table and complexion1 from customerexpectation table....i hope it's clear..
Aarti Meswania 20-Dec-12 4:36am
   
you mean to say
in below line...
left outer join complexion cp on ce.complexion1=cp.id

complexion1 have value, e.g '2,5'
and
customerexpectation.id have value, e.g 2
now you want to split complexion1 and join it to id of customerexpectation???

If I am getting it right then tell me...

Member 7767311 20-Dec-12 4:38am
   
Yes Exactly You are right
Aarti Meswania 20-Dec-12 4:54am
   
okay :)
see updated solution please
there is sqlquery.
Member 7767311 20-Dec-12 5:04am
   
I am getting an error while exec this SP ..Pls modify it accordingly i have created function also..

Error:-The multi-part identifier "pv.val" could not be bound.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [C244570_marriage].[sp_fillexpectation]
@CustomerId nvarchar(max)
as
begin
with custexp as
(
select c.*, pv.val as Comp1 from customerexpectation c
cross apply dbo.ParseValues(complexion1,',') as a
where len(a.val)>0
)
select * from CustExp ce
left outer join familystatus fs on ce.familystatus1=fs.id
left outer join complexion cp on ce.complexion1=cp.id
left outer join familytype ft on ce.familytype1=ft.id
left outer join foodhabit fh on ce.foodhabits=fh.id
left outer join education e on ce.qualification=e.id
where ce.CustomerId=@CustomerId
--select * from customerexpectation ce
--left outer join familystatus fs on ce.familystatus1 like fs.id
--left outer join complexion cp on ce.complexion1 like cp.id
--left outer join familytype ft on ce.familytype1 like ft.id
--left outer join foodhabit fh on ce.foodhabits like fh.id
--left outer join education e on ce.qualification like e.id
--where ce.CustomerId=@CustomerId
end


Aarti Meswania 20-Dec-12 5:06am
   
it was wrong alias
try updated solution sql-query
Aarti Meswania 20-Dec-12 5:24am
   
if it worked
then
Mark it as answer :)
Rate this:
Please Sign up or sign in to vote.

Solution 3

Have a look at example (using CTE[^]):
--example data
DECLARE @data TABLE(ID INT IDENTITY(1,1), SomeData NVARCHAR(30))

INSERT INTO @data (SomeData)
VALUES('A,B,C,')
INSERT INTO @data (SomeData)
VALUES('D,E,F,G,')
INSERT INTO @data (SomeData)
VALUES('H,I,')
INSERT INTO @data (SomeData)
VALUES('J,K,L,M,')
INSERT INTO @data (SomeData)
VALUES('N,O,P,')
INSERT INTO @data (SomeData)
VALUES('Q,R,S,')
INSERT INTO @data (SomeData)
VALUES('T,U,V,')
INSERT INTO @data (SomeData)
VALUES('X,Y,Z,')

--temporary table to store splited data
DECLARE @tmp TABLE (ID INT, LETTER NVARCHAR(1))

--using CTE split data
;WITH CTE AS
(
	SELECT ID, SomeData, LEFT(SomeData, CHARINDEX(',', SomeData)-1) AS Letter, RIGHT(SomeData, LEN(SomeData) - CHARINDEX(',', SomeData)) AS Remainder
	FROM @data
	WHERE CHARINDEX(',', SomeData)>1
	UNION ALL
	SELECT ID, SomeData, LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS Letter, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM CTE
	WHERE CHARINDEX(',', Remainder)>1
)
INSERT INTO @tmp (ID, Letter)
SELECT ID, Letter
FROM CTE
ORDER BY ID, Letter
--OPTION (MAXRECURSION 0)

--display result ;)
SELECT *
FROM @tmp


Result:
ID      Letter
1	A
1	B
1	C
2	D
2	E
2	F
2	G
3	H
3	I
4	J
4	K
4	L
4	M
5	N
5	O
5	P
6	Q
6	R
6	S
7	T
7	U
7	V
8	X
8	Y
8	Z


Remember, similar query will be proper only if complexion field conatain (comma) as a last sign in a field.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100