find_in_set problems

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thewebdrivers
Forum Commoner
Posts: 41
Joined: Fri Aug 17, 2007 3:32 pm
Location: india
Contact:

find_in_set problems

Post by thewebdrivers »

Hello Guys,

How are you all? Its been a long time since i visited the forum.

Anyways I am having a small problem which i need your help with. Below query does not work when i think it should work. I does not return any results.

Code: Select all

SELECT * FROM carstylestags WHERE 1=1  AND (FIND_IN_SET('Sedan',REPLACE(`bodystyle`,"','",',')) > 0  ) ORDER BY chrome_custom DESC
Please see the data in the table below:-

Code: Select all

id  modelid     bodystyle   marketsegment   size    chrome_custom   man_id  man_type
 
    1   17541   'SUV/Crossover'     'Near-Luxury'   'Midsize'   chrome      1   chrome
    6   56  'Coupe','Convertible'   'Near-Luxury','High-Performance'    'Sub-Compact','Midsize'     custom  4   chrome
    7   44  'Sedan'     'Luxury'    'Compact'   custom  16  custom
    8   17477   'SUV/Crossover'     'Near-Luxury'   'Compact'   chrome  1   chrome
    9   17411   'Sedan'     'Luxury'    'Midsize'   chrome  1   chrome
    10  9   'Sedan'     'Near-Luxury'   'Midsize'   stylegroup  1   chrome
    11  18213   'Sedan'     'Near-Luxury'   'Midsize'   chrome  1   chrome
    12  11  'Coupe'     'Exotic','Sport'    'Sub-Compact'   stylegroup  44  chrome
    13  12  'Convertible'   'Luxury','Exotic','Sport'   'Sub-Compact'   stylegroup  44  chrome
    14  18081   'Coupe'     'Luxury','Exotic','Sport'   'Sub-Compact'   chrome  44  chrome
    15  13  'Coupe'     'Luxury','Exotic','Sport'   'Sub-Compact'   stylegroup  44  chrome
    16  14  'Convertible'   'Luxury','Exotic','Sport'   'Sub-Compact'   stylegroup  44  chrome
    17  17299   'Station Wagon','Hatchback'     'Near-Luxury','Sport'   'Compact'   chrome  4   chrome
Please lemme know if i am missing something.

Mike
Last edited by Weirdan on Mon May 05, 2008 8:33 pm, edited 1 time in total.
Reason: code tags
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: find_in_set problems

Post by Weirdan »

Code: Select all

SELECT REPLACE(`bodystyle`,"','",',') FROM carstylestags
should give you a hint - you need to strip leading and trailing quotes as well.
Post Reply