split out name for email address

[bhp]

Hi does anyone know if possible to do the following:

Have a tabel that has UserName, e.g. "John Smith" and and column called emailaddress which is "jsmith' we want replace all "jsmith" with "John.smith@mycompany.com"
Is that possible?
Thanks - James

 

[markros]

It would be much simplier if you would have name in separate columns (FirstName and LastName - we just discussed this issue recently).

Anyway, assuming always one space between two names you can do

update myTable set email = replace(Rtrim(Name), ' ','.') + '@myCompany.com')

 

[markros]

No ) at the end - it's a typo.

Also, if you want smith in low case it's a bit harder, but you can use substring and charindex in this case.

 

[bhp]

thanks for super fast response was on similar lines