How to get an exact match

[DioPally]

How do you get a SELECT statement to look for an exact match when comparing strings ?

For example, SELECT name FROM table WHERE name="Jim"

returns records even if the name field is "JIM". I need an exact match. im using MS Access for database

[Daverms]

Your select statement should be,

SELECT name FROM table where upper(name) = "#TRIM(UCASE(form.NAME))#"

[DioPally]

quote:

---

Originally posted by: Daverms
Your select statement should be,

SELECT name FROM table where upper(name) = "#TRIM(UCASE(form.NAME))#"

---

[DioPally]

I need the strings to match exactly. The fields i am comparing to is a mixture of upper and lower case characters and I cannot change the case. I need Jim to be different from JIM to be different from JiM.

Changing everything to upper case is not an exact match. The fields is a ID field generated using random upper and lower case letters and number 0-9. an example of a value in the field is:
H10h6FFSJQzjIKRi57ZWFMNkPaq8qsyGRdjCeAxM0yW1t3Ui2W

and you see why i cannot use UCase to change the case of the string.

I need an exact match

[Dan_Bracuk]

Are you able to switch dbs? Access might be the only one that is not case sensitive.

[DioPally]

Unfortunatly, no. I must use their existing database. I checked and am surprised Access doesnt have a field type of binary or something simular.

So there is no way of doing this ?

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

MS Access has a StrComp function that might help you do this.

http://support.microsoft.com/kb/209674
http://support.microsoft.com/kb/244693

[DioPally]

Yes, StrComp was an answer. Thank you.

I did not realize that coldfusion must just pass the SELECT statement to MS Access with examining it therefore didn't think StrComp would work since it's not a CF keyword. I tried using Compare, and that did not work.

Here is the completed code that works:

<cffunction name="IsJobKeyUnique" access="public" returntype="boolean" output="false">
<cfargument name="jk" type="string" required="yes">
// default to True that jobkey is unique
<cfset ret=True>
<cfquery name="temp" datasource="lrs">
SELECT * FROM jobkeys WHERE StrComp(jobkey,'#ARGUMENTS.jk#',0)=0
</cfquery>
<cfif temp.RecordCount NEQ 0>
// a match was found so the jobkey is not unique, return False
<cfset ret=False>
</cfif>
<cfreturn ret>
</cffunction>

The function only checks if the passed string can be found in the database and if it is, then its not unique. Is there a more logical way to do it using COUNT or some other way ?

But anywho, it works as is, but I'm always looking to improve on things.

Thanks again