Select query serialized field
Posted: Wed Dec 15, 2010 2:15 pm
Hello,
I have a field in a table with states abbr. that have been serialized example,
s:155:"AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VA,VI,VT,WA,WI,WV,WY";
The above example is one with all states, but some just have one state or a few it depends on how many the client selected.
How would I write a select statement to pull all the records that have "CA" in the list for example.
I tried this: select * from locations where 1 and states REGEXP '["]CA["]' but this only returns records with just CA
s:2:"CA";
Im looking for a way to grab those and also the records with multiple states if CA is in it.
Any help would be greatly appreciated.
Thanks in advance
I have a field in a table with states abbr. that have been serialized example,
s:155:"AK,AL,AR,AZ,CA,CO,CT,DC,DE,FL,GA,HI,IA,ID,IL,IN,KS,KY,LA,MA,MD,ME,MI,MN,MO,MS,MT,NC,ND,NE,NH,NJ,NM,NV,NY,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VA,VI,VT,WA,WI,WV,WY";
The above example is one with all states, but some just have one state or a few it depends on how many the client selected.
How would I write a select statement to pull all the records that have "CA" in the list for example.
I tried this: select * from locations where 1 and states REGEXP '["]CA["]' but this only returns records with just CA
s:2:"CA";
Im looking for a way to grab those and also the records with multiple states if CA is in it.
Any help would be greatly appreciated.
Thanks in advance