Splitting 1 column into multiple

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

Splitting 1 column into multiple

Author

Topic

sprotson
Yak Posting Veteran

75 Posts

Posted - 2014-04-28 : 09:02:03

Hopefully someone can help me when a problem I have.

I have 1 column (memberassignment_1) that has the string in it - Port=A; Code=B; Sfx=C; Name=D; Night=E; Sig_Grp=F

I want to split this into 6 columns - Port, Code, sfx, Name, Night, Sig_Grp

EXAMPLE

PORT CODE SFX NAME NIGHT SIG_GRP
A B C D E F

Can anyone help me with this?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-28 : 09:55:04

Assuming the strings are fixed length, something like


select case when substring(memberassignment_1, 1, 5) = 'Port='
            then substring(memberassignment_1, 6, 1)
       end  as Port,
       case when substring(memberassignment_1, 9, 5) = 'Code='
            then substring(memberassignment_1, 10, 1)
       end  as Code,
       ...

sprotson
Yak Posting Veteran

75 Posts

Posted - 2014-04-28 : 10:16:12

Thanks, but the strings are not a fixed length unfortunately

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-28 : 10:25:48

OK, can you post some sample data as DDL (Create Table...) and DML (Insert Into ...)

Basically, you'll want to substitute absolute indexes in SUBSTRING with calls to CHARINDEX. SHouldn't be hard, though it is tedious.

sprotson
Yak Posting Veteran

75 Posts

Posted - 2014-04-28 : 10:57:49

CREATE TABLE TrunkGroup
(
group_number varchar(4),
memberassignment_1 varchar(150)
)

INSERT INTO TrunkGroup group_number,memberassignment_1)
VALUES (‘0001’, ‘Port=T00031; Code=abc; Sfx=sip 32; Name=sip onexmo; Night=T00145; Sig_Grp=sip55’);

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-28 : 13:15:34

Here's one approach using CROSS APPLY to pick apart the text bit by bit:


select ltrim(Port.Port) Port, LTrim(Code.Code) Code, LTrim(Sfx.Sfx) Sfx, LTRIM(Name.Name) Name, LTRIM(Night.Night) Night, LTRIM(Sig_Grp.Sig_Grp) Sig_Grp
from TrunkGroup tg
cross apply (select SUBSTRING(tg.memberassignment_1, 6, CHARINDEX(';', tg.memberassignment_1, 1)-6)
                  , SUBSTRING(tg.memberassignment_1, CHARINDEX(';', tg.memberassignment_1, 1)+2, LEN(tg.memberassignment_1))
			) Port(Port, memberassignment_1)
cross apply (select SUBSTRING(Port.memberassignment_1, 6, CHARINDEX(';', Port.memberassignment_1, 1)-6)
                  , SUBSTRING(Port.memberassignment_1, CHARINDEX(';', Port.memberassignment_1, 1)+2, LEN(Port.memberassignment_1))
			) Code(Code, memberassignment_1)
cross apply (select SUBSTRING(Code.memberassignment_1, 5, CHARINDEX(';', Code.memberassignment_1, 1)-5)
                  , SUBSTRING(Code.memberassignment_1, CHARINDEX(';', Code.memberassignment_1, 1)+2, LEN(Code.memberassignment_1))
			) Sfx(Sfx, memberassignment_1)
cross apply (select SUBSTRING(Sfx.memberassignment_1, 6, CHARINDEX(';', Sfx.memberassignment_1, 1)-6)
                  , SUBSTRING(Sfx.memberassignment_1, CHARINDEX(';', Sfx.memberassignment_1, 1)+2, LEN(Sfx.memberassignment_1))
			) Name(Name, memberassignment_1)
cross apply (select SUBSTRING(Name.memberassignment_1, 7, CHARINDEX(';', Name.memberassignment_1, 1)-7)
                  , SUBSTRING(Name.memberassignment_1, CHARINDEX(';', Name.memberassignment_1, 1)+2, LEN(Name.memberassignment_1))
			) Night(Night, memberassignment_1)
cross apply (select SUBSTRING(Night.memberassignment_1, 9, LEN(Night.memberassignment_1)-8)
                  --, SUBSTRING(Night.memberassignment_1, CHARINDEX(';', Night.memberassignment_1, 1)+2, LEN(Night.memberassignment_1))
			) Sig_Grp(Sig_Grp) --, memberassignment_1)

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-29 : 02:05:41

