select - 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

select

Author

Topic

JoseNeto
Starting Member

1 Post

Posted - 2008-12-11 : 06:33:07

Hi
All
I use sql server 2000 and i have this select:

select name from customer

This select return this:
name
==========
jose vieira silva
antonio carlos
joao silveira
etc.

I'd like it return this way:
if the colun name there are SILVA return just number = 1
if the colun name there are JOAO return just number = 2
if the colun name there are ANTONIO return just number = 3

How do i do this?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-11 : 07:00:11

SELECT Name,
CASE WHEN Name LIKE '%silva%' then 1
WHEN Name LIKE '%joao%' then 2
WHEN Name LIKE '%antonio%' then 3
end AS status
FROM Customer

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 10:30:46

it would be better to create a mapping table for each names with integer values to be returned if you want to check for lots of names. then just join on to table to retrive int value. no need of long case conditions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources