Remove the 'name@' part of an email???

[columbo2]

Hi all,
I need to match urls against emails based on domains and I've decided the best way to go is to remove the http/

http://www etc

from the URLs the remove the name@ bit of the emails - then match.

The trouble is, I can't work out how to get SQL server to remove all characters from the start of the string up to and including the '@'

can anyone help???

thanks
C

 

[ca8msm]

Have a look at the CHARINDEX function.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[tran008]

create table myTable ( emailaddr varchar(50))
insert into myTable values ('JONES@JUSTIN.com')
insert into myTable values ('ANDERSON@test.com')
insert into myTable values ('ANDERSON@none.com')
insert into myTable values ('SMITH@smith.com')
go

select replace(LTrim(substring( emailaddr, charindex('@', emailaddr), 50)),'@','') from mytable

drop table myTable

 

[ptheriault]

is this what you are looking for?

[COLOR=blue]DECLARE[/color] @email [COLOR=blue]varchar[/color](255)
[COLOR=blue]SET[/color] @email = [COLOR=red]'youremail@somedomain.com'[/color]

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]SUBSTRING[/color](@email, [COLOR=#FF00FF]charindex[/color]([COLOR=red]'@'[/color],@email)+1,len(@email))

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

tran008,

You can simplify that with...

[COLOR=blue]Declare[/color] @myTable [COLOR=blue]Table[/color]( emailaddr [COLOR=blue]varchar[/color](50))
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @myTable [COLOR=blue]values[/color] ([COLOR=red]'JONES@JUSTIN.com'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @myTable [COLOR=blue]values[/color] ([COLOR=red]'ANDERSON@test.com'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @myTable [COLOR=blue]values[/color] ([COLOR=red]'ANDERSON@none.com'[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @myTable [COLOR=blue]values[/color] ([COLOR=red]'SMITH@smith.com'[/color])

[COLOR=blue]select[/color] [COLOR=#FF00FF]Right[/color](emailaddr, len(emailaddr)-[COLOR=#FF00FF]CharIndex[/color]([COLOR=red]'@'[/color], emailaddr))
[COLOR=blue]from[/color]   @mytable

My thinking here is... string manipulation is slow to begin with, so the less string functions the better.

-George

"the screen with the little boxes in the window." - Moron

 

[tran008]

George, you are right...to early in the morning, didn't have my coffee yet.

 

[gmmastros]

I'm just sipping my first cup too. I think it's going to be another balmy day here in south-eastern Pennsylvania. I gotta get my coffee in before it gets too hot.

-George

"the screen with the little boxes in the window." - Moron

 

[columbo2]

You lot are great, cheers!
Thanks for your help, much appreciated
That's working a treat.
ta
C