SQLite3 - How to select a specific number from a string

Question

I have a table named shoes:

Shoe_model     price    sizes
-----------------------------------------------
Adidas KD      $55      '8, 9, 10, 10.5, 11, 11.5'

Nike Tempo     $56      '8, 9, 11.5'

Adidas XL      $30.99   '9, 10, 11, 13'

How can I select a row for a specific size?

My attempt:

SELECT * FROM shoes WHERE sizes = '10';

Is there a way around this? Using LIKE will get you both 10 and 10.5 sizes so I'm trying to use WHERE for this. Thanks

use `sizes like '%10,%' or right(sizes,2)='10'`. You will be better off normalizing your data — cha, Mar 08 '17 at 03:27
My code: `SELECT * FROM shoes WHERE CONCAT(sizes,',') like '%10,%'` — tadm123, Mar 08 '17 at 03:44
quick google search for "sqlite concat" finds this: http://stackoverflow.com/questions/6134415/how-to-concatenate-strings-with-padding-in-sqlite. Basically, it is `||` operator, like in Oracle, Postgres — cha, Mar 08 '17 at 03:49

score 1 · Answer 1 · answered Mar 08 '17 at 04:03

First, you should not be storing the sizes as a comma delimited string. You should have a separate row with one row per size per show. That is the SQLish way to store things.

Sometimes, we are stuck with other people's really bad design decisions. In you can do something like this:

SELECT *
FROM shoes
WHERE ',' || '10' || ',' LIKE '%,' || sizes || ',%';

The delimiters are important so "10" doesn't match "100".

SQLite3 - How to select a specific number from a string

1 Answers1