3

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

For example:

[email protected]
[email protected]

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?

2 Answers 2

3
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 ;
0

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

select regexp_replace ('[email protected]', '\.com.*','.com') email from dual;

works in Oracle, not sure about mysql.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.