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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.