CODEWITHSUNDEEP
sql-server
user1296762
user1296762

Reputation: 512

Separating firstname surname

If the original field looks like paul@yates then this syntax picks out the surname correctly

  substring(surname,CHARINDEX('@',surname+'@')+1,LEN(name3))

however if the field is paul@b@yates then the surname looks like @b@yates. I want the middle letter to be dropped so it picks only the surname out. any ideas?

Upvotes: 0

Views: 102

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

Here's an example for you

declare @t table (name varchar(max));
insert @t select
'john' union all select
'john@t@bill' union all select
'joe@public';

select firstname=left(name,-1+charindex('@',name+'@')),
       surname=case when name like '%@%' then
         stuff(name,1,len(name)+1-charindex('@',reverse(name)+'@'),'')
         end
from @t;

-- results

FIRSTNAME   SURNAME
john    (null)
john    bill
joe public

Upvotes: 0

Alex K.
Alex K.

Reputation: 175748

You can;

;with T(name) as (
    select 'paul@yates' union
    select 'paul@b@yates'
)
select 
    right(name, charindex('@', reverse(name) + '@') - 1) 
from T

>>

yates
yates

Upvotes: 1

fancy
fancy

Reputation: 2149

you could reverse the array, split it till you find the first "@", take that part and reverse it again.

if this is java, there should be an array.reverse function, otherwise you possibly need to write it on your own.

also you could cut the string in pieces until there are no mor "@" signs left and then take the last part (the substring should return "-1" or something), but i like my first idea better.

Upvotes: 0

Related Questions