Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
string parsing a single record into multiple records

Answered string parsing a single record into multiple records

  • Tuesday, December 04, 2012 7:36 PM
     
     
    Sign In to Vote
    0

    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

    column1         column2

    12345            the quick brown fox

    12346            mmm turkey

    -result set

    column1        column2

    12345           the

    12345           quick

    12345           brown

    12345           fox

    12346           mmm

    12346           turkey

     

All Replies

  • Wednesday, December 05, 2012 6:01 AM
     
     Proposed
    Sign In to Vote
    0

    Post questions relevant to data mining using SQL Server.

    See this: http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

     
  • Tuesday, December 11, 2012 8:05 AM
     
     Answered
    Sign In to Vote
    0

    Hi Circusboy78,

    You can use T-SQL to achieve your target, please see:
    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/b72e0084-c8e3-488f-bd6b-edf09ef54ae0

    Thanks,
    Eileen

    Eileen Zhao
    TechNet Community Support

     
  • Tuesday, December 11, 2012 8:20 AM
    Answerer
     
     
    Sign In to Vote
    0
    create 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
     GO


    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
     
      Has Code
    Sign In to Vote
    0

    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

    Regards
    Satheesh

     
  • Tuesday, December 11, 2012 9:52 AM
     
      Has Code
    Sign In to Vote
    0

    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 SomeID
    Just 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.

     
  • Tuesday, December 11, 2012 2:36 PM
     
     Proposed
    Sign In to Vote
    1

    Hi,

    In the below get a function which take string and return table

    http://candordeveloper.com/2012/06/29/sql-server-parsing-a-string-into-a-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 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0
     
    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)