I was working on some code to parse some data for a friend, and had
to write this little piece of code that some of you might find
useful. Right now, you can just take this, pop it into query
analyzer and run it. Its simple enough to change this to a
function that accepts the string, plus a delimeter value, such as:
CREATE FUNCTION dbo.ParseString (@string varchar(500), @delimeter char(1)) RETURNS table
Or do the same type thing using a stored procedure, whatever floats
your boat. You’ll juse have to make some small modifications to
the code.
i took some of your code and i made my own function i hope somebady like it.
CREATE FUNCTION Split(
@IDValues as varchar(500),
@Delimeter as varchar(1)
)
RETURNS @tValues TABLE(IDAux INT)
AS
BEGIN
DECLARE @I AS INT, @Aux AS INT
SET @I = 1
SET @Aux = 1
WHILE @I <= LEN(@IDValues)
BEGIN
IF SUBSTRING(@IDValues,@I,1)= @Delimeter
BEGIN
INSERT INTO @tValues (IDAux) VALUES (CAST(SUBSTRING(@IDValues,@Aux,(@I-@Aux))AS INT))
SET @Aux = @i + 1
END
SET @i = @i + 1
END
RETURN
END
GO
–examples with differents delimeters
select * from Split(’1058,1059,1060,1062,’,',’)
select * from Split(’1058|1059|1060|1062|’,'|’)
select * from Split(’1058;1059;1060;1062;’,';’)
Thanks. I had code that I wrote in the past that did this very thing, but I was in a crunch and didn’t have time to either find my old code or write it again from scratch. Thanks for saving me the time!
Great stuff but now I need to get the pased results into their own variables. Of course, I don’t have an infinite number of parts in the string-to-parse. Here are six example strings to be parsed that has 0 – 5 parts:
Please provide an example of how one would use this function to set variables for each of the parsed values. For example, if the string to parse was “ABC-123-DEF” how would we get the values into their own variables? For my requirements, there will be a constant maximum number of parts to parse from the string; not infinite. That value is 4 and the logic should handle these string examples:
(null – no value passed)
ABC
ABC-123
ABC-123-DEC
ABC-123-DEC-XYZ
I took his code and tweaked it some, dbo.Numbers is a simple standalone table with numbers from 1 to 1 million.
–Fast Split function using a sequence table (dbo.Numbers)
–Created 08/29/05 by Oskar Austegard from Erland Sommarskog’s code at
–http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
CREATE FUNCTION Split (
@List varchar(7998), –The delimited list
@Del char(1) = ‘,’ –The delimiter
)
RETURNS @T TABLE (Item varchar(7998))
AS
BEGIN
DECLARE @WrappedList varchar(8000), @MaxItems int
SELECT @WrappedList = @Del + @List + @Del, @MaxItems = LEN(@List)
INSERT INTO @T (Item)
SELECT SUBSTRING(@WrappedList, Number + 1, CHARINDEX(@Del, @WrappedList, Number + 1) – Number – 1)
FROM dbo.Numbers n
WHERE n.Number <= LEN(@WrappedList) – 1
AND SUBSTRING(@WrappedList, n.Number, 1) = @Del
I’m just partial to using PATINDEX because I’m used to using wildcard characters in my search strings, which CHARINDEX does not support. You’re way works just fine too in this exact scenario.
However, with PATINDEX, you can use the delimeter of “,_,”, which would exclude single char pieces of data from your result set. You cannot do this with CHARINDEX.
This following piece of code shows the value of using PATINDEX
declare @a varchar(100)
set @a = ‘abcdefgh,ijklm,n,opqrs,tuv’
print patindex(‘%,_,%’,@a)
Don`t want to be picky here (or do I ? , but is there any reason why you use patindex?
This works too:
– [...]
set @pos = charindex(‘,’ , @string)
while @pos <> 0
begin
set @piece = left(@string, @pos – 1)
print cast(@piece as varchar(500))
set @string = stuff(@string, 1, @pos, ”)
set @pos = charindex(‘,’ , @string)
end
You can try the following approach using XML. However still on works for SQL Server 2008:
CREATE FUNCTION dbo.fnToDelimitedTable(@Value varchar(8000), @Delimiter nvarchar(1) = ‘,’)‘)‘ + @Value + ‘
RETURNS @TableValues table
(
DataRow nvarchar(4000)
,Id bigint identity(1,1))
AS
BEGIN
DECLARE @data xml
SET @Value = REPLACE(@Value, @Delimiter, ‘
SET @Value = ‘
SET @data = @Value
INSERT @TableValues (DataRow)
SELECT
DataRows.DataRow.value(‘/DataRows[1]/DataRow[1]‘, ‘nvarchar(4000)’)
FROM @data.nodes(‘DataRows[1]/DataRow’) DataRows(DataRow)
RETURN
END
GO
who cares
Nice captcha
Worked great! Thanks!
I like it. Easy and smart for short strings!
Thanks alot…
Thank you, just what i was looking for.
Kudo’s
Thanks for the simple solution.
Totally awesome. I threw this into a cursor and was able to parse a whole table 1 row at a time. Saved me so much time.
f1e9ks
Just what I needed to get the file name from a filename and path, thanks
thanks alot this worked for me
Raymond — Thanks!
This is just what I was looking for; easy to understand, and effective.
Since I’ll be using this for Sybase ASE 12.5, the only change I might have to make is:
From: set @string = stuff(@string, 1, @pos, ”)
To: set @string = stuff(@string, 1, @pos, null)
Per Sybase 12.5:
“Adaptive Server converts an empty string constant to a string of 1 space”
Thanks again!
Raj
i took some of your code and i made my own function i hope somebady like it.
CREATE FUNCTION Split(
@IDValues as varchar(500),
@Delimeter as varchar(1)
)
RETURNS @tValues TABLE(IDAux INT)
AS
BEGIN
DECLARE @I AS INT, @Aux AS INT
SET @I = 1
SET @Aux = 1
WHILE @I <= LEN(@IDValues)
BEGIN
IF SUBSTRING(@IDValues,@I,1)= @Delimeter
BEGIN
INSERT INTO @tValues (IDAux) VALUES (CAST(SUBSTRING(@IDValues,@Aux,(@I-@Aux))AS INT))
SET @Aux = @i + 1
END
SET @i = @i + 1
END
RETURN
END
GO
–examples with differents delimeters
select * from Split(’1058,1059,1060,1062,’,',’)
select * from Split(’1058|1059|1060|1062|’,'|’)
select * from Split(’1058;1059;1060;1062;’,';’)
Thank you very much it work just fine.
Thanks. I had code that I wrote in the past that did this very thing, but I was in a crunch and didn’t have time to either find my old code or write it again from scratch. Thanks for saving me the time!
Thanks. Worked for me.
Thanks.
Good stuff.
Thanks!
Great stuff but now I need to get the pased results into their own variables. Of course, I don’t have an infinite number of parts in the string-to-parse. Here are six example strings to be parsed that has 0 – 5 parts:
1. null
2. ABC
3. ABC-123
4. ABC-123-DEF
5. ABC-123-DEF-789
6. ABC-123-DEF-789-XYZ
I would like to get the results into variables such as:
@v_parsed_1
@v_parsed_2
@v_parsed_3
@v_parsed_4
@v_parsed_5
where, in the 4th example above, would result in:
@v_parsed_1 = ‘ABC’
@v_parsed_2 = ’123′
@v_parsed_3 = ‘DEF’
@v_parsed_4 = null
@v_parsed_5 = null
Thanks in advance!
Please provide an example of how one would use this function to set variables for each of the parsed values. For example, if the string to parse was “ABC-123-DEF” how would we get the values into their own variables? For my requirements, there will be a constant maximum number of parts to parse from the string; not infinite. That value is 4 and the logic should handle these string examples:
(null – no value passed)
ABC
ABC-123
ABC-123-DEC
ABC-123-DEC-XYZ
Thanks in advance!
Many thanks!
Thank you.
Just what I was looking for.
Great job.
B
Thanks, this worked for me to parse my postcodes.
…and just what I needed as well. Thanks!
Thanks, just what I needed….
old post, but i just stole your code. works perfectly – patindex or not.
You may also want to have a look at Erland Sommarskog’s article at http://www.sommarskog.se/arrays-in-sql.html.
I took his code and tweaked it some, dbo.Numbers is a simple standalone table with numbers from 1 to 1 million.
–Fast Split function using a sequence table (dbo.Numbers)
–Created 08/29/05 by Oskar Austegard from Erland Sommarskog’s code at
–http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
CREATE FUNCTION Split (
@List varchar(7998), –The delimited list
@Del char(1) = ‘,’ –The delimiter
)
RETURNS @T TABLE (Item varchar(7998))
AS
BEGIN
DECLARE @WrappedList varchar(8000), @MaxItems int
SELECT @WrappedList = @Del + @List + @Del, @MaxItems = LEN(@List)
INSERT INTO @T (Item)
SELECT SUBSTRING(@WrappedList, Number + 1, CHARINDEX(@Del, @WrappedList, Number + 1) – Number – 1)
FROM dbo.Numbers n
WHERE n.Number <= LEN(@WrappedList) – 1
AND SUBSTRING(@WrappedList, n.Number, 1) = @Del
RETURN
END
David,
I’m just partial to using PATINDEX because I’m used to using wildcard characters in my search strings, which CHARINDEX does not support. You’re way works just fine too in this exact scenario.
However, with PATINDEX, you can use the delimeter of “,_,”, which would exclude single char pieces of data from your result set. You cannot do this with CHARINDEX.
This following piece of code shows the value of using PATINDEX
declare @a varchar(100)
set @a = ‘abcdefgh,ijklm,n,opqrs,tuv’
print patindex(‘%,_,%’,@a)
Will print ’15′.
Hi Raymond,
Don`t want to be picky here (or do I ?
, but is there any reason why you use patindex?
This works too:
– [...]
set @pos = charindex(‘,’ , @string)
while @pos <> 0
begin
set @piece = left(@string, @pos – 1)
print cast(@piece as varchar(500))
set @string = stuff(@string, 1, @pos, ”)
set @pos = charindex(‘,’ , @string)
end