QUERYING A STATE WITH CITY AND STATE INSIDE FIELD

[kingjjx]

Hi, I have a drop down box that have the choices for different states.

Now, the problem I have is that the field I am going to querry which is called Address2, has both the city and state in it (ie. Los Angeles, CA)

How do I write the query statement to be able to query by state.
(the drop down box is not dynamically populated)

thanx
-j

 

[CFDude]

If the field will always have it in the form of "City, State" with a comma, you could just treat it as a list and grab the second part.

E.g

<cfset State = trim(ListGetAt(form.Address2,2,&quot;,&quot)>

This should grab the CA off of Los Angeles, CA

HTH,
Tim P.

 

[GunJack]

Hey kingjjx,

If I understand your question correctly, I think this is the sql syntax you'll need.

select * from myTable where address2 like '%, #form.stateField#'

This should give a pretty good match but you're much better off going with a separate field for the state if possible. I'm assuming that your drop down contains just states (<option>AL<option>AK...) but if it contains city, state information, you'll need to use what CFDude posted to strip out just the state.

Good luck,
GJ