x

I have a email database

Now in email address column lets say we have a email like 'test@gmail.comvvv', 'test1@yahoo.comss', can any one say how to update the email address column and truncate anything that is after .com.

I wan to make .comvvv as .com.

Please advice

Regards Vishvas
more ▼

asked Aug 09 '12 at 06:35 AM in Default

Vishvas gravatar image

Vishvas
0 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

The @sp_lock solution will work, unless there is another occurence in the '.com' in the eamil addres (eg. in user name priror @). So I suggest to find the position in reversed email addres as below.

WITH SampleData AS (
 SELECT 'test1@gmail.comwww' AS email UNION ALL
 SELECT 'test2.com@gmail.comzzz' 
)
SELECT
 left(email, len(email) - charindex('moc.', reverse(email)) + 1)
FROM SampleData

Update after @Usman comment: This will ignore any not .com domains. However the ideal way would be to use a CLR Regex with single niice expression. :-)

WITH SampleData AS (
 SELECT 'test1@gmail.comwww' AS email UNION ALL
 SELECT 'test2.com@gmail.comzzz' UNION ALL
 SELECT 'test3.com@gmail.cozzz'
)
SELECT
 LEFT(email, len(email) - charindex('moc.', reverse(substring(email, patindex('%@%.com%', email), sign( patindex('%@%.com%', email)) * len(email)))) + 1)
FROM SampleData
more ▼

answered Aug 09 '12 at 07:59 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

I agree... Nice solution!
Aug 09 '12 at 08:00 AM sp_lock
@Pavel This solution seems to be vulnerable to strings like ' test2.com@gmail.cozzz' ;) But +1 for the core idea.
Aug 09 '12 at 10:12 AM Usman Butt
Goot point @Usman, added a second possibility which will ignore any not .com domains.
Aug 09 '12 at 11:22 AM Pavel Pawlowski
@Pavel But I guess you are still missing the mysql part ;) Please see my answer and any re-shot at this with mysql perspective and REGEXP? Since you are very good at that, I just wanted to know what REGEXP would account for this?
Aug 09 '12 at 11:33 AM Usman Butt
Yeah @Usman, you are right. This is for the SQL Server. I wil post a RegEx for this as commen to your post. :-)
Aug 09 '12 at 11:45 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

Hey guys you missed the most important part that the OP tagged it in mysql not MSSQL ;)

So just translating Pavel's solution with an additional check

SELECT  --THE FOLLOWING EXPRESSION CAN BE PART OF WHERE CLAUSE WHILE UPDATING THE DATA
        CASE WHEN LEFT(REVERSE(email), LOCATE('.', REVERSE(email))) LIKE '%moc.'
             THEN LEFT(email,
                        CHAR_LENGTH(email) - LOCATE('moc.', REVERSE(email)) + 1)
             ELSE email
        END
FROM    SampleData
I am still not sure it is fool proof solution and any mysql guru could come up with a better solution since REGEXP is native to mysql.(@Pavel I am sure you are going to like REGEXP being native in mysql. So would you like to take a shot at this :))
more ▼

answered Aug 09 '12 at 09:43 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Hi @Usman, here is RegEx which will return any email but only up to the .com. It will ignore anything behind the .com. If the email witll end with .anything which is not .com it will return it completly.

(.+?@\w+?\.(?:com|(?:(?<!com)\w*)))
Aug 09 '12 at 11:49 AM Pavel Pawlowski
@Pavel Thanks. I knew it would have been a piece of cake for you ;)
Aug 09 '12 at 11:53 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

Is this what you are after?

Is it basic and would need changing for other addresses like .co.uk .net etc

DECLARE @string varchar(50) 
SET @string = 'jl@test.comjy'
SELECT 
 LEFT( @string,charindex('.com',@string) + 3)
more ▼

answered Aug 09 '12 at 07:42 AM

sp_lock gravatar image

sp_lock
9k 24 27 30

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x362
x103
x74
x65
x20

asked: Aug 09 '12 at 06:35 AM

Seen: 1038 times

Last Updated: Aug 09 '12 at 11:53 AM