March 2, 2010 at 6:55 am
I am trying to figure out how to split one single field that has data with commas into multiple fields. Ideally, this is something I would need to do in a stored procedure. I have an nvarchar(255). I tried using a combination of left, right and mid, but the positions aren't consistant.
March 2, 2010 at 8:35 am
You should look for split string functions on this site.
There are numerous solutions available.
One pretty fast solution is described at the end of the Tally Table article referenced in my signature.
You also might look for a CLR solution, if you're familiar with CLR programming.
March 2, 2010 at 8:44 am
Here is one way to solve the problem:
declare @STR varchar(200)
set @STR = 'Apple, Banana,Orange, Pinapple, Lemon'
declare @result table (string varchar(100))
declare @start int
declare @end int
set @start = 1
set @end = charindex(',', @STR) - 1 -- Find first comma
while @end > 0
begin
insert into @result
select ltrim(substring(@str, @start, @end - @start + 1))
set @start = @end + 2 -- We want to skip the comma, hence add to the position after
set @end = charindex(',', @STR, @start + 1) - 1 -- Skip comma, subtract one to avoid it in the end
end
select *
from @result
/Markus
March 2, 2010 at 10:11 am
Hunterwood (3/2/2010)
Here is one way to solve the problem:
declare @STR varchar(200)set @STR = 'Apple, Banana,Orange, Pinapple, Lemon'
declare @result table (string varchar(100))
declare @start int
declare @end int
set @start = 1
set @end = charindex(',', @STR) - 1 -- Find first comma
while @end > 0
begin
insert into @result
select ltrim(substring(@str, @start, @end - @start + 1))
set @start = @end + 2 -- We want to skip the comma, hence add to the position after
set @end = charindex(',', @STR, @start + 1) - 1 -- Skip comma, subtract one to avoid it in the end
end
select *
from @result
/Markus
Well, Markus, that's one way to do it...
But I think you should read the article I referred to in my prev. post, too...
The WHILE loop approach performs almost as bad as (or even equivalent to) a cursor solution. There are much better solutions available. And easy to find on this side, too... 😉
March 2, 2010 at 10:57 am
lmu92 (3/2/2010)
You should look for split string functions on this site.There are numerous solutions available.
One pretty fast solution is described at the end of the Tally Table article referenced in my signature.
You also might look for a CLR solution, if you're familiar with CLR programming.
Ok, since I am a total moron, can you dumb down the Tally Table for me? Is the number of records in a tally table equate to the number of records in the table I want to parse (40,000), or the number of fields I think I will need to parse out a single record (6)? Once I create the Tally Table, can I run this as a stored procedure in SQL 2005?
March 2, 2010 at 11:24 am
david.pelizzari (3/2/2010)
lmu92 (3/2/2010)
You should look for split string functions on this site.There are numerous solutions available.
One pretty fast solution is described at the end of the Tally Table article referenced in my signature.
You also might look for a CLR solution, if you're familiar with CLR programming.
Ok, since I am a total moron, can you dumb down the Tally Table for me? Is the number of records in a tally table equate to the number of records in the table I want to parse (40,000), or the number of fields I think I will need to parse out a single record (6)? Once I create the Tally Table, can I run this as a stored procedure in SQL 2005?
The Tally (or numbers) table contains of a column that includes consecutive numbers. You'd need as many numbers in there to match the longest character you want to split.
I can't explain it better than Jeff did in the section "Going all the way... do the Split" in the article I referred to.
March 2, 2010 at 2:20 pm
david.pelizzari (3/2/2010)
lmu92 (3/2/2010)
You should look for split string functions on this site.There are numerous solutions available.
One pretty fast solution is described at the end of the Tally Table article referenced in my signature.
You also might look for a CLR solution, if you're familiar with CLR programming.
Ok, since I am a total moron, can you dumb down the Tally Table for me? Is the number of records in a tally table equate to the number of records in the table I want to parse (40,000), or the number of fields I think I will need to parse out a single record (6)? Once I create the Tally Table, can I run this as a stored procedure in SQL 2005?
The Tally table only needs the same number of rows as the maximum width +2 of the "field" you want to split. That's also covered in the article in the section where I explain why I made my Tally table 11,000 rows long.
Like Lutz said, anything more that I say here would be a regurgitation of the article. Go through the examples in article... the light will come on.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2010 at 1:49 am
Imu, I didn't see your post until after I posted my solution..
From now on, I will look for a Tally-solution first, before trying with a loop (or cursor)... 😉
/Markus
March 3, 2010 at 4:07 am
it may one of solution:
DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = '1,2,3,6,7,8,R,a,n,g,a,n,a,t,h',@delimiter = ','
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
SELECT @textXML
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
March 3, 2010 at 5:50 am
Mohamed Asane (3/3/2010)
it may one of solution:
DECLARE @data NVARCHAR(MAX),@delimiter NVARCHAR(5)
SELECT @data = '1,2,3,6,7,8,R,a,n,g,a,n,a,t,h',@delimiter = ','
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
SELECT @textXML
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
I agree... it's a solution that works. Try it on a couple of hundred thousand rows and see if you like the performance, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2010 at 6:16 am
Hunterwood (3/2/2010)
Here is one way to solve the problem:
declare @STR varchar(200)set @STR = 'Apple, Banana,Orange, Pinapple, Lemon'
declare @result table (string varchar(100))
declare @start int
declare @end int
set @start = 1
set @end = charindex(',', @STR) - 1 -- Find first comma
while @end > 0
begin
insert into @result
select ltrim(substring(@str, @start, @end - @start + 1))
set @start = @end + 2 -- We want to skip the comma, hence add to the position after
set @end = charindex(',', @STR, @start + 1) - 1 -- Skip comma, subtract one to avoid it in the end
end
select *
from @result
/Markus
THis one will work better as above will skip the last word ( here "lemon")
SET STATISTICS IO ON
declare @STR varchar(200)
set @STR = 'Apple, Banana,Orange, Pinapple, Lemon'
declare @result table (string varchar(100))
declare @start int
declare @end int
set @start = 1
set @end = charindex(',', @STR) - 1 -- Find first comma
while @end > 0
begin
insert into @result
select ltrim(substring(@str, @start, @end - @start + 1))
set @start = @end + 2 -- We want to skip the comma, hence add to the position after
set @end = charindex(',', @STR, @start + 1) - 1 -- Skip comma, subtract one to avoid it in the end
end
select *
from @result
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 3, 2010 at 6:28 am
Heh... you really really don't won't to use even well written While Loops for this though.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2010 at 8:21 am
david.pelizzari (3/2/2010)
I am trying to figure out how to split one single field that has data with commas into multiple fields. Ideally, this is something I would need to do in a stored procedure. I have an nvarchar(255). I tried using a combination of left, right and mid, but the positions aren't consistant.
Performance Results: http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html
Paul
March 4, 2010 at 5:50 am
It would have been helpful if you had posted some data
but does this sort of approach help you out?
IF NOT OBJECT_ID('tempdb.dbo.#FOO', 'U') IS NULL
DROP TABLE #FOO
SELECT 'Apple,Banana,Orange,Pineapple,Lemon,pear' AS BulkColumn
INTO #FOO
UNION ALL SELECT '1,2,3,4,5,6'
UNION ALL SELECT 'dave,harry,john,peter,fred, bill'
;
WITH cteTally
AS
(
SELECT TOP 20000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS pk
FROM master.sys.All_Columns t1
CROSS JOIN master.sys.All_Columns t2
)
SELECT * FROM #FOO
CROSS APPLY
(
SELECT
[1] AS COL1,
[2] AS COL2,
[3] AS COL3,
[4] AS COL4,
[5] AS COL5,
[6] AS COL6
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
NULLIF(SUBSTRING(BulkColumn+',', pk, CHARINDEX(',', BulkColumn+',', pk)-pk), '') AS Value
FROM cteTally
WHERE pk-1<LEN(BulkColumn)+LEN(',') AND SUBSTRING(',' + BulkColumn + ',', pk, 1)=','
) AS Z
PIVOT
(
MAX(Value) for ROW in
(
[1],
[2],
[3],
[4],
[5],
[6]
)
)
AS pvt
)
AS Y
March 4, 2010 at 5:57 am
This is what we actually came up with after all the great help on here to fuel the brain:
Update [ad import]
Set ADBU =
replace (substring([distinguished name],
patindex('%Workstations,OU=%', [distinguished name] )+
charindex(',', substring([distinguished name],patindex('%Workstations,OU=%', [distinguished name] )+17,30),2 )
+17+3,4),',','')
where [distinguished name] like '%Workstations,OU=%'
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply