1

I was wondering if there was a way to select from a column that has comma separated values.

For example, the column has this data: ('16', '20', '27', '2', '3')

Is it possible to do something like this:

-- get value 16 from table:-

select from table where value = '16'

or maybe use in?

select from table where value in '16'
Bikramjeet Singh
  • 681
  • 1
  • 7
  • 22
Sa Rod
  • 21
  • 1

3 Answers3

1

If the column value contains data with single quotes like this:

'16', '20', '27', '2', '3'

then you can use the operator like:

select * from table where value like '%''16''%'

If there are no single quotes and the data is like:

16, 20, 27, 2, 3

then:

select * from table 
where ',' || replace(value, ' ', '') || ',' like '%,16,%'

If there are not any spaces between the comma separated values then you can don't need replace():

select * from table where ',' || value || ',' like '%,16,%'
forpas
  • 160,666
  • 10
  • 38
  • 76
0

Kindly use the below if the value column has single quotes sql fiddle here

        select * from tt where REGEXP_LIKE(value1,'(''16'',)[^,]+');

if it doesn't have single quotes use the below sql fiddle here

   select * from tt where REGEXP_LIKE(value1,'(16,)[^,]+');
psaraj12
  • 4,772
  • 2
  • 21
  • 30
0

Possibly the database schema violates the first normal form