Try this.....




CREATE TABLE TrunkGroup(group_number varchar(4),memberassignment_1 varchar(150))
INSERT INTO TrunkGroup VALUES ('0001', 'Port=T00031; Code=abc; Sfx=sip 32; Name=sip onexmo; Night=T00145; Sig_Grp=sip55');

DECLARE @temp TABLE (CT VARCHAR(1024),Rn INT)
;WITH cteXML
AS
(
	SELECT		group_number
			   ,CAST('<r>' + REPLACE(memberassignment_1,';','</r><r>')+'</r>' AS XML) AS parents
	FROM		TrunkGroup
)
INSERT INTO		@temp (CT,Rn)
	   SELECT	U.value('.','VARCHAR(100)') AS CT,ROW_NUMBER()OVER (ORDER BY (SELECT 1)) AS Rn
       FROM     cteXML t1 CROSS APPLY parents.nodes('r') a(u)
SELECT  DISTINCT
 (SELECT SUBSTRING(CT,CHARINDEX('=',CT)+1,LEN(CT)) FROM @temp WHERE Rn=1 ) AS Port 
,(SELECT SUBSTRING(CT,CHARINDEX('=',CT)+1,LEN(CT)) FROM @temp WHERE Rn=2 ) AS Code
,(SELECT SUBSTRING(CT,CHARINDEX('=',CT)+1,LEN(CT)) FROM @temp WHERE Rn=3 ) AS Sfx
,(SELECT SUBSTRING(CT,CHARINDEX('=',CT)+1,LEN(CT)) FROM @temp WHERE Rn=4 ) AS Name
,(SELECT SUBSTRING(CT,CHARINDEX('=',CT)+1,LEN(CT)) FROM @temp WHERE Rn=5 ) AS Night
,(SELECT SUBSTRING(CT,CHARINDEX('=',CT)+1,LEN(CT)) FROM @temp WHERE Rn=6 ) AS Sig_Grp
 FROM @temp  AS a

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-29 : 03:47:28

When you are not sure about the columns in column (memberassignment_1) it's better to go for DYNAMIC PIVOT


----------------------------------------------------------------------------------------------------------------------------
--Table Creation 
----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('YourDatabase..TrunkGroup') IS NOT NULL
DROP TABLE TrunkGroup
ELSE
CREATE TABLE TrunkGroup(group_number varchar(4),memberassignment_1 varchar(150))
INSERT INTO TrunkGroup VALUES ('0001', 'Port=T00031; Code=abc; Sfx=sip 32; Name=sip onexmo; Night=T00145; Sig_Grp=sip55');
----------------------------------------------------------------------------------------------------------------------------
--Temp table creation to hold the CTE Result
----------------------------------------------------------------------------------------------------------------------------
CREATE  TABLE #temp(CT VARCHAR(1024),Rn INT)
;WITH cteXML
AS(	SELECT		group_number
		       ,CAST('<r>' + REPLACE(memberassignment_1,';','</r><r>')+'</r>' AS XML) AS parents
	FROM		TrunkGroup
   )
INSERT INTO		#temp (CT,Rn)
	   SELECT	U.value('.','VARCHAR(100)') AS CT
		       ,ROW_NUMBER()OVER (ORDER BY (SELECT 1)) AS Rn
       FROM     cteXML t1 CROSS APPLY parents.nodes('r') a(u)
