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

SQL Server 2000 Forums

SQL Server Development (2000)

chatAt

Author

Topic

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-19 : 05:07:04

I have Table_A with 2 fields, LAST_NAME and EMAIL. My LAST_NAME have data with all 'N.A.'. I will like to retrieve the username of EMAIL to update it to be the LAST_NAME of Table_A.

For example,

N.A. mary@hotmail.com
N.A. jon@yahoo.com

To

mary mary@hotmail.com
jon jon@yahoo.com

Is there anyway I can do it with pure SQL?

I know I can use charAt('@'). However, I am not sure how I can go about doing it. For example, how do you select everything before the '@' character? Can anyone help?

Thanks!

- HELP -

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-19 : 05:31:16

[code]SELECT last_name = CASE
WHEN last_name = 'N.A.' THEN LEFT(email, charindex('@', email)-1)
ELSE last_name
END, email
FROM table_a[/code]

EDIT: Or if you would like to update your table then this would be the way to do it:[code]UPDATE table_a SET lastname = LEFT(email, charindex('@', email)-1) WHERE last_name = 'N.A.'[/code]--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-19 : 05:33:05

Thx for ur help!

What does 'last_name = CASE' mean?

quote:
Originally posted by Lumbago

SELECT last_name = CASE
WHEN last_name = 'N.A.' THEN LEFT(email, charindex('@', email)-1)
ELSE last_name
END, email
FROM table_a

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

- HELP -

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-19 : 05:39:21

It means that you can select different things based on the existing value of a field in the table.

Example:
DECLARE @States table (State varchar(2))
INSERT INTO @States 
SELECT 'CA' UNION ALL SELECT 'IA' UNION ALL 
SELECT 'NM' UNION ALL SELECT 'AL' 

SELECT State, StateName = CASE
  WHEN State = 'CA' THEN 'California'
  WHEN State = 'IA' THEN 'Iowa'
  WHEN State = 'NM' THEN 'New Mexico'
  ELSE 'Unknown'
  END
FROM @States

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-19 : 05:41:06

I see. WoW~ *Impress*

I managed to select all of them out! ^-^

But is it possible for me to do the update the LAST_NAME at the same time?

- HELP -

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-19 : 05:43:42

Not sure if this is what you mean but here is one option:

UPDATE table_a SET lastname = LEFT(email, charindex('@', email)-1) WHERE last_name = 'N.A.'

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

doggi3
Yak Posting Veteran

62 Posts

Posted - 2004-10-19 : 05:49:37

YuPz YuPz!

You've been sucha great help! ^-^

Thank You~!!!

- HELP -

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-10-19 : 06:12:37

no problemo

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources