CODEWITHSUNDEEP
sql-servercharindex
daw dasf
daw dasf

Reputation: 35

SQL Server rtrim ltrim and charindex

I have the following scenario John Doe [email protected] john [email protected]

I want away that I can detect the first right space and just exclude everything to the left so I just get the email address of the person. So: John Doe [email protected] should be [email protected] john [email protected] should be [email protected]

This is what i have 

Declare @test varchar(50)
Select @test = 'John Doe [email protected]'
SELECT Right(@test, CHARINDEX(' ', @test))

This is only giving me the email.com!

Thank you.

Upvotes: 2

Views: 1895

Answers (3)

m.h Taba
m.h Taba

Reputation: 1

Hi you can also try rtrim

Declare @test varchar(50)
Select @test = 'John Doe [email protected]'
SELECT RIGHT(@test, CHARINDEX(' ', REVERSE(rtrim(@test))))

Upvotes: 0

David Dubois
David Dubois

Reputation: 3932

CharIndex is returning 5, which is the position of the first space in your test string, between "John" and "Doe".

Right is therefore returning the right-most 5 characters of your test string, which are "l.com"

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Declare @test varchar(50)
Select @test = 'John Doe [email protected]'
SELECT RIGHT(@test, CHARINDEX(' ', REVERSE(@test)-1))

or a safer approach (if there are strings without space separator):

Declare @test varchar(50)
Select @test = '[email protected]'
SELECT 
    CASE 
        WHEN CHARINDEX(' ', REVERSE(@test)) > 0 THEN RIGHT(@test, CHARINDEX(' ', REVERSE(@test))-1)
        ELSE @test
    END

Upvotes: 3

Related Questions