Remove the last character from Variable String

Question

I have a few variable data in the column "email".

For example:

roy2087@gmail.com123
sg123@yahoo.compori

How do I get rid of the characters after .com?

I used Replace(email,'.com%',gmail.com), however, we cannot use wildcard characters in Replace.

Any Suggestions?

Answer 1

Does mysql have regular expressions? If so, maybe this points in the right direction

select regexp_replace ('roy2087@gmail.com123', '\.com.*','.com') email from dual;

works in Oracle, not sure about mysql.

Answer 2

SELECT
    SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) ;

If you want update the strings from a table, chopping off what is on the right of .com:

UPDATE 
    tableX
SET
    col = SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) 
WHERE
    SUBSTRING_INDEX(col, '.com', -1) <> col ;