Skip navigation.
Home

SQL Question

I am trying to write a query that will return all Authors associated with a specified county. For this to work with the current database design, the query must find a county named “Dale” in the following text field:

  • Fort Rucker, Coffee and Dale Counties: civilian employment
  • Ozark, Dale County: adult residence
  • Fairhope, Baldwin County: adult residence

Passing the following query a url variable, “…/county.cfm?CountyName=Dale” accomplishes the task:

SELECT AuthorTitle,AuthorID,AuthorFirst,AuthorLast
FROM Authors
WHERE AuthorCounties LIKE'%#Arguments.CountyName#%'
ORDER BY AuthorLast,AuthorFirst

Here is the problem, the query above also finds “dale” inside the word “Lauderdale” in the following record:

  • Florence, Lauderdale County: adult residence
  • Tuscaloosa, Tuscaloosa County: adult residence
  • Tuskegee, Macon County: adult residence

Can I use RegEx to modify the WHERE cause to find only first character capitalized word? This way Dale would be found, but Lauderdale would not.


The use of LIKE and IN in SQL

Hey tigersam! First of all, when you use the '%' character in the search string, you are telling it to find any string that contains the word 'dale'. In other words, the percent sign says that anything can come before and anything can come after 'dale'. Instead of LIKE, try using the SQL command IN.

See how to use it here:
http://www.1keydata.com/sql/sqlin.html

This is the format:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)

IN does not use wildcard values, as opposed to LIKE.