Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
ExpertsI am facing tricky part in sqlMy requirement is like to get particular string from the recordIn my DB I have records like Name_colCN-FCT?AB3457;~XXXXXX;~CN-FCT?AB3458;~YYYYYY;~CN-FCT?AB3459;~ZZZZZZI want like Name_colXXXXXX;YYYYYY;ZZZZZZHow could i get that one??T.I.A
LoztInSpace
Aged Yak Warrior
940 Posts
Posted - 2013-03-03 : 02:23:14
Some combination of left, mid, substr, charindex and patindex will help here depending on your data and requirements.
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-03-03 : 11:30:02
You can use a string splitter such as DelimitedSplit8K - see the example below:
declare @col varchar(512) = 'CN-FCT?AB3457;~XXXXXX;~CN-FCT?AB3458;~YYYYYY;~CN-FCT?AB3459;~ZZZZZZ';select ';'+item from dbo.DelimitedSplit8K(REPLACE(@col,';~',';'),';')where ItemNumber%2 = 0 for xml path('')
The DelimitedSplit8K is available here: http://www.sqlservercentral.com/articles/Tally+Table/72993/