Extract string - SQL Server Forums

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

Extract string

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-07-21 : 03:44:40

Hi,
My table contains value like Master's/MBA, Bachelors/BCA etc.
I need to get the result as masters,bachelors etc.
ie,to get the value before the slash from the string.
Any help is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 04:20:28

[code]SELECT LEFT(Field,CASE WHEN CHARINDEX('/',Field) >0 THEN CHARINDEX('/',Field)-1 ELSE LEN(Field) END) FROM Table
[/code]

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-21 : 04:22:12


select
case
 when charindex('/',YourColumn) > 0 then substring(YourColumn,1,charindex('/',YourColumn)-1)
 else YourColumn
end as YourColumn
from table

No, you're never too old to Yak'n'Roll if you're too young to die.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-07-21 : 04:23:22

select left(value,charindex('/',value)-1) from YourTable

Here value is Column name

--Ranjit

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 04:24:17

quote:
Originally posted by Ranjit.ileni

select left(value,charindex('/',value)-1) from YourTable

Here value is Column name

--Ranjit

this will break if there are values present in value field without / character

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

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-07-21 : 04:40:32

Thank you. It worked. What if I need to return an integer?
Like if masters, return 1..
if bachelors, return 2...

quote:
Originally posted by visakh16

SELECT LEFT(Field,CASE WHEN CHARINDEX('/',Field) >0 THEN CHARINDEX('/',Field)-1 ELSE LEN(Field) END) FROM Table

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 04:58:42

you need a CASE WHEN


SELECT CASE LEFT(Field,CASE WHEN CHARINDEX('/',Field) >0 THEN CHARINDEX('/',Field)-1 ELSE LEN(Field) END)
WHEN 'Masters' THEN 1
WHEN 'Bachelors' THEN 2
...
END
 FROM Table

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-21 : 05:02:49

quote:
Originally posted by visakh16

you need a CASE WHEN

SELECT CASE LEFT(Field,CASE WHEN CHARINDEX('/',Field) >0 THEN CHARINDEX('/',Field)-1 ELSE LEN(Field) END)
WHEN 'Master''s' THEN 1
WHEN 'Bachelors' THEN 2
...
END
FROM Table

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

No, you're never too old to Yak'n'Roll if you're too young to die.

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-07-21 : 06:14:00

Thanks again visakh and webfred..

I need to update a table's all category values with the result I got
for each record.
Say, corresponding to master,it should update with 1 and bachelors it
should update with 2..

Update MyTable
SET Category = (The value I got as result for the select statement)

Can I directly assign the select statement or use joins?
How to achieve that?

quote:
Originally posted by visakh16

you need a CASE WHEN

SELECT CASE LEFT(Field,CASE WHEN CHARINDEX('/',Field) >0 THEN CHARINDEX('/',Field)-1 ELSE LEN(Field) END)
WHEN 'Masters' THEN 1
WHEN 'Bachelors' THEN 2
...
END
FROM Table

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 06:22:45

you can use it like


UPDATE t
SET t.Category= CASE LEFT(Field,CASE WHEN CHARINDEX('/',Field) >0 THEN CHARINDEX('/',Field)-1 ELSE LEN(Field) END)
WHEN 'Master''s' THEN 1
WHEN 'Bachelors' THEN 2
...
END 
FROM MyTable t

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

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-07-21 : 08:48:31

Thanks a lot...

quote:
Originally posted by visakh16

you can use it like

UPDATE t
SET t.Category= CASE LEFT(Field,CASE WHEN CHARINDEX('/',Field) >0 THEN CHARINDEX('/',Field)-1 ELSE LEN(Field) END)
WHEN 'Master''s' THEN 1
WHEN 'Bachelors' THEN 2
...
END
FROM MyTable t

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 10:04:01

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources