CODEWITHSUNDEEP
mysqlsql
Mitul Lakhani
Mitul Lakhani

Reputation: 192

select data mysql

i have in my table places named field. there are space separated values(there are problem to store csv value in one field). now i want to fire query like below. how i can do ??

select * from tablename where variablename in places

i did try this way but it shows syntax error.

select * from tablename where variablename in replace(places,' ',',')



### places ###

bank finance point_of_interest establishment

Upvotes: 0

Views: 40

Answers (3)

zakhefron
zakhefron

Reputation: 1443

Use FIND_IN_SET

For comma separated

SELECT *
FROM tablename
WHERE  ( FIND_IN_SET( 'bank', variablename ) )

Refer : SQL Fiddle

For space separated

SELECT *
FROM tablename
WHERE  ( FIND_IN_SET( 'bank', replace(variablename,' ',',') ) )

Refer : SQL Fiddle

Upvotes: 1

Shadow
Shadow

Reputation: 34294

The best solution would be to normalise your data structure and do not have a single field storing multiple values.

You can make a query work without normalisation, but any solutions would be lot less optimal from a performance point of view.

  1. Use patter matching with like operator:

    ... where fieldname like '% searched_value %'

  2. Use the replace() function and combine it with find_in_set():

    ... where find_in_set('searched_value',replace(fieldname,' ',','))>0

Upvotes: 0

SatriaPriambada
SatriaPriambada

Reputation: 41

Hi I think your problem comes from the usage of IN
IN for MySql is used like this 

SELECT *
FROM table_name
WHERE column_name IN (bank,finance,point_of_interest, establishment);

In case of you want to select places you need to specify each place into value like

Upvotes: 0

Related Questions