CODEWITHSUNDEEP
sql-serversubstring
daw dasf
daw dasf

Reputation: 35

SQL Server Ltrim or substring to get the email address only

I have this scenario:

John Doe [email protected]
John [email protected]

I want away that would only parse out this as following:

John Doe
John

In other words, removing the email address only.

I have tried this:

SELECT
                (CASE WHEN CHARINDEX(‘ ‘, REVERSE(column1)) > 0 THEN Left(column1, CHARINDEX(‘ ‘, REVERSE(column1)) – 1)
                ELSE column1
                END) AS column1
FROM Book1

Upvotes: 0

Views: 384

Answers (2)

M.Ali
M.Ali

Reputation: 69504

DECLARE @Str VARCHAR(MAX) = 'John Doe [email protected]'


 SELECT LEFT(@Str, LEN(@Str) - LEN(REVERSE(SUBSTRING(REVERSE(@Str), 1, CHARINDEX(' ', REVERSE(@Str)) - 1))))

Upvotes: 0

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

You're on the right track... just add the len function and drop the "minus 1"

SELECT  (CASE WHEN CHARINDEX(' ', REVERSE(column1)) > 0 THEN Left(column1, len (column1) - CHARINDEX(' ', REVERSE(column1)))
                ELSE column1
                END) AS column1
FROM Book1

Upvotes: 1

Related Questions