How to select only the characters appearing before a specific symbol in a SQL Select statement

Question

I have strings in a database like this:

firstname.lastname@email.com/IMCLientName

And I only need the characters that appear before the @ symbol.

I am trying to find a simple way to do this in SQL.

score 37 · Accepted Answer · answered Nov 28 '11 at 16:46

37

DECLARE @email VARCHAR(100)
SET @email = 'firstname.lastname@email.com/IMCLientName'

SELECT SUBSTRING(@email,0, CHARINDEX('@',@email))

answered Nov 28 '11 at 16:46

Ian Nelson

57,123
20
76
103

What happens if the character isn't found? e.g. If `SET @email = 'firstname.lastname.email.com'`, what would be selected in this example? – qJake Sep 19 '14 at 18:15
3

@SpikeX an empty string would be selected. – Ian Nelson Sep 22 '14 at 07:40

score 6 · Answer 2 · answered Feb 12 '18 at 21:21

Building on Ian Nelson's example we could add a quick check so we return the initial value if we don't find our index.

DECLARE @email VARCHAR(100)
SET @email = 'firstname.lastname.email.com/IMCLientName'

SELECT  CASE WHEN CHARINDEX('@',@email) > 0
            THEN SUBSTRING(@email,0, CHARINDEX('@',@email))
            ELSE @email
        END AS email

This would return 'firstname.lastname.email.com/IMCLientName'. If you used 'firstname.lastname@email.com/IMCLientName' then you would receive 'firstname.lastname' as a result.

How to select only the characters appearing before a specific symbol in a SQL Select statement

2 Answers2

Linked