Search result that has a C and a number after the C in the Part Number Field

[jamie61880]

I have a search results page that lists part numbers that engineers have to work on, but I only want part numbers displayed on the results page that have a C and then a number after the C in the part number. How do I write this in the Where statement in my CFQuery? If I write Where Part_Number Like 'C%', it lists all the parts that have anything after a C in the part number like CBT-BGA. I want it to find only part numbers like this: C4560. I also tried this: Where Part_Number Like '^C[0-9]', but this doesn't show any results. Thanks for your help.

[BKBK]

What database brand are you using? I ask because pattern-matching depends on the database brand.

For example, in MySQL, you can use the operator RLIKE for regular-expression pattern-matching:

SELECT *
FROM codeTBL
WHERE code RLIKE 'b[0-9][0-9][0-9][0-9]' -- matches B6942

[jamie61880]

Thank you! This worked, but I had to remove the "R" before the Like in your code above. Some part numbers have 4 digits and some have 5 or maybe even more than that. Do I have to do an Or statement for all these different variations with how many digits might be in the part number? I did this and it works, but what if we have a part number that has 10 digits in it and I don't have that in the where statement? It won't display that then. Also, I'm not sure what database brand I have. How do I tell if I'm using SQL or MySQL?

[BKBK]

I am glad to hear that it is working.

There is an easy way to find out which database brand you're using. Go to the datasources page in the ColdFusion Administrator. Look in the Driver column. What is the driver name corresponding to the datasource of the CFQuery?

[BKBK]

As it works without the "R", you are maybe on SQL Server. Try the following:

WHERE code LIKE 'b[0-9][0-9][0-9][0-9]|c[1-3][0-9][0-6][4-8][0-9][1-7]' 
-- matches B6942 and C280564

[jamie61880]

Yes, we're on Microsoft SQL Server. This code does not work:

'b[0-9][0-9][0-9][0-9]|c[1-3][0-9][0-6][4-8][0-9][1-7]'

 Was this to capture all the digits?

It does work like this:

And (Part_Number LIKE 'C[0-9][0-9][0-9][0-9]' Or Part_Number LIKE 'C[0-9][0-9][0-9][0-9][0-9]')

[jamie61880 · Accepted solution]

This actually works the best because I don't have to specify a certain number of digits after the C:

And Part_Number Like '[C][0-9]%'