ColdFusion How can I adjust my search criteria to include/not include dashes?

How can my search criteria find a number with or with out dashes included?

Example:
Numbers are in one field in an Access database formated: 1234-56-789-1011

I would like the search to find this record (1234-56-789-1011) when the user types in:

1234-56-789-1011
1234567891011 (no dashes inputted by user)
1011
 
I.E. The search will find the record if dashes are inputted or not.
DJPr0Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gdemariaConnect With a Mentor Commented:
is GSN the column name?

 AND replace(#GSN#,'-','') like '%#Form.GSN#%'


If it is, it should not be enclosed in #'s

 AND replace(GSN,'-','') like '%#Form.GSN#%'

as I said before, I am not sure if replace() is the correct function name for access, you may have to look that up.
0
 
SidFishesCommented:
Use the like operator ... i think the access version of it uses *


<cfquery...>
select * from tbl where field like "*#Form.searchstring#*"
</cfquery

it should find any instance of the characters
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
gdemariaCommented:
I would suggest stripping out dashing and comparing them without dashes

Not sure the command in Access, something like replace(string,'-','')
0
 
SidFishesCommented:
gd - that won't help if they search by a partial ie: 1011
0
 
Helen FeddemaCommented:
Strip out all but alphanumeric characters before doing the search.  I use this function:

Public Function StripNonAlphaNumericChars(strText As String) As String
'Strips a variety of non-alphanumeric characters from a text string
'Created by Helen Feddema 10-15-97
'Modified by Ruud H.G. van Tol 6-18-99
'Modified by Brad Beacham 6-Feb-2005
'Last modified by Helen Feddema 7-Feb-2005

On Error GoTo ErrorHandler

   Dim strTestString As String
   Dim strBadChar As String
   Dim i As Integer
   Dim strStripChars As String

   strStripChars = " `~!@#$%^&*()-_=+[{]};:',<.>/?" & Chr$(34) & Chr$(13) & Chr$(10)
   strTestString = strText

   For i = 1 To Len(strStripChars)
      strBadChar = Mid(strStripChars, i, 1)
      strTestString = Replace(strTestString, strBadChar, vbNullString)
   Next

   StripNonAlphaNumericChars = strTestString
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in StripNonAlphaNumericChars procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window


It is very useful for processing phone numbers that may be entered in a variety of formats -- I convert them to all numbers, then apply a standard formatting before saving them.
0
 
SidFishesCommented:
That's a nice function but I think that the UI is web based (hence the coldfusion TA) so the solution has to be either straight sql or coldfusion based...

(i may be wrong...)
0
 
DJPr0Author Commented:
SidFishes you are correct - this is a web based UI via CF.

The * didn't work, I tried:
 AND GSN LIKE '*#GSN#*'        No results, even if I input the exact number
 AND GSN LIKE "*#GSN#*"       Produces an error
 AND GSN LIKE '*%#GSN#%*'  Nothing
 AND GSN LIKE '%*#GSN#*%'  Nothing

The statement below works for partials I.E. 1011, but not for dashes 1234567891011 (no dashes inputted by user)

Here is my actual search statement:
<cfif GSN Is NOT "">
			   <cfif GSN Is NOT "ALL">
                           <cfif Exact Is NOT "No">
				       AND GSN = '#GSN#' 
					   <cfelse>
				        AND GSN LIKE '%#GSN#%' 
					  </cfif>
				  </cfif>	
                </cfif>

Open in new window

0
 
gdemariaCommented:
You need to strip out the dashes from the database field and the input search string

<cfset Form.searchstring = replace(Form.searchstring,"-","")>

select *
 from tbl
where replace(field,'-','') like "*#Form.searchstring#*"
0
 
DJPr0Author Commented:
Not sure what I'm doing wrong:

My actual statements:
<cfset #Form.GSN# = replace(Form.GSN,"-","")>


<cfif GSN Is NOT "">
		 <cfif GSN Is NOT "ALL">
                      <cfif Exact Is NOT "No">
				       AND GSN = '#GSN#' 
					   <cfelse>

                   AND replace(#GSN#,'-','') like '%#Form.GSN#%'

				    <!---    AND GSN LIKE '%#Form.GSN#%' --->
					  </cfif>
				  </cfif>	
                </cfif>

Open in new window


Error:
 Syntax error (missing operator) in query expression '1=1 AND (received is null OR received < project) AND GSN replace([2-],[-],[]) like '*2*''.
0
 
Jeffrey CoachmanMIS LiasonCommented:
DJPr0,

Just my 2c...

As you can see this is not easy.
...nor is it the standard way most searches are conducted (hence the difficulty)

Why not just keep it simple and just let them search the numbers only?
I don't really see any benefit to adding the dashes.

If the number is "formatted" with dashes, the the dashes are not stored anyway...
A true query only inspects the actual stored value, ...not the formatted value.

Finally, if they will be typing in the "entire" value (as you show in your example), then why not just give them a combobox?


JeffCoachman
0
 
DJPr0Author Commented:
Jeff - The project manager wants to see the hyphens on the output results and not interested in a combobox for some reason.

Everyone - Could I store the numbers in the database without the dashes and automatically add them on the output via CF?

AND replace(GSN,'-','') like '%#Form.GSN#%' Can not get this part to work - error: Syntax error (missing operator) in query expression

Read conflicting reports if Replace works with an Access database.
0
 
gdemariaCommented:
> Could I store the numbers in the database without the dashes and automatically add them on the output via CF?

Of course, you have a couple of options here.
1) You can strip out all dashes and store the number without dashes in the database, then when displaying them, put the dashes back in.   This will work if the dashes are always in the same spot and it never will change.
2) Store two values of the number (in two different fields).  One formatted and one without dashes.  Search on the unformatted column and display the formatted colum
3) store the number formatted and simply strip out the dashes when doing a search.  The search may be a tad slower, but if you don't have tens of thousand of records, it should not be a big deal


To remove dashes in your query...

http://webcheatsheet.com/SQL/access_functions/replace.php

the example shows double quotes, I don't know if that's necessary in access or not

Replace("Green Apple", "Green", "Yellow")      returns "Yellow Apple"
Replace("Green Apple", "e", "a", 7)                returns "Green Appla"
Replace("Green Apple", "e", "a", 1, 2)            returns "Graan Apple"

Access uses * not %

Seems that this should work:

AND Replace(GSN,"-","") like "*#Form.GSN#*"
0
 
gdemariaCommented:
sorry, just found out that MS Access can use either * or %
either are Ok
0
 
DJPr0Author Commented:
AND Replace(GSN,"-","") like "*#Form.GSN#*"  

This statement produces an error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.
 

AND Replace(GSN,"-","","all") like "*#Form.GSN#*"

This statement produces an error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4.
0
 
gdemariaCommented:
That's weird..

http://www.techonthenet.com/access/functions/string/replace.php

Replace ( string1, find, replacement, [start, [count, [compare]]] )


AND Replace(GSN,"-","",1)
0
 
DJPr0Author Commented:
gdemaria - Finally got it to work with your previous suggestion:

 AND Replace(GSN,'-','') Like '%#Form.GSN#%'

Not sure what happen!
0
 
DJPr0Author Commented:
Thanks gdemaria!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.