Write SQL which grab only email address 1

[amardesai2005]

Hello Everyone

I would like to write a code which grab email address from following string.

"X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=Burke;g=Andrew;i=P;%SMTP:BurkeA@xyz.com"

Please help me out.

Thanks
AD

 

[vongrunt]

For this particular case (mail at the end of string, after token 'SMTP:'):

declare @s varchar(128)
set @s = 'X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=Burke;g=Andrew;i=P;%SMTP:BurkeA@xyz.com'
select substring(@s, charindex('SMTP:', @s)+5, 200)

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[amardesai2005]

Thank you so much.

AD

 

[VIJGAN]

DECLARE @s VARCHAR(100)
SET @s = 'X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=Burke;g=Andrew;i=P;%SMTP:BurkeA@xyz.com'
SELECT SUBSTRING(@s, CHARINDEX('SMTP:', @s)+ 5,
LEN(@s) - (CHARINDEX('SMTP:', @s)+ 4))

 

[amardesai2005]

Hello guys, I have try to implement codeing what you suggest me. but it didn't work properly. I need your help again.

Let me explain you in detail, I have table called exchange and there is a field called proxy_addresses which has following data.

X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=Burke;g=Andrew;i=P;%SMTP:BurkeA@test.com


SMTP:EngJ@test.com%MS:test/USA/GEJLE00%X500:/o=test/ou=USA/cn=
Recipients/cn=GEJLE00%X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=Eng;g=

SMTP:MastersonJ@test.com%MS:test/USA/GEJXM00%X500:/o=test/ou=
USA/cn=Recipients/cn=GEJXM00%X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=

X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=Brady;g=Ken;i= ;%SMTP:BradyK@test.com

MS:test/USA/GELSW002%SMTPowersL@test.com%X500:/o=test/ou=
USA/cn=Recipients/cn=GELSW00%X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=Po

So, I created view with following code

SUBSTRING(Proxy_Addresses, CHARINDEX('SMTP:', Proxy_Addresses)+ 5, LEN(Proxy_Addresses) - (CHARINDEX('SMTP:', Proxy_Addresses)+ 4)) AS EmailAddresses

When i open the view i got following output

BurkeA@test.com

EngJ@test.com%MS:test/USA/GEJLE00%X500:/o=test/ou=USA/cn=
Recipients/cn=GEJLE00%X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=Eng;g=

MastersonJ@test.com%MS:test/USA/GEJXM00%X500:/o=test/ou=
USA/cn=Recipients/cn=GEJXM00%X400:c=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=

BradyK@test.com

PowersL@test.com%X500:/o=test/ou=USA/cn=Recipients/cn=GELSW00%X400:c
=US;a=KNA;p=Enterprise;o=KNA US New Jersey;s=Po


As i said i need only email address, How can i remove other characters

PLease help me out.
Thanks
AD

 

[vongrunt]

This is possible with single query... but code is kind of messy and redundant. Personally I would rather write user-defined function like this one:

create function fn_grabtoken( @s varchar(512), @delimFrom varchar(16), @delimTo varchar(16) )
returns varchar(255)
as
begin
	declare @ret varchar(255)
	declare @start smallint, @end smallint
	set @start = charindex(@delimFrom, @s)
	if @start > 0
	begin
		set @start = @start + len(@delimFrom)
		set @end = charindex(@delimTo, @s, @start)
		if @end = 0 set @end = len(@s) + 1

		set @ret = substring(@s, @start, @end-@start)
	end
	return @ret
end
go

and then extract email address(es) with [!]dbo.fn_grabtoken(s, 'SMTP:', '%')[/!].

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[amardesai2005]

Thank you so much, It work absulutaly fine.

Thanks again
AD