Parsing a variable for a where clause

Question

Parsing a variable for a where clause

Mark Harley

SSCrazy

Points: 2259
More actions
May 29, 2013 at 8:35 am

#394868

I have a variable that gets set by a user on a web form, and the user can set that value to something along the lines of '2K + 1A3B'. I need to break this up for my where clause, and filter the query on each part of the string. For example, if the variable equals 2K + 1A3B, I want to a where clause similar to:
LIKE '%2K%' OR '%1A%' OR '%3B%'
I know this doesn't work, but is there an alternative?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Misread the requirement. Comment removed.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286589 More actions · Answer 2

In essence you are passing a delimited string where a space is your delimiter. You can use the DelimitedSplit8K function for this quite easily. You can find the code for that function and the explanation of how it works by following the link in my signature about splitting strings.

Here it is in action. Notice that I posted readily consumable sample data. This is something you should do in the future to make it easier on the volunteers around here to help you.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

SomeVal varchar(100)

)

insert #Something

select 'Part #82kilo' union all

select 'This is not found' union all

select 'My part number 231a3b8547 should be found'

declare @SearchVal varchar(20) = '2K + 1A3B'

select *

from #Something s

cross apply dbo.DelimitedSplit8K(@SearchVal, ' ') x

where s.SomeVal like '%' + x.Item + '%'

Now keep in mind that using a wildcard at the beginning of your search value will negate any indexing you have and force a table scan. If you have lots of data this will be horribly inefficient and very slow.

_______________________________________________________________

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/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Mark Harley (5/29/2013)
I have a variable that gets set by a user on a web form, and the user can set that value to something along the lines of '2K + 1A3B'. I need to break this up for my where clause, and filter the query on each part of the string. For example, if the variable equals 2K + 1A3B, I want to a where clause similar to:
LIKE '%2K%' OR '%1A%' OR '%3B%'
I know this doesn't work, but is there an alternative?

I guess you need generic solution?

There is no way to do this even using Jeff's function, until you specify exact rules of splitting the string into parts.

It's easy to see that it should be split on ' + ', but why 1A3B should be split into 1A and 3B?

What about if there is three or five characters there: 1AB or 1AB3C.

Any other combination?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mark Harley SSCrazy Points: 2259 More actions · Answer 4

Any other combination?

These are the possible combinations:

[1-6][A-Z]

[1-6][A-Z][A-Z][A-Z]

[1-6] + [1-6][A-Z][1-6][A-Z]

I'm beginning to wonder if this is something that should be handled in the application, and then passed through to the sp after it's been parsed.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Mark Harley (5/29/2013)
Any other combination?
These are the possible combinations:
[1-6][A-Z]
[1-6][A-Z][A-Z][A-Z]
[1-6] + [1-6][A-Z][1-6][A-Z]
I'm beginning to wonder if this is something that should be handled in the application, and then passed through to the sp after it's been parsed.

And what about parsing (splitting) rules?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mark Harley SSCrazy Points: 2259 More actions · Answer 6

Eugene Elutin (5/29/2013)
Mark Harley (5/29/2013)
Any other combination?
These are the possible combinations:
[1-6][A-Z]
[1-6][A-Z][A-Z][A-Z]
[1-6] + [1-6][A-Z][1-6][A-Z]
I'm beginning to wonder if this is something that should be handled in the application, and then passed through to the sp after it's been parsed.
And what about parsing (splitting) rules?

A numeric may or may not be followed by an alpha. If a numeric is followed by a series of alphas, it needs to be parsed into the numeric followed by each alpha. For example, 1ABC would become 1A 1B 1C (and each would be its own filter). Each individual numeric or numeric+alpha combination is its own filter. Does that make any sense?