467,118 Members | 984 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,118 developers. It's quick & easy.

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,
lq
Jul 20 '05 #1
  • viewed: 9265
Share:
4 Replies
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
Jul 20 '05 #2
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.

Jul 20 '05 #3
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.

Jul 20 '05 #4
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
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Alvin Sebastian | last post: by
18 posts views Thread by Steve Litvack | last post: by
5 posts views Thread by Tim::.. | last post: by
1 post views Thread by Andrew Morton | last post: by
1 post views Thread by caine | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.