CODEWITHSUNDEEP
sql
user370306
user370306

Reputation:

How to build this sql query

Table: sometbl

field1|field2|field3
txt1  |txt2  |1,5,7,4
txt3  |txt4  |1,7,8,9
txt5  |txt6  |8,3

How to select all from sometbl where field3 contains 1 ?

Upvotes: 0

Views: 90

Answers (4)

Dyppl
Dyppl

Reputation: 12381

If comma-separated values are numbers, not digits, don't use just LIKE '%1%', it will include "5,21" and "14,65" as well. If that's the case, you can use something hacky like SELECT * FROM sometbl WHERE field3 LIKE '%,1' OR field3 LIKE '%,1,%' OR field3 LIKE '1,%' OR field3 = '1'. Messy, but semantically correct.

Upvotes: 1

Oded
Oded

Reputation: 499132

This is not a normalized table.

You can use the LIKE operator:

SELECT * 
FROM sometbl
WHERE field3 LIKE '%1%'

Edit:

This, of course will also match 11, 10, 101 etc...

If you know 1 can only exist in the start, you can use this:

SELECT * 
FROM sometbl
WHERE field3 LIKE '1,%' 
OR field3 = '1'

If 1 can appear anywhere, this will work:

SELECT * 
FROM sometbl
WHERE field3 LIKE '1,%' 
OR field3 LIKE '%,1' 
OR field3 LIKE '%,1,%' 
OR field3 = '1'

Though the best option is to normalize.

Upvotes: 2

Dienekes
Dienekes

Reputation: 1548

SELECT * 
FROM sometbl
WHERE field3 LIKE '%,1' OR field3 LIKE '1,%' OR field3 LIKE '%,1,%'

Upvotes: 0

Hosam Aly
Hosam Aly

Reputation: 42453

If field3 is text, use the LIKE operator.

Upvotes: 0

Related Questions