• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

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

Engaged ,
May 10, 2023 May 10, 2023

Copy link to clipboard

Copied

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.

Views

242

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Engaged , May 11, 2023 May 11, 2023

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]%'

Votes

Translate

Translate
Community Expert ,
May 10, 2023 May 10, 2023

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
May 10, 2023 May 10, 2023

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 10, 2023 May 10, 2023

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 10, 2023 May 10, 2023

Copy link to clipboard

Copied

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

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
May 11, 2023 May 11, 2023

Copy link to clipboard

Copied

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]')

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
May 11, 2023 May 11, 2023

Copy link to clipboard

Copied

LATEST

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]%'

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation