Getting rid of everything after underscore

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

General SQL Server Forums

New to SQL Server Programming

Getting rid of everything after underscore

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2013-09-11 : 09:41:12

Hello,

I have data that looks like this:

CAT_DOG
MICE_CAT
RABBIT_DOG
LION_MICE

I want a select clause that will give me just this:

CAT
MICE
RABBIT
LION

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-11 : 09:45:24

SELECT LEFT(columnname, charindex('_', columnname)-1)

- Lumbago
My blog-> http://thefirstsql.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-11 : 09:57:18

And, in case you could have rows in which there is no underscore,

SELECT LEFT(columnname, charindex('_', columnname+'_')-1)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-09-11 : 10:02:11

quote:

And, in case you could have rows in which there is no underscore,
SELECT LEFT(columnname, charindex('_', columnname+'_')-1)

My bad

- Lumbago
My blog-> http://thefirstsql.com

Johnph
Posting Yak Master

103 Posts

Posted - 2013-09-11 : 12:00:13

Thank you!

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-09-16 : 09:55:10

Hi John,

Another Way
DECLARE @v_ID VARCHAR(20)
SET @v_ID = 'CAT_DOG'

SELECT SUBSTRING(@v_ID,1,CHARINDEX('_',@v_ID+'_')-1)

P.V.P.MOhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources