Multiple checks on one parameter?

[pechar]

Hi all,

I'm having a hard time creating a query with parameters. I have a table which contains ID numbers (not primary key) which are just a value attached to a customer. In a way like a password without which the user cannot continue to checkout on a site we're developing.

The problem is that we don't specify the exact format of this data and so need to make a check for a certain amount of zeros before the actual number. i.e. the values in the database can have a certain amount of zeros in front of the id number supplied by the customer. eg. "00456P" or "0000456P"...what we get from the user is 456P and we have to check if the value exists in the database whatever the number of zeros before the actual id number.

What I cant get to work is the following query where ? is the parameter passed from ASP.NET:

SELECT *  
FROM tb_Ids 
WHERE nr_id = ?    OR
     nr_id = CONCAT('0', ?) OR
     nr_id = CONCAT('00', ?) OR
     nr_id = CONCAT('000', ?) OR
     nr_id = CONCAT('0000', ?) OR
     nr_id = CONCAT('00000', ?) OR
     nr_id = CONCAT('000000', ?) OR
     nr_id = CONCAT('0000000', ?);

I know the syntax is not correct (i am getting an error and am sure its cuz of the way I use the parameter) but it is somewhat similar to what I'm looking for.

In fact when i tested it on database with a query analyser it works fine i.e. if I replace the ? with '456P'

So my question is how do I make multiple checks on one parameter in a query. Please don't tell me to use Stored Procedures since I cannot in this case

Thanks a lot and sorry for the long post but its the only way I can make it clear

[pechar]

Anyone Please??
I would appreciate anyone's help

Thanks

[ronverdonk]

Use a regular expression on this. So in order to select a value containing (prefix) 0-9 zeroes and chars ABC (your variable data), the statement is like

SELECT * FROM tb_Ids 
     WHERE nr_id REGEXP concat('^0{0,9}','ABC');
 

Ronald

[pechar]

Use a regular expression on this. So in order to select a value containing (prefix) 0-9 zeroes and chars ABC (your variable data), the statement is like

SELECT * FROM tb_Ids 
     WHERE nr_id REGEXP concat('^0{0,9}','ABC');
 

Ronald

Thanks a lot. I could marry you for this !!!!

I've been banging my head on the wall for ages!

Thanks again
Luk

[ronverdonk]

Thanks a lot. I could marry you for this !!!!

I've been banging my head on the wall for ages!

Thanks again
Luk

Glad it works for you. Btw: you can get the REGEXP conventions from the MySQL manual.

See you around.

Ronald