How to parse one variable into three (delimited )

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)

How to parse one variable into three (delimited )

Author

Topic

shilpagupta4
Starting Member

3 Posts

Posted - 2010-04-02 : 10:12:07

I have variable "Category" delimited by underscore:

Entertain-TV/Movie_News_Pre Roll
Entertain-TV/Movie_News_Standard Media
Entertain-TV/Movie_News_Video Companion

Sample Output

Three variables:
Category_1 Subcategory Placement_name
Entertain-TV/Movie News Pre Roll
Entertain-TV/Movie News Standard Media
Entertain-TV/Movie News Video Companion

How can we do that in SQL? Thanks in advance.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-04-02 : 10:28:04

try like this
declare @t varchar(max)
select @t = 'Entertain-TV/Movie_News_Pre Roll'

select @t = replace(@t,'_','.')

select parsename(@t,1),parsename(@t,2),parsename(@t,3)

tamancha.1
Starting Member

37 Posts

Posted - 2010-04-02 : 10:50:14

Awesome. Thanks.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-04-02 : 10:53:16

welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 11:02:37

will work fine as long as your input has exactly 3 _'s

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-02 : 11:08:36

and does not contain dot

for better handling use fnParseString

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 11:18:14

or this

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources