Remove the last character from Variable String 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?
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) &lt;&gt; col ;