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, 2012 at 06:35 AM in Default

avatar image

Vishvas
0 1 1 3

(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, 2012 at 07:59 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

I agree... Nice solution!

Aug 09, 2012 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, 2012 at 10:12 AM Usman Butt

Goot point @Usman, added a second possibility which will ignore any not .com domains.

Aug 09, 2012 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, 2012 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, 2012 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, 2012 at 09:43 AM

avatar image

Usman Butt
14k 6 13 21

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, 2012 at 11:49 AM Pavel Pawlowski

@Pavel Thanks. I knew it would have been a piece of cake for you ;)

Aug 09, 2012 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, 2012 at 07:42 AM

avatar image

sp_lock
10.5k 27 37 37

(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.

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:

x426
x148
x100
x96
x33

asked: Aug 09, 2012 at 06:35 AM

Seen: 1863 times

Last Updated: Aug 09, 2012 at 11:53 AM

Copyright 2016 Redgate Software. Privacy Policy