How to retrieve all data where delivery id is equal 5

Question

I want to retrieve all data where the delivery_id is equal 5 and that delivery_id is in array.How to do?.This is my problem.

my database is

sale_code    seller_id     product     delivery_id
202108        6           some1         [5,1]
202108        4           some2         [7]
202109        5           some3         [5]
202109        4           some4         [7,1]

I suggest you read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Nick, Jan 21 '20 at 03:46
That’s because that’s not the right way to store data. Research database normalization to learn how to normalize this table. Hint: you need a table between deliveries and this table, to tie them together. — Tim Morton, Jan 21 '20 at 03:50
Note that you are not storing an array in column `delivery_id`, you are storing a string. — Turophile, Jan 21 '20 at 04:08

zip · Answer 1 · 2020-01-21T04:16:36.463

2

You have 4 cases. I have them in or logic

select * from tbl 
where (delivery_id like '[5,%' or delivery_id like '[%,5]' or delivery_id like '%,5,%' or delivery_id like '[5]')

edited Jan 21 '20 at 04:16

answered Jan 21 '20 at 03:56

zip

3,938
2
11
19

1

You missed the instance where 5 is the only value in the list. – Turophile Jan 21 '20 at 04:06
Sorry Sir, I give you feedback and response for your answer.But show me alert that is "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score.".I don't understand that.I am newbie in SOF. – thaw zin Jan 21 '20 at 10:10

Turophile · Accepted Answer · 2020-01-21T04:23:35.657

0

select *
from table1
where delivery_id LIKE '[5,%' 
or delivery_id LIKE '%,5]' 
or delivery_id LIKE '%,5,%' 
or delivery_id = '[5]'

It goes without saying that this is terrible - you should not be storing your data like that with multiple values in a list within a column.

EDIT: If you are using mySQL you can use the REGEXP function:

select *
from table1
where delivery_id REGEXP '[\[,]5[\],]'

edited Jan 21 '20 at 04:23

answered Jan 21 '20 at 04:05

Turophile

3,367
1
13
21

How to retrieve all data where delivery id is equal 5

2 Answers2