Deleting part of column data - 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

Deleting part of column data

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-05-11 : 10:48:00

Hello everyone.

I have a list of email addresses. for example they are like

robert dineen [rob.dineen@hotmail.com]

i wont to update the data so each email reads like

rob.dineen@hotmail.com not just select, but update so it is permanently amended.

MCTS / MCITP certified

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 10:53:45

DECLARE @str varchar(100)
SET @str = 'robert dineen [rob.dineen@hotmail.com]'
SELECT REPLACE(STUFF(@str,1,CHARINDEX('[',@str),''),']','')

Jim

Everyday I learn something that somebody else already knew

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-05-11 : 11:12:47

i am using the script supplied but changed as follows

DECLARE @str varchar(100)
set @str = (select email from table)
SELECT REPLACE(STUFF(@str,1,CHARINDEX('[',@str),''),']','')

then recieve the following error

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)

i want to change all email address in one statement.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-11 : 11:16:17

UPDATE yourTable
SET email = REPLACE(STUFF(email,1,CHARINDEX('[',email),''),']','')
--WHERE

--------------------------
http://connectsql.blogspot.com/

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-05-11 : 11:27:36

excellent

thank you very much, saved me a lot of time

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources