TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
How to Split Comma Separated Values in SQL Server
Amit Shrivastava
Sep 12
2014
Code
2.7
k
0
2
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
Create
FUNCTION
[dbo].[Split_Function]
(@String
varchar
(
MAX
),@ImageNameByUser
varchar
(
MAX
)=
null
, @Delimiter
char
(1),@QuestionId
bigint
,@ID1
int
)
returns
@temptable
TABLE
(RightOptions
varchar
(
MAX
),NotInUse
varchar
(
MAX
),QuestionId
bigint
,QuestionOptionId
bigint
,RightAnswerId
bigint
)
as
begin
declare
@idx
int
declare
@slice
varchar
(8000)
,@slice2
varchar
(8000)
,@idx2
int
select
@idx = 1
select
@idx2 = 1
if len(@String)<1
or
@String
is
null
return
while @idx!= 0
begin
set
@idx = charindex(@Delimiter,@String)
set
@idx2 = charindex(@Delimiter,@ImageNameByUser)
if @idx!=0
set
@slice =
left
(@String,@idx - 1)
else
set
@slice = @String
if @idx2!=0
set
@slice2 =
left
(@ImageNameByUser,@idx2 - 1)
else
set
@slice2 = @ImageNameByUser
--If(IsNull(@ImageNameByUser,'@@')!='@@')
--if(len(@slice)>0 And len(@slice2)>0 )
--Else
if(len(@slice)>0)
BEGIN
SET
@ID1=@ID1+1
declare
@RightAnsId
bigint
Select
@RightAnsId=
isnull
(QuestionOptionId,0)
from
tblQuestionOption
where
OptionDetail = LTRIM(RTRIM(@slice))
And
QuestionId=@QuestionId
insert
into
@temptable(RightOptions,NotInUse,QuestionId,QuestionOptionId,RightAnswerId)
values
(LTRIM(RTRIM(@slice)),LTRIM(RTRIM(@slice2)),@QuestionId,@RightAnsId,@ID1)
END
set
@String =
right
(@String,len(@String) - @idx)
if len(@String) = 0 break
set
@ImageNameByUser =
right
(@ImageNameByUser,len(@ImageNameByUser) - @idx2)
if len(@ImageNameByUser) = 0 break
end
return
end
Learn SQL Server
Split Comma Separated Values in SQL Server