By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,101 Members | 1,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,101 IT Pros & Developers. It's quick & easy.

How to extract part of a string

P: n/a
Is there a function that will extract part of a string when the data you
want does not occur in a specific position?
Field "REF" is varchar(80) and contains an email subject line and the email
recipients contact name
Example data:
Rec_ID REF
1 Here is the information you requested (oc:Johm
Smith)
2 Thanks for attending our seminar (oc:Peggy Sue
Johnson)
3 Re: Our meeting yesterday (oc:Donald A. Duck)

What I need to extract is the contact name that is in parenthesis after the
oc:
The name is always in parenthesis and occurs immediately after "oc:" - no
spaces after the "oc:"

Thanks.


Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You could use PATINDEX or CHARINDEX along with a SUBSTRING in the
select statement to get what you are looking for. PATINDEX or CHARINDEX
will return the starting position of a particular string, you can use
it with SUBSTRING to extract the string that follows the oc:

Let me know if you need the actual SQL Statement to do what you are
looking for.

Jul 23 '05 #2

P: n/a
Thanks but I can't seem to get the syntax correct to extract this. Do you
have a sample SQL statement to do this?
"SQL_developer" <vl******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
You could use PATINDEX or CHARINDEX along with a SUBSTRING in the
select statement to get what you are looking for. PATINDEX or CHARINDEX
will return the starting position of a particular string, you can use
it with SUBSTRING to extract the string that follows the oc:

Let me know if you need the actual SQL Statement to do what you are
looking for.

Jul 23 '05 #3

P: n/a
SELECT SUBSTRING([Ref], CHARINDEX('(oc:', [Ref]) + 4, LEN([Ref]) -
(CHARINDEX('(oc:', [Ref]) + 4) - 1)
GeoSynch
"rdraider" <rd******@sbcglobal.net> wrote in message
news:wz****************@newssvr21.news.prodigy.com ...
Thanks but I can't seem to get the syntax correct to extract this. Do you
have a sample SQL statement to do this?
"SQL_developer" <vl******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
You could use PATINDEX or CHARINDEX along with a SUBSTRING in the
select statement to get what you are looking for. PATINDEX or CHARINDEX
will return the starting position of a particular string, you can use
it with SUBSTRING to extract the string that follows the oc:

Let me know if you need the actual SQL Statement to do what you are
looking for.


Jul 23 '05 #4

P: n/a
I started getting the error: "Invalid length parameter passed to the
substring function." and it cut off the last letter of the contact name so I
removed the -1 from the end of the query. Then I added the WHERE to just
give me the recordS with contact names (I found some records that just seem
to be garbage).
This query works well:
SELECT SUBSTRING([Ref], CHARINDEX('(oc:', [Ref]) + 4, LEN([Ref]) -
(CHARINDEX('(oc:', [Ref]) + 4))
FROM TABLENAME
WHERE REF LIKE '%(oc:%'
Thanks for your help, you saved me a lot of time.

"GeoSynch" <Sp********@Casablanca.com> wrote in message
news:Up*****************@newsread2.news.pas.earthl ink.net...
SELECT SUBSTRING([Ref], CHARINDEX('(oc:', [Ref]) + 4, LEN([Ref]) -
(CHARINDEX('(oc:', [Ref]) + 4) - 1)
GeoSynch
"rdraider" <rd******@sbcglobal.net> wrote in message
news:wz****************@newssvr21.news.prodigy.com ...
Thanks but I can't seem to get the syntax correct to extract this. Do
you have a sample SQL statement to do this?
"SQL_developer" <vl******@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
You could use PATINDEX or CHARINDEX along with a SUBSTRING in the
select statement to get what you are looking for. PATINDEX or CHARINDEX
will return the starting position of a particular string, you can use
it with SUBSTRING to extract the string that follows the oc:

Let me know if you need the actual SQL Statement to do what you are
looking for.



Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.