need to split the string value of a column

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 2005 Forums

Transact-SQL (2005)

need to split the string value of a column

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2008-12-29 : 06:50:18

Hi,

I have a table as below:

Report_Name ---- Report_Audience
==============================================
XYZreport ---- Finance,Admin,Management,
ABCreport ---- Finance,Management,

But, I want to show the above table as below:

Report_Name ---- Report_Audience
==============================================
XYZreport ---- Finance
XYZreport ---- Admin
XYZreport ---- Management
ABCreport ---- Finance
ABCreport ---- Management

Kindly advise me how to write a sql query to get the result like this.

Thanks in advance.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-29 : 07:36:27

Hi Try This

SELECT s.report_name,
SUBSTRING(s.report_audience, v.Number - 1,
COALESCE(NULLIF(CHARINDEX(',', s.report_audience, v.Number), 0), LEN(s.report_audience) + 1) - v.Number + 1) AS value
FROM reports AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(',_' + s.report_audience, v.Number, 1) = ','

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 12:14:17

[code]
SELECT t.Report_Name,f.Val
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.Report_Audience,',') f
ORDER BY f.ID
[/code]

ParseValues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources