How to extract part of a string

[rdraider]

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.

[SQL_developer]

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.

[rdraider]

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.

[GeoSynch]

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.

[rdraider]

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.