Introduction:
Here I will explain simple split function in SQL
Server to split comma separated string into table values in SQL
Server database or How to split comma separated string with custom split() function
in SQL Server.
Description:
In previous articles I explained Pass table as parameter to stored procedure in SQL Server, Different types of joins in SQL Server, Difference between len & datalength functions in SQL Server,
convert rows to columns in sql server without using pivot, Create database schema diagrams in SQL Server, Delete Duplicate records from SQL Server and many articles
relating to SQL Server. Now I will explain how to create split function
in SQL Server.
To split comma separated string in SQL
Server we need to write custom method for that we need to create one
function like as shown below
Custom
Split function to split comma separated string into table
CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT
EQUAL Z
-- ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST
OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE
LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX
- 1)
ELSE
SELECT @SLICE =
@STRING
-- PUT THE ITEM INTO THE RESULTS
SET
INSERT INTO
@Results(Items)
VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE
MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
|
Once we create custom function Split() run sample query like as shown below
SELECT items FROM [dbo].[Split] ('861,739,10,1670', ',')
|
Once we run above query we will get output like as
shown below
Output
 |
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
13 comments :
Nice work
But How to add Those Numbers By comma seperated
I have a checkboxlist for and based on the selected items in the first checkboxlist I want to display cities. For example if user has selected U.P. n Haryana then cities of both states should be displayed on city checkboxlist.. Please reply as soon as possible.
warm regards
very helpfull..Thank u
very helpful...
hi... i have 10 items in my form each item have 10 checkbox. now i checked the checbox the values are stored in database table like this 1,2,3.then i want the checked box are disabled after value stored tothe particular ids
what to do ???? helpme....
See This one so simple I hope you would like this logic
alter function CustomSplit(@String nvarchar(max),@dilmiter nvarchar(1))
RETURNS @Results TABLE (Items nvarchar(4000))
begin
declare @index1 int
declare @temp1 nvarchar(max)
while CHARINDEX(@dilmiter,@String)<>0
begin
select @index1=CHARINDEX(@dilmiter,@String )
select @temp1=SUBSTRING(@String ,1,@index1-1)
--select @temp1
insert into @Results values(@temp1)
set @String=REPLACE(@String,@temp1+@dilmiter,'')
if(CHARINDEX(@dilmiter,@String)=0)
begin
insert into @Results values(@String)
end
end
return
end
select * from dbo.CustomSplit('111,112,122,133,126',',')
HI, you don't by any change have a function to split a column in a table with multiple entries (delimited) into multiple columns. The data looks something like this:
(10:00~11:00~12:00). Needs to be dynamic as values can be from 1 to max 30 .
Regards
I have a table like this
Id FullName
-------------------
1 Cleo,Smith,james
I want to separate the comma delimited string into two columns
Id FullName Name Surname Last
--- ------------ ------- ----------- ------
1 Cleo,Smith Cleo Smith james
How can i do this?
Give me query for this?
nice
thx budy its works for me
how to seperate multiple varibale at the same time
nice
How we can split string into column wise without using pivot. I have code with pivot, but its taking longer time for larger records till (1 lac). Can u give me some solution.
Note: Only a member of this blog may post a comment.