Find text with condition (emails)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find text with condition (emails)

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 06:29:51

I have a field in a datatable that includes several concatenated text values.

I'd like to strip out just one element of this field, by finding "email:" in this text, then the result to populate the email address that are found after this text.

So example data in the field would be: Name: SQLTeam Date: Today EMail: me@there.com

The code I want to write would just return "me@there.com" part.

Any ideas?

Many thanks
Humate

quote:
Originally posted by Michael Valentine Jones

It takes real skill to produce something good out of a giant mess.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 06:38:21

SELECT SUBSTRING(col,PATINDEX('%EMail:%',Col)+7,LEN(col)) FROM YourTable

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 06:56:45

great, that gets me address. How do I limit it, so any text after the email address is not included?

After each email address is the phrase "OWNER".

Can I include the email part, upto when this phrase appears?

I.e. Email: me@there.com Owner: me

would return only.

me@there.com

At the moment i get all the following text. Thanks again

quote:
Originally posted by Michael Valentine Jones

It takes real skill to produce something good out of a giant mess.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 07:06:46

[code]SELECT SUBSTRING(LEFT(Col,PATINDEX('%OWNER:%',Col)-1),PATINDEX('%EMail:%',LEFT(Col,PATINDEX('%OWNER:%',Col)-1))+7,LEN(LEFT(Col,PATINDEX('%OWNER:%',Col)-1))) FROM YourTable

[/code]

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 07:24:29

Hmm, the -1 seems to be causing me problems. I have an error saying; Invalid length parameter passed to the SUBSTRING function.

If I change each to +0, the query runs, but I need the -1 position point

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 08:21:25

Can I look for the first space after "Email:" and use this as the end point of the substring?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 08:25:31

quote:
Originally posted by Humate

Hmm, the -1 seems to be causing me problems. I have an error saying; Invalid length parameter passed to the SUBSTRING function.

If I change each to +0, the query runs, but I need the -1 position point

that means you have values without OWNER: pattern in it. Modify like below and see

SELECT SUBSTRING(LEFT(Col,CASE WHEN  PATINDEX('%OWNER:%',Col)>0 THEN PATINDEX('%OWNER:%',Col) ELSE LEN(Col)+1 END -1),PATINDEX('%EMail:%',LEFT(Col,CASE WHEN  PATINDEX('%OWNER:%',Col)>0 THEN PATINDEX('%OWNER:%',Col) ELSE LEN(Col)+1 END-1))+7,LEN(LEFT(Col,CASE WHEN  PATINDEX('%OWNER:%',Col)>0 THEN PATINDEX('%OWNER:%',Col) ELSE LEN(Col)+1 END-1))) FROM YourTable

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 08:36:38

That is correct, I have found a number of different anomolies in my older data.

Would it be possible to use the first space after the email start position? This would be ideal I think

I tried this with no luck

SELECT SUBSTRING(col,PATINDEX('%Email:%',col)+15,(CHARINDEX(' ', col,PATINDEX('%Email:%',col)+7))) FROM Mytable

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 08:48:26

tried modified query too? posted 09/22/2008 : 08:25:31 ?

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 08:51:43

Yes, it seems the phrase after "Email" has changed over time, so it doesn't work for all entires.

The only constant it the space after the email address it seems.

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 09:01:05

I think to find the text between "Email:" and the first space after "Email:" is the solution.

Should this work?

SELECT SUBSTRING(col,PATINDEX('%Email:%',col)+7,(CHARINDEX(' ', col,PATINDEX('%Email:%',col)+7))) FROM Mytable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 09:25:48

quote:
Originally posted by Humate

I think to find the text between "Email:" and the first space after "Email:" is the solution.

Should this work?

SELECT SUBSTRING(col,PATINDEX('%Email:%',col)+15,(CHARINDEX(' ', col,PATINDEX('%Email:%',col)+7))) FROM Mytable

whats the significance of 15?

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 09:34:30

My error, should be 7.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 09:37:41

quote:
Originally posted by Humate

My error, should be 7.

can you state clearly what you're trying to do?
As i understand what you want is text after Email:
but in some cases you'll have Owner: following this in which case you should take string only till start of Owner. is this what your requirement is?

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 09:45:03

Nearly. I'll try to be more clear.

I want to get all the text starting after "EMail:", until the next space appears (' ') in the string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 09:56:30

[code]SELECT LEFT(SUBSTRING(col,PATINDEX('%EMail:%',Col)+7,LEN(col)),CHARINDEX(' ',SUBSTRING(col,PATINDEX('%EMail:%',Col)+7,LEN(col)))-1) FROM YourTable[/code]

Humate
Posting Yak Master

101 Posts

Posted - 2008-09-22 : 10:00:19

Brilliant! This is exactly what I need

Thanks very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 10:58:35

quote:
Originally posted by Humate

Brilliant! This is exactly what I need

Thanks very much

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources