Now in email address column lets say we have a email like 'email@example.com', 'firstname.lastname@example.org', 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 = 'email@example.com' 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 'firstname.lastname@example.org' AS email UNION ALL SELECT '
email@example.com' ) 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 'firstname.lastname@example.org' AS email UNION ALL SELECT '
email@example.com' UNION ALL SELECT '
firstname.lastname@example.org' ) SELECT LEFT(email, len(email) - charindex('moc.', reverse(substring(email, patindex('%@%.com%', email), sign( patindex('%@%.com%', email)) * len(email)))) + 1) FROM SampleData
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 :))