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
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)
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
@Pavel This solution seems to be vulnerable to strings like '
test2.com@gmail.cozzz' ;) But +1 for the core idea.
Goot point @Usman, added a second possibility which will ignore any not **.com** domains.
@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?
Yeah @Usman, you are right. This is for the SQL Server. I wil post a RegEx for this as commen to your post. :-)
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 :))
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|(?:(?
@Pavel Thanks. I knew it would have been a piece of cake for you ;)
10 People are following this question.
