2

I have a field having comma seprated values value like this : 192,193,195,196,197,198,199,200

I want to perform a exact search Like if i search for 92 then record having above should not appear in result. But if i search for 192 then only above record should appear.

I have tried LIKE query but not seems to work.

SELECT * FROM TABLE_NAME WHERE field_name LIKE '92'
SELECT * FROM TABLE_NAME WHERE field_name LIKE '%92%'

But above query returns record having value like 192 as well.

Any help will be appreciated.

Amit Kumar
  • 3,384
  • 6
  • 25
  • 42
  • 3
    You shouldn't have comma-separated values in the first place, normalize your schema. – Barmar Jun 01 '18 at 08:22
  • Keep the data in some box like format [191][192][193][194] and so on, then you can easily search for any value like '%[192]%' – Ranjit Singh Jun 01 '18 at 08:24
  • @RanjitSingh No. – Strawberry Jun 01 '18 at 08:24
  • @Strawberry why not? – Ranjit Singh Jun 01 '18 at 08:25
  • For the last one : `SELECT * FROM TABLE_NAME WHERE field_name LIKE '%,'` For others : `SELECT * FROM TABLE_NAME WHERE field_name LIKE ',%'` EXAMPLE : `SELECT * FROM TABLE_NAME WHERE field_name LIKE '%,200'` – Killer Queen Jun 01 '18 at 08:26
  • @RanjitSingh See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Strawberry Jun 01 '18 at 08:26
  • @Barmar, Actually its already build by someone other.. Thanks for you help – Amit Kumar Jun 01 '18 at 08:27
  • @KillerQueen Your suggestion ignores the 92 in 184,92,33 – Strawberry Jun 01 '18 at 08:28
  • you're right, there are a few cases missing @Strawberry – Killer Queen Jun 01 '18 at 08:30
  • @Strawberry we are not discussing whether keeping comma separated value is better or creating a new table for mapping is better, its all depend on the requirement. Agree with the point mentioned in shared link, this also has been mentioned by Barmer. But if then also OP wants to keep the data in the single column then he can use box format [1][2][3]. Please let me know if anything is wrong with that. – Ranjit Singh Jun 01 '18 at 08:32
  • 1
    @RanjitSingh It doesn't depend on the requirement. Relational databases (RDBMS) are for storing relational data. People are of course entirely at liberty to store data (or not store data) in any way they see fit. But if they choose not to store it 'relationally' then I see little merit in bothering with an RDBMS. – Strawberry Jun 01 '18 at 08:40

0 Answers0