String to Columns - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

String to Columns

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2013-03-21 : 10:27:33

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

352 Posts

Posted - 2013-03-21 : 12:11:46

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-21 : 12:42:39

SELECT replace(<ColumnName>,' ',':')
FROM <yourTableName>

Cheers
MIK

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-22 : 00:51:37

[code]
--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
[/code]

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-22 : 01:31:19

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources