Techie Techie - 2 years ago 122
SQL Question

How to explode in MySQL and use it in the WHERE clause of the query - MySQL

I have a database table as below.


id(INT), promotion_name(VARCHAR),......, bungalow_ids(VARCHAR)

We can add a promotion for a bungalow(23). So a row is added with the bungalow id as below.

1, My Promotion, ........, 23

But if I single a promotion is added for a multiple bungalows(23,42) all ids are saved in the
column as below.

2, My Promotion 2, ........, 23 | 42

If a user search for promotion which are for specific bungalow(23) All promotions for the bungalow should be shown in the result.

I have a query as below.

SELECT * FROM Promotion_Table WHERE bungalow_ids = '23'

It only gets 1 rows. But actually 2nd row should be shown too since there is a offer. I can nt use
since it gets wrong records.

Given that I have already referred below links but I have no idea how to use them in the query.

Can you split/explode a field in a MySQL query?

Equivalent of explode() to work with strings in mysql

How can I fix this? How can I explode the column data and use it in the query ?

Answer Source

Use , to separate the string and try this query

select * from promotion_table where FIND_IN_SET("23",bungalow_ids)!2/7bbcb/1

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download