Urgent help required for Query

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

SQL Server 2012 Forums

Transact-SQL (2012)

Urgent help required for Query

Author

Topic

amit120k
Starting Member

16 Posts

Posted - 2015-01-08 : 02:02:48

I have string below:

role1 KPI[jan 25th];role2 kpi[feb 30th];role3 KPI[mar 31st]

The output should be

role1 KPI;role2 KPI;role3 KPI

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-01-08 : 02:26:43

declare @data varchar(100), @sql varchar(8000)
set @data='role1 KPI[jan 25th];role2 kpi[feb 30th];role3 KPI[mar 31st]'
set @sql=''
select @sql=@sql+substring(data,1,charindex('[',data)-1)+';' from dbo.fnParseList(';',@data)
select @sql

You can get fnParseList at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Madhivanan

Failing to plan is Planning to fail

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-08 : 09:18:42

@madhivanan -- caution, that type of query is not documented, not supported and can cause erroneous results.

Use FOR XML PATH('') instead.

See here: http://sqlmag.com/sql-server/multi-row-variable-assignment-and-order

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-01-08 : 12:57:05

[code]DECLARE @Data VARCHAR(MAX) = 'role1 KPI[jan 25th];role2 kpi[feb 30th];role3 KPI[mar 31st]';

-- SwePeso
SELECT REPLACE(REPLACE(REPLACE(REPLACE(CAST(Data.query('i') AS VARCHAR(MAX)), '', ';'), '', ''), '', ''), '', '')
FROM (
VALUES (CAST('' + REPLACE(REPLACE(REPLACE(@Data, '[', '<x>'), ']', '</x>') + '', ';', '') AS XML))
) AS d(Data);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources