Update from 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

SQL Server 2005 Forums

Transact-SQL (2005)

Update from select.....

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2009-07-16 : 04:48:50

Hi all,

I need some help with an update query,

I need to update a column with part of a email address namely anything before the @ sign

I have the following code....

UPDATE table1
SET col2 =
(SELECT left (col1, 5) FROM table1)

But as you can appreciate names are different lengths; how can I modify the script to detect the @ and copy everything before it?

Kind Regards

Pete.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 04:56:32

declare @mail varchar(255)
set @mail='YAKnROLL@sqlteam.com'
select left(@mail,charindex('@',@mail)-1)

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

petek
Posting Yak Master

192 Posts

Posted - 2009-07-16 : 05:12:31

Hey that’s brilliant webfred thank you for the quick response......

Only I have hundreds of accounts is there a way to select the column?

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 05:18:25

select left(YourColumnNameHere, charindex('@', YourColumnNameHere) - 1)

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 05:22:58

Sorry I thought it was clear...

update table1
set col2 = left(col1, charindex('@', col1) - 1)

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

petek
Posting Yak Master

192 Posts

Posted - 2009-07-16 : 05:26:02

Cheers Pesco,

i get the following error....

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

Kind Regards

Pete.

petek
Posting Yak Master

192 Posts

Posted - 2009-07-16 : 05:34:41

sorry should have said there is a .(fullstop/Dot) in the string too...

Kind Regards

Pete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 05:34:49

It means you do have records without the "@" character due to faulty integrity checks.

UPDATE	Table1
SET	Col1 =	CASE CHARINDEX('@', Col1) 
			WHEN 0 THEN Col1
			ELSE SUBSTRING(Col1, 1, CHARINDEX('@', Col1) - 1)
		END

UPDATE	Table1
SET	Col1 =	SUBSTRING(Col1, 1, CHARINDEX('@', Col1) - 1)
WHERE	Col1 LIKE '%@%'

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

petek
Posting Yak Master

192 Posts

Posted - 2009-07-16 : 06:36:21

sorry i should have said......the email consists of....

firstname.lastname@emailaddress.co.uk

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources