Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sg2255551
Constraint Violating Yak Guru

274 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
352 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
1054 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
2241 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
52326 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  
 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.08 seconds. Powered By: Snitz Forums 2000