March 11, 2014 at 4:13 am
Dear All,
I am new to this forum, i have a situation where is need to get email address from a varchar column. Here is some sample data for five records; The format can be change. Please suggest some solution,thanks
dummy text;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk
Admissions dummy text;
T: +44 (0)1234 4444;
E: xyz@co.uk;
dummy text;
dummy text;
Tel: +123 32323 33;
Email: test@yahoo.com;
dummy text;
t: +88 (0) 115 333 5553;
f: +99 (0) 115 222 8888
e: dummy@gmail.com;
dummy text;
t: +11 (0) 222 222 2222;
e: myemail@test.com;
March 11, 2014 at 5:05 am
You need to use the CHARINDEX function to determine the start position (semi column or space) and end-position of the e-mail part inside the string.
if object_id('tempdb..#test') is not null
drop table #test
create table #test (value nvarchar(500))
insert into #test (value)
values ('Tel: +44 (0)1234 566788;Email: bbc@co.uk')
, ('Admissions dummy text;T: +44 (0)1234 4444;E: xyz@co.uk;w:http://www.yahoo.com/faqs;')
, ('dummy text;dummy text;Tel: +123 32323 33;Email: test@yahoo.com;')
select
substring(value
-- determine start_position (temporarily replace spaces for semi_columns)
, len(value)
- charindex(';', reverse(replace(value, ' ', ';')), charindex('@', reverse(value)))
+ 2
-- determine end_position and substract start_position (same as above)
, charindex(';', value + ';', charindex('@', value))
- (len(value)
- charindex(';', reverse(replace(value, ' ', ';')), charindex('@', reverse(value)))
+ 2
)
)
from #test
In my solution I've replace (only in the code) all spaces with semi_columns when determining the start- and endposition. Also added an extra semi-column so I know there is one present when determining the endposition. I've also had to reverse the string to determine the first presence of a semi-column before the @-sign to determine the startposition.
The code to determine the startposition is copied in the code that determines the endposition, because the SUBSTRING function needs the startposition + length of a string instead of startposition + endposition.
March 11, 2014 at 5:35 am
It's almost impossible to build solution which will cover all posible permitations of something which looks like the free-text.
He is a bit more comprehensive logic which cover most of your samples and a bit more:
declare @table table (val varchar(255))
insert @table select
'dummy text;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk
'
insert @table select
'dummy text no char at the end;
Tel: +44 (0)1234 566788;
Email: bbc@co.uk'
insert @table select
'Admissions dummy text;
T: +44 (0)1234 4444;
E: xyz@co.uk;'
insert @table select
'du.mmy text;
dummy text;
Tel: +123 32323 33;
Email: test@yahoo.com;'
insert @table select
'dummy text;
t: +88 (0) 115 333 5553;
f: +99 (0) 115 222 8888
e: dummy@gmail.com;'
insert @table select
'dum-m_y.123 text;
t: +11 (0) 222 222 2222;
e: myemail@test_d.com;
insert @table select
'dummy text;
t: +11 (0) 222 222 2222;
NO EMAIL
select ep1.pN + ep2.pN as email
from @table
outer apply (select nullif(charindex('@',val),0)) at(pos)
outer apply (select reverse(left(val,at.pos))) epr1(p)
outer apply (select reverse(left(epr1.p,charindex(' ',epr1.p)))) ep1(pN)
outer apply (select patindex('%[^A-Z0-9-._]%',substring(val,at.pos+1,len(val)))) apr2(pos)
outer apply (select substring(val,at.pos+1,isnull(nullif(apr2.pos,0)-1,len(val)))) ep2(pN)
If you are going to use it for one-off data conversion you can leave it as it is. If you want to use it more than in one place, you will do better by turning it to iTVF function.
Also, you might need to enhance the pattern to cover more cases.
It is not bullet-proof logic, as if your "free-text" will contain @ not in the email - it will fail...
March 11, 2014 at 6:22 am
Dear I have 1800 records and i am getting "Invalid length parameter passed to the LEFT or SUBSTRING function." error message.
March 11, 2014 at 6:30 am
The code in my post does NOT check if an email address is present in the string. If you have values without an @-sign, the code will fail. You can add a "WHERE value LIKE '%@%' " clause to the query.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply