asked on

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

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

14_east

ASKER

Nice... I had just found the answer... and came to update this question but see you have elaborated on my solution. Thanks so much!!!

14_east

ASKER

Much thanks!!!

14_east

ASKER

Wait!!! one more question pertaining to this... can I strip out more than one character? Like account for pound sign as well, etc?

14_east

ASKER

well.. I just answered my own question, but am wondering how to include the # in the query replace as well.

gdemaria

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")>

14_east

ASKER

very nice, thank you!!!

14_east

ASKER

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

14_east

ASKER

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?

14_east

ASKER

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