Edit replace in SQL? - 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

Edit replace in SQL?

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2009-01-30 : 04:39:08

I've just been 'scanning' my database and have noticed that in the email address field there are email address with a space.

This is obviously bad data and I was wondering if there is a way to 'edit replace' within sql?

JT

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-30 : 04:41:27

if u want to replace the space with emptystring
use this
replace(fieldname,' ','')
if not post some sample data

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-30 : 04:41:58

update table
set email = replace( email,' ','')

Topaz
Posting Yak Master

199 Posts

Posted - 2009-01-30 : 10:53:35

will this just remove the spaces in the email address field? Eg if it currently looked like this:

j o h nny@johnny. co.uk

it would change it to johnny@johnny.co.uk?

Thanks

JT

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-30 : 11:07:43

Well.. why not try it out for yourself? Set a little test up like I did below..


declare @x char(80)
set @x='a b   c de'
print @x

result: a b   c de

set @x=replace(@x,' ','')
print @x

result: abcde

r&r

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-30 : 22:57:06

u can update the email with that query check this
declare @val varchar(80)
set @val='j o h nny@johnny. co.uk'

select @val
select @val=replace(@val,' ','')
select @val

update tablename
set email= replace(email,' ','')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources