Query Sql Like String

Question

I need help for sql query LIKE. Value for column in database is same below:

 record 1 : "3,13,15,20"
 record 2 : "13,23,14,19"
 record 3 : "3,14,15,19,20"......

for now I want to get the most accurate record with a value of 3 This is my query :

SELECT * FROM accounts where type like '%3%'

This query will find all record with value exist is '3' eg: 13,23 .... And It does not solve my problem.

Don't store data as comma separated items. It will only cause you lots of trouble. — jarlh
– jarlh, Commented Aug 6, 2018 at 11:05
@TimBiegeleisen, same people who say field instead of column? (The ANSI SQL specification uses row and column exclusively.) — jarlh
– jarlh, Commented Aug 6, 2018 at 11:12
@jarlh: you're absolutely right - relational databases have tables with rows and columns - but it seems like a struggle in vain to try and convince the IT crowd to be precise and use the proper, correct lingo :-( — marc_s
– marc_s, Commented Aug 6, 2018 at 12:04

Tim Biegeleisen · Accepted Answer · 2018-08-06 11:06:19Z

3

Try this:

SELECT *
FROM accounts
WHERE CONCAT(',', type, ',') LIKE '%,3,%';

Demo

This trick places commas around the end of the type CSV string, so that we all we have to do is then check for ,3, anywhere in that string.

By the way, it is generally not desirable to store CSV data like this in your SQL tables. Instead, consider normalizing your data and storing those CSV values across separate rows.

answered Aug 6, 2018 at 11:06

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query Sql Like String

1 Answer 1

Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related