Split function in sp sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

SQL

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 19-Dec-12 2:04am

Member 7767311

Add a Solution

3 solutions

Add a Solution

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

Aarti Meswania · Answer 1 · 2012-12-19T03:07:00

Solution 1

Use below function

SQL

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

SQL

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!
:)

Posted 19-Dec-12 3:07am

Aarti Meswania

Updated 19-Dec-12 23:05pm

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 :)

KedarA · Answer 2 · 2013-06-05T05:10:00

Solution 2

http://sandipgsql.blogspot.in/2013/06/split-funtions-in-sql-server.html[^]

Posted 5-Jun-13 5:10am

KedarA

Maciej Los · Answer 3 · 2013-06-05T12:02:00

Have a look at example (using CTE[^]):

SQL

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

Split function in sp sql server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Split function in sp sql server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us