Coldfusion / SQL query with a dash (-) in the search field...

I have a query that looks up part numbers.  It is as follows in the code snippet below. Dashes need to be ignored (ie: if 'RRAL-1972' is entered into the search box, nothing is found..., yet RRAL1972 can be found.  How can I strip out the characters like the dash, or a pound sign?  Thanks in advance!!

<cfquery name="getCR" datasource="">
        SELECT * FROM tbl_cr
        WHERE cr_them LIKE '%#form.cr_them#%'
    </cfquery>

Open in new window

14_eastAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
How are the values stored in the database?   If they are always without a dash, then simply strip off the dash before searching

<cfset form.cr_them = replace(form.cr_them,"-","all")>


if the database sometimes can have a dash and some times not have one, then strip it out from both locations for the comparison.   So, do the above and then strip it out in the query as well..

where replace(cr_them,'-','') like '%form.cr_them%'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
14_eastAuthor Commented:
Nice... I had just found the answer... and came to update this question but see you have elaborated on my solution.  Thanks so much!!!
0
14_eastAuthor Commented:
Much thanks!!!
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

14_eastAuthor Commented:
Wait!!! one more question pertaining to this... can I strip out more than one character?  Like account for pound sign as well, etc?
0
14_eastAuthor Commented:
well.. I just answered my own question, but am wondering how to include the # in the query replace as well.
0
gdemariaCommented:
sure, you can just nest the replace function or use a regular expression replace function

<cfset form.cr_them = replace(replace(form.cr_them,"-","all"),"#","","all")>
0
14_eastAuthor Commented:
very nice, thank you!!!
0
14_eastAuthor Commented:
well... I do have more questions... can you help for a moment?  I have the following query:

<cfquery name="getCR">
    SELECT * FROM tbl_cr
    WHERE replace(cr_them,'-','') LIKE '%#form.cr_them#%'
</cfquery>

Open in new window



If I search like this:

DWR  (Works GOOD, returns ALL 12 matches)
DWR10  (Works GOOD, returns 4 matches)
DWR1017  (Works GOOD, returns 2 matches)
DWR-10  (Finds no match?)
DWR-10-17 (Finds no match?)


**The database fields are as follows:

DWR-10-17
DWR-10-17PD
DWR-10-22
DWR-10-22PD
DWR-12-17
DWR-12-17PD
DWR-12-22
DWR-12-22PD
DWR-16-17
DWR-16-17PD
DWR-16-22
DWR-16-22PD
0
14_eastAuthor Commented:
I guess what I am wondering... can we have it both ways from one search box?   The format in the database is the correct way, product numbers with hyphens.  What we want is for the website user to enter a competitors product number and do a look up of cross-referenced products.  They may or may not enter the hyphens correctly... or at all.  Can this be achieved?
0
14_eastAuthor Commented:
Got it!!  here's what worked:

  <cfset form.cr_them = replace(replace(form.cr_them,"-",""),"-","","")>
    <cfquery name="getCR">
        SELECT * FROM tbl_cr
        WHERE replace(cr_them,'-','') LIKE '%#form.cr_them#%'
    </cfquery>

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.