Substring manipulation question

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

Substring manipulation question

Author

Topic

jet_black
Starting Member

4 Posts

Posted - 2009-01-08 : 09:48:18

I am trying to return only characters that appear before an '@' symbol for a particular table and column. The SUBSTRING function seems to only have the ability to bring back a defined string, but I am sure there has to be a function or another way to bring back a variable length based on a character.

My end goal is to update a large number of email addresses in a table -- changing their format from '<firstname>.<lastname>@z.z' to <firstname>_<lastname>@z.z'.

Any assistance you can render would be much appreciated. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:51:06

Have a look at CHARINDEX function in the SQL Server help file "Books Online".

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 09:55:22

if format is consistent


SELECT STUFF(yourcol,CHARINDEX('.',yourcol),1,'_') FROM YourTable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:58:09

Or

UPDATE Table1
SET Col1 = PARSENAME(3) + '_' + PARSENAME(2) + '.' + PARSENAME(1)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 10:03:33

quote:
Originally posted by Peso

Or

UPDATE Table1
SET Col1 = PARSENAME(Col1,3) + '_' + PARSENAME(Col1,2) + '.' + PARSENAME(Col1,1)

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

jet_black
Starting Member

4 Posts

Posted - 2009-01-08 : 11:10:48

Thank you for your help -- in order to make room for cases that didn't adhere to the format, I went ahead and used an update join with a temp table. Hopefully this was the best way:

select * into ##temp from TABLE1

update ##temp
set col1 = PARSENAME(col1, 4) + '_' + PARSENAME(col1,3) + '.' + PARSENAME(col1,2) + '.' + PARSENAME(col1,1)

update TABLE1
set col1 = t.col1
from ##temp t on t.key = TABLE1.key and t.col1 is not null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources