Extract a string in a Stored Procedure

Is there anyway to extract part of a string in a stored procedure
using a parameter as the starting point?
For example, my string might read: x234y01zx567y07zx541y04z
My Parameter is an nvarchar and the value is: "x567y"
What I want to extract is the two charachters after the parameter, in
this case "07".
Can anyone shed some light on this problem?
Thanks,
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> Is there anyway to extract part of a string in a stored procedure
> using a parameter as the starting point?
> For example, my string might read: x234y01zx567y07zx541y04z
> My Parameter is an nvarchar and the value is: "x567y"
> What I want to extract is the two charachters after the parameter, in
> this case "07".
> Can anyone shed some light on this problem?

Looks like a combination of substring and charindex (or possibly
patindex) is what you need. I recommend that you use the SQL Server
Books Online to study all the string functions that SQL Server
offers. They are not that many, and not that extremely powerful, but
it's very useful to know them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks.
I'm on a crash project using MDSE and don't have immediate access to
Books Online though...
lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946FEC5AC5081Yazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > Is there anyway to extract part of a string in a stored procedure
> > using a parameter as the starting point?
> > For example, my string might read: x234y01zx567y07zx541y04z
> > My Parameter is an nvarchar and the value is: "x567y"
> > What I want to extract is the two charachters after the parameter, in
> > this case "07".
> > Can anyone shed some light on this problem?
> Looks like a combination of substring and charindex (or possibly
> patindex) is what you need. I recommend that you use the SQL Server
> Books Online to study all the string functions that SQL Server
> offers. They are not that many, and not that extremely powerful, but
> it's very useful to know them.|||I figured out how to do this:

substring(mystring,charindex(@.parameter,myString)+ len(@.parameter),2)

where @.parameter = 'x' + [myUserID] + 'y'

Thanks for pointing me in the right direction.

lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946FEC5AC5081Yazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > Is there anyway to extract part of a string in a stored procedure
> > using a parameter as the starting point?
> > For example, my string might read: x234y01zx567y07zx541y04z
> > My Parameter is an nvarchar and the value is: "x567y"
> > What I want to extract is the two charachters after the parameter, in
> > this case "07".
> > Can anyone shed some light on this problem?
> Looks like a combination of substring and charindex (or possibly
> patindex) is what you need. I recommend that you use the SQL Server
> Books Online to study all the string functions that SQL Server
> offers. They are not that many, and not that extremely powerful, but
> it's very useful to know them.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I'm on a crash project using MDSE and don't have immediate access to
> Books Online though...

You have. Check my signature.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp