May 28, 2013 at 7:19 am
I am trying to write a where clause that tests for a string which may or may not contain a specific character. Specifically, I need to check if the string is '%', and if not I need to search for a match to the string that may or may not contain a /. The format of the string will always be AAA(space OR slash)AAAA. I was thinking something like the following, but that obviously doesn't work. Any suggestions?
where DIRECTED LIKE
case
when @directed = '%' THEN '%'
when @directed != '%' THEN (('%' + @directed + '%') OR ('%' + REPLACE(@directed ,'','/') + '%'))
end
May 28, 2013 at 7:40 am
Mark Harley (5/28/2013)
I am trying to write a where clause that tests for a string which may or may not contain a specific character. Specifically, I need to check if the string is '%', and if not I need to search for a match to the string that may or may not contain a /. The format of the string will always be AAA(space OR slash)AAAA. I was thinking something like the following, but that obviously doesn't work. Any suggestions?where DIRECTED LIKE
case
when @directed = '%' THEN '%'
when @directed != '%' THEN (('%' + @directed + '%') OR ('%' + REPLACE(@directed ,'','/') + '%'))
end
You are trying to use a case expression to conditionally change the structure of the query. You can't use a where clause like that.
You might want to take a look at this article from Gail.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2013 at 8:43 am
Wouldn't the following work?
WHERE Directed LIKE '%' + REPLACE(@Directed, '/', '[ /]') + '%'
If the @Directed variable is '%', the comparison is to '%%%', functionally equivalent to '%'
If the @Directed variable is 'AAA/AAAA', the comparison is '%AAA[ /]AAAA%' and the percent signs won't really matter, but the [ /] stands for either a space or slash.
If it is possible for the @Directed variable to have either a space or a slash, then a second REPLACE function to catch the other option is needed:
WHERE Directed LIKE '%' + REPLACE(REPLACE(@Directed, '/', '[ /]'), ' ', '[ /]') + '%'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply