String manipulation

Question

String manipulation

wannabe dba

SSCommitted

Points: 1524
More actions
September 22, 2010 at 4:01 pm

#226743

I need to extract a substring from a column .The data in the column mainly contains email addresses and I need to remove the .com,.br,.uk,.ar etc from the end of the e-mail address.The problem is the length of the email is different in every row .
eg there could be an email like george@gmail.com and joe@yahoo.ar.In this scenario is there a way I can display george@gmail and joe@yahoo

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

wannabe dba (9/22/2010)
I need to extract a substring from a column .The data in the column mainly contains email addresses and I need to remove the .com,.br,.uk,.ar etc from the end of the e-mail address.The problem is the length of the email is different in every row .
eg there could be an email like george@gmail.com and joe@yahoo.ar.In this scenario is there a way I can display george@gmail and joe@yahoo

Sure, try something like...

select substring('george@gmail.com',1,

(len('george@gmail.com') -

(len('george@gmail.com') - CHARINDEX('.','george@gmail.com') +1))

)

At implementation time just replace literal by variable name. 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

subbu_e SSCrazy Points: 2431 More actions · Answer 2

for ex. if you have table called email

create table email (id int ,emailaddress varchar(50))

here you have email address on emailaddress column. please use the below query to show your email address by removing (.com,.ge,.us,etc)

SELECT substring(emailaddress,1,CHARINDEX( '.', emailaddress, 1)-1) from email

hope this helps.

Regards,

Subbu

http://mssqlforum.wordpress.com

Regards,
Subbu
Click here to Get Speedy answer or solution

Shanalise Hall of Fame Points: 3188 More actions · Answer 3

You can research on these functions and get a simpler way to get your results

SUBSTRING

LTRIM

RTRIM

CHARINDEX

LythamsFinest SSCommitted Points: 1786 More actions · Answer 4

A simple mechanism to cater for situations where more than one period (.) can appear in the emailaddress string try the following:

DECLARE @EmailAddress VARCHAR(50)

SET @EmailAddress = 'firstname.lastname@test.org'

SELECT SUBSTRING(@EmailAddress,1,

LEN(@EmailAddress) - CHARINDEX('.',REVERSE(RTRIM(@EmailAddress))))

Note how the REVERSE() function has been used to flip the string allowing for the CHARINDEX() function to pick up the correct period position. ie:

LEN(@EmailAddress) = 27

CHARINDEX('.',REVERSE(RTRIM(@EmailAddress)) = 4

results in:

SUBSTRING(@EmailAddress,1,(27 -4))

Also note that RTRIM() is important and used to cover for both VARCHAR and CHAR datatypes for the emailaddress string.

Hope this helps

wannabe dba SSCommitted Points: 1524 More actions · Answer 5

wannabe dba

SSCommitted

Points: 1524

September 23, 2010 at 8:06 am

#1226345

Paul:

thx man

wannabe dba SSCommitted Points: 1524 More actions · Answer 6

Spencer,

Thx man.that is an elegant script.it did exactly what I wanted.