----------------------------------------------------------------------------------------------------------------------------
--Dynamic pivot
----------------------------------------------------------------------------------------------------------------------------       
DECLARE @Col VARCHAR (MAX) = STUFF((SELECT DISTINCT ',['+[Column]+']' FROM 
							(SELECT   LEFT(CT,CHARINDEX('=',CT)-1) AS [Column]
									  FROM #temp) a 
							 FOR XMl PATH ('')),1,1,'')      
DECLARE @Query VARCHAR(MAX)

SET @Query =N'SELECT * FROM       
(
SELECT LEFT(CT,CHARINDEX(''='',CT)-1) AS [Column],SUBSTRING(CT,CHARINDEX(''='',CT)+1,LEN(CT)) AS VALUE FROM #temp   
)a
PIVOT (MAX(VALUE) FOR [Column] IN('+@Col+'))AS PVT '
EXECUTE (@Query)
       
----------------------------------------------------------------------------------------------------------------------------
--Drop the tables
DROP TABLE TrunkGroup
DROP TABLE #temp
----------------------------------------------------------------------------------------------------------------------------

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-29 : 08:04:57

Here's a method using XML methods to do the parsing and extracting:


select mx.value('@Port'   , 'varchar(50)') as Port
     , mx.value('@Code'   , 'varchar(50)') as Code
     , mx.value('@Sfx'    , 'varchar(50)') as Sfx
     , mx.value('@Name'   , 'varchar(50)') as Name
     , mx.value('@Night'  , 'varchar(50)') as Night
     , mx.value('@Sig_Grp', 'varchar(50)') as Sig_Grp
from   TrunkGroup
cross apply (select cast('<tgdata ' + replace(replace(memberassignment_1, '=', '="'), ';', '"') + '" />' as xml)) tgxml(mxml) 
cross apply mxml.nodes('tgdata') as tgdata(mx)

Note that this should work unless you have values with embedded double-quotation marks, in which case you'd need to add a third replace method to escape them.

sprotson
Yak Posting Veteran

75 Posts

Posted - 2014-04-29 : 08:29:05

Thanks, I really appreciate the help. This last method seems to work for me the best.

Another question related to this, if I may.

If I have another column - memberassignment_2, how would I modify the query to also split this column out.

Ideally I would want to retain the existing 6 columns, and just add another called - AssignmentNumber, eg 1 or 2 and for assignment 1 to be on 1 row in the 6 columns and assignment 2 to be on row in the 6 columns.

This would also have to be for each port number

Port No Assign No Port Code SFX Name Night SigGrp
1 1 T00031 MM710 a sip onexmo e 1
2 1 T00043 MM710 b SIP Trunk f 2
1 2 T00298 MM710 c to CMM g 3
2 2 001V201 MM710 d to CMM h 10

sprotson
Yak Posting Veteran

75 Posts

Posted - 2014-04-29 : 08:41:20

I managed to do it with UNION ALL, not pretty but it works

select group_number, '1' as AssignmentNumber, mx.value('@Port' , 'varchar(50)') as Port
, mx.value('@Code' , 'varchar(50)') as Code
, mx.value('@Sfx' , 'varchar(50)') as Sfx
, mx.value('@Name' , 'varchar(50)') as Name
, mx.value('@Night' , 'varchar(50)') as Night
, mx.value('@Sig_Grp', 'varchar(50)') as Sig_Grp
from AvayaSDKTrunkGroup
cross apply (select cast('<tgdata ' + replace(replace(memberassignment_1, '=', '="'), ';', '"') + '" />' as xml)) tgxml(mxml)
cross apply mxml.nodes('tgdata') as tgdata(mx)

UNION ALL

select group_number, '2' as AssignmentNumber, mx.value('@Port' , 'varchar(50)') as Port
, mx.value('@Code' , 'varchar(50)') as Code
, mx.value('@Sfx' , 'varchar(50)') as Sfx
, mx.value('@Name' , 'varchar(50)') as Name
, mx.value('@Night' , 'varchar(50)') as Night
, mx.value('@Sig_Grp', 'varchar(50)') as Sig_Grp
from AvayaSDKTrunkGroup
cross apply (select cast('<tgdata ' + replace(replace(memberassignment_2, '=', '="'), ';', '"') + '" />' as xml)) tgxml(mxml)
cross apply mxml.nodes('tgdata') as tgdata(mx)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-29 : 10:06:06

How about this:


;with m1 as (
	select group_number, '1' as AssignmentNumber,  memberassignment_1
	from TrunkGroup
	union all
	select group_number, '2' as AssignmentNumber,  memberassignment_2
	from TrunkGroup
	)
select tg.group_number, tg.AssignmentNumber
     , mx.value('@Port'   , 'varchar(50)') as Port
     , mx.value('@Code'   , 'varchar(50)') as Code
     , mx.value('@Sfx'    , 'varchar(50)') as Sfx
     , mx.value('@Name'   , 'varchar(50)') as Name
     , mx.value('@Night'  , 'varchar(50)') as Night
     , mx.value('@Sig_Grp', 'varchar(50)') as Sig_Grp
from   m1 tg
cross apply (select cast('<tgdata ' + replace(replace(memberassignment_1, '=', '="'), ';', '"') + '" />' as xml)) tgxml(mxml) 
cross apply mxml.nodes('tgdata') as tgdata(mx)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources