Extract a string in a Stored Procedure

[Lauren Quantrell]

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,
lq

[Erland Sommarskog]

Lauren Quantrell (la*************@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, so****@algonet.se

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

[Lauren Quantrell]

Thanks.
I'm on a crash project using MDSE and don't have immediate access to
Books Online though...
lq

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Lauren Quantrell (la*************@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]

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 <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Lauren Quantrell (la*************@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]

Lauren Quantrell (la*************@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, so****@algonet.se

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