question

Vishvas avatar image
Vishvas asked

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
queryselectmysqlhomeworkjoin
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I agree... Nice solution!
0 Likes 0 ·
@Pavel This solution seems to be vulnerable to strings like ' test2.com@gmail.cozzz' ;) But +1 for the core idea.
0 Likes 0 ·
Goot point @Usman, added a second possibility which will ignore any not **.com** domains.
0 Likes 0 ·
@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?
0 Likes 0 ·
Yeah @Usman, you are right. This is for the SQL Server. I wil post a RegEx for this as commen to your post. :-)
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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 :))
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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|(?:(?
1 Like 1 ·
@Pavel Thanks. I knew it would have been a piece of cake for you ;)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.