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
 General SQL Server Forums
 New to SQL Server Programming
 Substring
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

410 Posts

Posted - 06/06/2014 :  11:11:10  Show Profile  Reply with Quote
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

Edited by - peace on 06/06/2014 11:12:17

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/06/2014 :  11:36:03  Show Profile  Reply with Quote
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/
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 06/06/2014 :  11:51:04  Show Profile  Reply with Quote
How to do create function for dbo.delimitedSplit8K
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/06/2014 :  12:31:44  Show Profile  Reply with Quote
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.
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 06/07/2014 :  00:03:42  Show Profile  Reply with Quote
Can I set @x as columnA something like this?

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

it doesn't work.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/07/2014 :  09:37:50  Show Profile  Reply with Quote
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
Go to Top of Page

peace
Constraint Violating Yak Guru

410 Posts

Posted - 06/15/2014 :  04:27:55  Show Profile  Reply with Quote
I tried but it came out as NULL
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 06/16/2014 :  08:37:12  Show Profile  Reply with Quote
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.
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.08 seconds. Powered By: Snitz Forums 2000