Tuesday, December 04, 2012 7:36 PM
i would like to parse a single string with an ID into multiple records with the same ID, i am lost as to the most efficient way of doing this. always using space as the delimiter.
-what i have
12345 the quick brown fox
12346 mmm turkey
- Moved by Eileen ZhaoMicrosoft Contingent Staff Tuesday, December 11, 2012 7:59 AM (From:Data Mining)
Wednesday, December 05, 2012 6:01 AM
Post questions relevant to data mining using SQL Server.
Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Tuesday, December 11, 2012 5:55 PM
Tuesday, December 11, 2012 8:05 AM
You can use T-SQL to achieve your target, please see:
TechNet Community Support
Tuesday, December 11, 2012 8:20 AMAnswerercreate table #t ( c1 int,c2 varchar(20))
insert into #t values (1,'one two'),(2, 'three four')
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
SELECT c1,SUBSTRING(c2, number, CHARINDEX(' ', c2 + ' ', number) - number)
from #t,numbers where substring(' '+c2,number,1)=' '
AND number < LEN(c2) + 1
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
Tuesday, December 11, 2012 9:05 AM
A split function would help you here, try this
if OBJECT_ID ('dbo.Split') is not null drop FUNCTION dbo.Split Go CREATE FUNCTION dbo.Split ( @RowData nvarchar(2000), @SplitOn varchar(1)=',' ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) AS BEGIN Declare @Cnt int Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0) Begin Insert Into @RtnValue (Value) Select value = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) Set @Cnt = @Cnt + 1 End Insert Into @RtnValue (value) Select value = ltrim(rtrim(@RowData)) Return END GO
Now splitting the column is easy
declare @tab table( column1 int, column2 varchar(500)) insert into @tab values (12345,'the quick brown fox'), (12346,'mmm turkey') select t.column1, s.value from @tab t cross apply dbo.split(t.column2,' ') s
Tuesday, December 11, 2012 9:52 AM
Here is the SQL from Stack Overflow referenced by Eileen:
;with Testdata as ( select * from ( values (0, 5, ' '), (1, 9, '18,20,22'), (2, 8, '17,19'), (3, 7, '13,19,20'), (4, 6, '') ) Testdata(SomeID, OtherID, Data) ), tmp(SomeID, OtherID, DataItem, Data) as ( select SomeID, OtherID, convert(varchar(max), LEFT(Data, CHARINDEX(',',Data+',')-1)), STUFF(Data, 1, CHARINDEX(',',Data+','), '') from Testdata union all select SomeID, OtherID, convert(varchar(max), LEFT(Data, CHARINDEX(',',Data+',')-1)), STUFF(Data, 1, CHARINDEX(',',Data+','), '') from tmp where Data > '' ) select SomeID, OtherID, DataItem from tmp order by SomeIDJust change the delimiter ',' above to a space as ' '.
"Premature optimization is the root of all evil." - Knuth
If I provoked thought, please click the green arrow
If I provoked Aha! please click Propose as Answer
We are here to learn, to share knowledge, and to earn points; all in about equal measure.
- Edited by Pieter Geerkens Tuesday, December 11, 2012 9:53 AM
Tuesday, December 11, 2012 2:36 PM
In the below get a function which take string and return table
use that function to take a table then select from ....hope you can do ..
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/
- Proposed As Answer by RAYAIN Friday, December 21, 2012 8:54 AM
Tuesday, December 11, 2012 4:28 PMModerator
declare @test table(id int identity(1,1), column1 varchar(35),column2 varchar(35)); insert into @test values('12345', 'the quick brown fox'),('12346', 'mmm turkey') SELECT column1, S.a.value('.', 'VARCHAR(100)') AS splitVal FROM ( SELECT column1,CAST (N'<H><r>' + REPLACE(column2, ' ', '</r><r>') + '</r></H>' AS XML) AS [vals] FROM @test) d CROSS APPLY d.[vals].nodes('/H/r') S(a)