Retrieve first email from string in SQL row

Question

Good morning, I have an email column in a table that contains in some rows more than one email, separated by a semicolon. Actually i am trying to retrieve the email with this query

-- cdu_mail is the column

select
    ltrim(rtrim(cdu_mail)),
    substring(ltrim(RTRIM(cdu_mail)), 0, charindex(';',ltrim(RTRIM(cdu_mail)))) 
FROM Clientes

If i have more than one mail in the row the query retrieves correctly the first email, but if there is only one email in the row the result is blank.

enter image description here

What do i need to change to get all the mails correctly?

Thank you.

Possible [duplicate](http://stackoverflow.com/a/5493616/679449). Just uses comma instead of semicolon. — Kermit, Sep 04 '12 at 14:13

score 3 · Accepted Answer · answered Sep 04 '12 at 14:15

You can also tack on a semi-colon at the end of the column to guarantee a semi-colon will always exist, and avoid using case expressions:

select     ltrim(rtrim(cdu_mail)),     
    substring(ltrim(RTRIM(cdu_mail)), 0, charindex(';',ltrim(RTRIM(cdu_mail + ';')))) 
FROM #Clientes

score 1 · Answer 2 · answered Sep 04 '12 at 14:12

You can put this into a case statement:

select (case when cdu_email like '%;'
             then ltrim(rtrim(cdu_mail)), substring(ltrim(RTRIM(cdu_mail)), 0,
                                                    charindex(';',ltrim(RTRIM(cdu_mail))))
             else cdu_email
        end) as firstEmail
from Clientes

The problem is that one email does not end in a ';', so the charindex() returns 0.

I realize, you can also solve this by appending a semicolon for that particular search as well:

select ltrim(rtrim(cdu_mail)), substring(ltrim(RTRIM(cdu_mail)), 0,
                                         charindex(';',ltrim(RTRIM(cdu_mail+';'))))
from Clientes

score 1 · Answer 3 · answered Sep 04 '12 at 14:14

1

    select  case when charindex(';', cdu_mail) = 0 then cdu_mail else
                    substring(cdu_mail,1, charindex(';', cdu_mail) - 1) end
    from    clientes

answered Sep 04 '12 at 14:14

paul

21,653
1
53
54

score 0 · Answer 4 · answered Sep 04 '12 at 14:26

I don't know what version of SQL syntax you are using but in MySQL you can do something like this:

SELECT 
    IF( LOCATE(  ";", TRIM( cdu_mail ) ) >1, LEFT( TRIM( cdu_mail ), 
    LOCATE(  ";", TRIM( cdu_mail ) ) -1 ), 
    TRIM( cdu_mail ) ) AS email
FROM Clientes

Effectively if there is a ";", pick the first email out, otherwise just get the value of the field (since there is only one email).

You might also want to introduce checking for NULL values with a WHERE clause.

HTH

Retrieve first email from string in SQL row

4 Answers4