Substring - 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

General SQL Server Forums

New to SQL Server Programming

Substring

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-06-06 : 11:11:10

I have data as below:

columnA
D7 330 4/13/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/13/2014 19:15:0 4/13/2014 23:45:0 4/14/2014 0:45:0

How can I separate it to 5 additional columns?

columnB: D7 330 4/13/2014 0:0:0
columnC: KUL PVG 4/13/2014 18:35:0
columnD: 4/13/2014 19:15:0
columnE: 4/13/2014 23:45:0
columnF: 4/14/2014 0:45:0

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-06 : 11:36:03

quote:
Originally posted by peace

I have data as below:

columnA
D7 330 4/13/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/13/2014 19:15:0 4/13/2014 23:45:0 4/14/2014 0:45:0

How can I separate it to 5 additional columns?

columnB: D7 330 4/13/2014 0:0:0
columnC: KUL PVG 4/13/2014 18:35:0
columnD: 4/13/2014 19:15:0
columnE: 4/13/2014 23:45:0
columnF: 4/14/2014 0:45:0

You have to find some rule by which you can split the data. Fo example, based on your sample data, every instance of ":0 " (i.e., a colon, followed by a zero followed by a space) might be the rule that you can use. If that is the case this is one way to split it:

DECLARE @x VARCHAR(1024) = 
	'D7 330 4/13/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/13/2014 19:15:0 4/13/2014 23:45:0 4/14/2014 0:45:0';
SELECT * FROM
dbo.delimitedSplit8K(REPLACE(@x,':0 ',':0|'),'|')
PIVOT(MAX(Item) FOR ItemNumber IN ([1],[2],[3],[4],[5]))P

The function that I am using delimitedSplit8K is available here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-06-06 : 11:51:04

How to do create function for dbo.delimitedSplit8K

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-06 : 12:31:44

Copy the code in Figure 21 of that web page (the code that starts with CREATE FUNCTION...), paste it into an SSMS query window, and execute. That will create the function in the database that you have selected. You need to do this only once. Once the function is created, that is there forever for you to use.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-06-07 : 00:03:42

Can I set @x as columnA something like this?

declare @x nvarchar(max)
set @x=select columnA from tableA

it doesn't work.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-07 : 09:37:50

quote:
Originally posted by peace

Can I set @x as columnA something like this?

declare @x nvarchar(max)
set @x=select columnA from tableA

it doesn't work.

SELECT a.ColumnA, b.* FROM
tableA a
cross apply
( select * from dbo.delimitedSplit8K(REPLACE(columnA,':0 ',':0|'),'|')
	PIVOT(MAX(Item) FOR ItemNumber IN ([1],[2],[3],[4],[5]))P
) b

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2014-06-15 : 04:27:55

I tried but it came out as NULL

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-16 : 08:37:12

quote:
Originally posted by peace

I tried but it came out as NULL

What is the query you tried? See this example below:

CREATE TABLE TableA(columnA VARCHAR(256));
INSERT INTO TableA VALUES
('D7 330 4/13/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/13/2014 19:15:0 4/13/2014 23:45:0 4/14/2014 0:45:0'),
('D8 330 4/24/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/24/2014 19:15:0 4/24/2014 23:45:0')

SELECT a.ColumnA, b.* FROM
tableA a
cross apply
( select * from dbo.delimitedSplit8K(REPLACE(columnA,':0 ',':0|'),'|')
	PIVOT(MAX(Item) FOR ItemNumber IN ([1],[2],[3],[4],[5]))P
) b

-- OUTPUT
--ColumnA	1	2	3	4	5
--D7 330 4/13/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/13/2014 19:15:0 4/13/2014 23:45:0 4/14/2014 0:45:0	D7 330 4/13/2014 0:0:0	KUL PVG 4/13/2014 18:35:0	4/13/2014 19:15:0	4/13/2014 23:45:0	4/14/2014 0:45:0
--D8 330 4/24/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/24/2014 19:15:0 4/24/2014 23:45:0	D8 330 4/24/2014 0:0:0	KUL PVG 4/13/2014 18:35:0	4/24/2014 19:15:0	4/24/2014 23:45:0	NULL

If you are still not able to make it work, post the code you tried.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources