SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 String to Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sg2255551
Constraint Violating Yak Guru

272 Posts

Posted - 03/21/2013 :  10:27:33  Show Profile  Reply with Quote
hi

I have an Nvarchar field of 250 in length and this the sample data

1 1540 6 2148 1253 1568
2 1220 5 2111 235
3 1234 5 5212 1232 1444 2358

I would like break them up into cols based on spaces between them.

How should i go about it? Thanks a lot

djj55
Constraint Violating Yak Guru

USA
329 Posts

Posted - 03/21/2013 :  12:11:46  Show Profile  Reply with Quote
One way is to use a function. We have one that you can pass a delimiter along with the string to be parsed. We pass back a table variable.

See CHARINDEX

djj
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/21/2013 :  12:42:39  Show Profile  Reply with Quote
SELECT replace(<ColumnName>,' ',':')
FROM <yourTableName>

Cheers
MIK

Edited by - MIK_2008 on 03/21/2013 13:21:25
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 03/22/2013 :  00:51:37  Show Profile  Reply with Quote

--Dynamic query
CREATE TABLE #test(id int, data varchar(250))

INSERT INTO #test 
SELECT 1, '1540 6 2148 1253 1568' union all
SELECT 2, '1220 5 2111 235' union all
SELECT 3, '1234 5 5212 1232 1444 2358'

SELECT * FROM #test
DECLARE @pivot varchar(8000)
DECLARE @select varchar(8000)

SELECT 
        @pivot=coalesce(@pivot+',','')+'[col'+cast(number+1 as varchar(10))+']'
FROM 
        master..spt_values where type='p' and 
        number<=(SELECT max(len(data)-len(replace(data,' ',''))) FROM #test)

SELECT 
    @select='
        select p.*
        from (
        select 
            id,substring(data, start+2, endPos-Start-2) as token,
            ''col''+cast(row_number() over(partition by id order by start) as varchar(10)) as n
        from (
            select 
                id, data, n as start, charindex('' '',data,n+2) endPos
                from (select number as n from master..spt_values where type=''p'') num
                cross join 
                (
                    select 
                        id, '' '' + data +'' '' as data 
                    from 
                        #test
                ) m
            where n < len(data)-1
            and substring(data,n+1,1) = '' '') as data
        ) pvt
        Pivot ( max(token)for n in ('+@pivot+'))p'
        PRINT @select

EXEC(@select)

DROP TABLE #test
------------------------------------------------------------
--Method2:  This is based on user-defined function [dbo].[CustomSplit] 
If the Space separated columns maximum count is known, then you can use the following 

CREATE TABLE #test(id int, data varchar(100))

INSERT INTO #test 
SELECT 1, '1540 6 2148 1253 1568' union all
SELECT 2, '1220 5 2111 235' union all
SELECT 3, '1234 5 5212 1232 1444 2358'

SELECT * FROM #test

SELECT * FROM
(
	SELECT
		Id, n As ItemNumber, s AS Item
	FROM
		#test
		CROSS APPLY  dbo.CustomSplit (' ',[data]) dsk -- CustomSplit
)s PIVOT
(MAX (Item) FOR ItemNumber IN  
	([1],[2],[3],[4],[5],[6],[7],,[9],[10],
	[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
	[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
	[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
	[41],[42],[43],[44],[45],[46],[47],[48],[49],[50]
))P

DROP TABLE #test;

CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
	WITH Pieces(n, start, stop) AS (
		SELECT 1, 1, CHARINDEX(@sep, @s)
		UNION ALL
		SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
			FROM Pieces
			WHERE stop > 0
	)
	SELECT n,
	  SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
	FROM Pieces



--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/22/2013 :  01:31:19  Show Profile  Reply with Quote
quote:
Originally posted by sg2255551

hi

I have an Nvarchar field of 250 in length and this the sample data

1 1540 6 2148 1253 1568
2 1220 5 2111 235
3 1234 5 5212 1232 1444 2358

I would like break them up into cols based on spaces between them.

How should i go about it? Thanks a lot


This is one way

http://visakhm.blogspot.in/2013/01/delimited-string-split-xml-parsing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000