Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fralo
Posting Yak Master
161 Posts |
 Posted - 2013-06-03 : 11:43:16
|
| Hi all,I have a stored procedure which looks like this:ALTER PROCEDURE [dbo].[specso_SexOffenses] @StartDate DATE, @Choose VARCHAR(75)ASBEGINAs you can see, it receives an incoming parameter, @choose, which can be combinations of:'Predator, 'Offender', 'Previous'.I need to form several queries based on what combination of values the proc receives. How do I search the list to see what it contains and then base my query on that. Can I use SUBSTRING?IF SUBSTRING(@Choose, 'Predator') and SUBSTRING(@Choose, 'Previous')...ELSE IF SUBSTRING(@Choose, 'Offense')...Thanks for your help. |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-03 : 12:27:33
|
| You can use PATINDEX() function[CODE]DECLARE @Choose VARCHAR(75);SET @Choose = 'Predator Offense'SELECT PATINDEX( '%Predator%', @Choose), PATINDEX( '%Previous%', @Choose), PATINDEX( '%Offense%', @Choose)[/CODE] |
 |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2013-06-03 : 13:15:04
|
| Thanks, but PATINDEX returns the starting position doesn't it? I need to simply know if it occurs within the incoming array of values.pseudocode:If @choose contains 'Predator" and if @choose contains "Offense. |
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-06-03 : 13:27:24
|
It returns zero if there is no match.you can do ( if PATINDEX(...) > 0) to find a matchquote:
Originally posted by fralo
Thanks, but PATINDEX returns the starting position doesn't it? I need to simply know if it occurs within the incoming array of values.pseudocode:If @choose contains 'Predator" and if @choose contains "Offense.
|
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2013-06-03 : 14:04:14
|
| Thanks again. I had already been looking and discoverd CHARINDEX works as well. |
|
|
|
|
|
|
|
|
Substring an Array