Need to use wildcard while using <= and >=

[NeeNee]

I have a query which searches a range, but I need to modify it to use all wildcards.

If I use:
WHERE (EM_PAYROLL_UNIT >= '0%%%') AND (EM_PAYROLL_UNIT <= 'Z%%%')
it works, but if I use
WHERE (EM_PAYROLL_UNIT >= '%%%%') AND (EM_PAYROLL_UNIT <= '%%%%')
It does not work.

Is it possible to make this work, or do I need to create 2 queries based on the parameters passed to the query.

Denis
Programmer, Canada

 

[gmmastros]

Can you post some example data to help clarify your question? I've read it a couple times now, and I'm still not sure what you are looking for. Sorry.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[swetab]

You can try this
WHERE (EM_PAYROLL_UNIT >= '[0-9][0-9][0-9][0-9]') AND (EM_PAYROLL_UNIT <= '[A-Z][0-9][0-9][0-9]')


I am not sure is this is what you are looking at.

Good luck

 

[NeeNee]

Hi George,
Thanks.

The user wants to search a range of payroll units (5 charater string), but he wants to be able to view them all or a range by entering FROM and TO values. He also wants to be able to leave the field blank to display them all.

I can get the query to work by using the code above with leading characters followed by the wildcard caracter (same as LIKE), but if I place all wildcards "%", the query does not return any records.

I can't get the Between statement to work, I assume its because I am searching strings.

Can I use the query as is, or is there a better way to search a range?

Thank you,

Denis
Programmer, Canada

 

[r937]

between statements work just fine on strings

where exactly does the user get to enter the from and to values? a web page? a desktop app?

r937.com | rudy.ca

 

[gmmastros]

Please see this thread as an example of sample data. thread183-1247204

Because sample data was provided by the person asking the question, it was answered faster.

For example, select top 10 EM_PAYROLL_UNIT From SomeTable. Copy/paste the results here. Then, show how the user is entering the search parameters. Based on this information, we should be able to help you better.

Usually, people accomodate missing parameters by using an OR in the where clause. For example, if @From = '' when the user wants ALL the data, then...

Where @From = '' or (EM_PAYROLL_UNIT >= '0%%%' AND EM_PAYROLL_UNIT <= 'Z%%%')

This is just an example usage. Hope it helps.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NeeNee]

The user enters the parameters from in a cobol program where I convert them to inline SQL using MS SQL Server.

I can now get the Between to work, but only if I specify a value, but I still can't get it to work if the parameters are all wildcards "%" as you can do using the LIKE statement.



Denis
Programmer, Canada

 

[SQLDenis]

that's because these are the same
WHERE (EM_PAYROLL_UNIT >= '%%%%') AND (EM_PAYROLL_UNIT <= '%%%%')


that's the same as saying where value >= 1 and value <= 1 (meaning =1)

the best way to handle (IMNSHO) this is to do something like this

if @var1 is null and @Var2 is null (or blank, don't know how you have it set up)
begin
select * from table <without where clause>
end
else
begin
select *
from table
where <add where clause here>


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

I'm with Denis on this one, that is what I would do too. It also has the advantage of if they give the whole ccorrect range then you can have a branch of the if that searches for that specifically without using wild card characters which will improve performance. If you still need to specify partial ranges how are they specifying? If they are giving say the first three characters, you can write an if branch that looks at the left three. As to them not specifying the whole range , I'd need to see sample data in order to suggest the best way to do that but wildcards can only be used with the like statment and should always be avoided as it means the indexes are useless.

All this should be in a stored proc rather than an on-the-fly SQL statement.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLBill]

What is the purpose of the multiple %'s? The use of % means 'any number of characters'. So %%%%% is the same as %. Plus, unless I am wrong...wildcards can only be used with LIKE. So this:

WHERE (EM_PAYROLL_UNIT >= '0%%%') AND (EM_PAYROLL_UNIT <= 'Z%%%')

Would look for a value greater than or equal to the STRING 0%%% and less than or equal to the STRING Z%%%. So you would only find values like Y%%%, Z%%% not Yell, or Yellow or Zoo or Zebra (all of which you would find with LIKE and those wildcards).

I could be wrong, but...........

-SQLBill


Posting advice: FAQ481-4875

 

[r937]

actually it will find Yellow



r937.com | rudy.ca