I am running a query to test whethe

[iao]

I am running a query to test whether or not a street entered by the user already exists in my database.

The trouble is this: in the database, the names are listed as "127TH" and "21ST" instead of "127" and "21".

So, as you might be able to guess, if the user enters "127", I need to insure that I am querying all the streets which have "127TH". If the user enters "122", and this street does not exist in the database, I need to add "122ND" (and not "122&quot.

Any ideas on the best way to handle this. I will assume that I would need to add some kind of logic to differentiate between 1 (which ends with a "ST&quot, 2 (which ends with a "ND&quot, 3 (which ends a "RD&quot, and 4-0 (which ends with a "TH&quot.

 

[Silvano]

There is probably an easier way to do this, but for starter you can try to use 'Replace' function; if you are absolutly positive that nonnumeric characters in your string can be "st", "nd", "rd" or "th" only, than you can try and include something like this in your program:

<cfset newValue = Replace(queryName.street, &quot;st&quot;, &quot;&quot>


it will simply replace all specified strings (if found)with &quot;&quot;

 

[ded]

I would try this if you are just checking to see if a street exists or not.

<CFQUERY NAME=&quot;test&quot;....>
SELECT *
FROM streettable
WHERE SUBSTR(streetname,1,LEN(#form.userstreet#)) = '#form.userstreet#'
</CFQUERY>

<CFIF test.RecordCount EQ 0>
Doesn't exist
<CFELSE>
exists
</CIFF>

Hope this helps.