How to split in 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

General SQL Server Forums

New to SQL Server Programming

How to split in columns

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2013-09-07 : 01:46:06

Guys,

A#B#C
A#B
A#B#C#D
How do I split above value in columns by "#"?
I need output:

Column1 Column2 Column3 Column4
A B C
A B
A B C D

Please Help!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-07 : 03:11:32

This might work for you, but be aware - this will not work when field contains < or >


with yourtable (yourfield)
  as (select 'A#B#C' as yourfield
      union all
      select 'A#B' as yourfield
      union all
      select 'A#B#C#D' as yourfield
     )
select convert(xml,'<f>'+replace(yourfield,'#','</f><f>')+'</f>').value('/f[1]','varchar(10)') as col1
      ,convert(xml,'<f>'+replace(yourfield,'#','</f><f>')+'</f>').value('/f[2]','varchar(10)') as col2
      ,convert(xml,'<f>'+replace(yourfield,'#','</f><f>')+'</f>').value('/f[3]','varchar(10)') as col3
      ,convert(xml,'<f>'+replace(yourfield,'#','</f><f>')+'</f>').value('/f[4]','varchar(10)') as col4
  from yourtable

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-07 : 03:15:15

use fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

pushp82
Yak Posting Veteran

83 Posts

Posted - 2013-09-07 : 03:24:04

I did this a bit old way...

DECLARE @mnu varchar(max)
DECLARE @strXML VARCHAR(max)
DECLARE @XML XML

SET @strXML = '<table><row><col>' +replace((REPLACE(@mnu,'#','</col><col>')),';','</col></row></table>')

set @XML=cast(@strXML as XML)
insert into table_1
SELECT @mnu_cat,line.col.value('col[1]', 'varchar(1000)') AS col1
,line.col.value('col[2]', 'varchar(1000)') AS col2
,line.col.value('col[3]', 'varchar(1000)') AS col3

FROM @XML.nodes('/table/row') AS line(col)

and a loop over it to cover one by one

Thanks for the responce 'bitsmed' and 'khtan'....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-08 : 04:17:59

Also refer this http://beyondrelational.com/modules/2/blogs/70/posts/10844/splitting-delimited-data-to-columns-set-based-approach.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources