Parsing a character string

Question

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 1

Try

SELECT RIGHT(Type,CHARINDEX('-',REVERSE(Type))-1)

Mark

tquataert SSC Rookie Points: 45 More actions · Answer 2

Thanks Mark,

I get the following error when running the above statement:

Server: Msg 536, Level 16, State 1, Line 1

Invalid length parameter passed to the substring function.

Doesn't like the -1 in the charindex. I tried 0 but it includes the "-" and +1 puts me on the left side of the dash????

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 3

You will get that error if there is no '-' in TYPE. You can get around that by doing a check first.

SELECT

CASE CHARINDEX('-',type)

WHEN 0 THEN ''

ELSE RIGHT(Type,CHARINDEX('-',REVERSE(Type))-1)

END

tquataert SSC Rookie Points: 45 More actions · Answer 4

Thanks so much Mark. I ended up using a vairation. Thanks for pointing me in the "right" direction.

substring(right(i.Type,CHARINDEX('-', reverse(i.Type))), 2, 20) as Category

Terry

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Just for fun, here's another variation on that topic

SELECT

PARSENAME(REPLACE('EXISTING SERVICE-DESKTOP HARDWARE-PC', '-','.'),1)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

covitz Old Hand Points: 322 More actions · Answer 6

Here is another interesting way:

select Type2=substring(Type,charindex('-',Type,charindex('-',Type)+1)+1,

len(Type) - charindex('-',Type,charindex('-',Type)+1))