Square ([]) brackets not being recognized in text column of sql

Question

I have a column in a SQL table that contains json lists as text.

I want to find all the rows that have [7] in the column. So I have something like the following

SELECT col1, col2 FROM tbl1 WHERE col2 LIKE '[7]'

However this comes back with nothing (though I can see rows when I inspec the table that have that in the field).

Stranger still, if I try editing the query to the following

SELECT col1, col2 FROM tbl1 WHERE col2 LIKE '%[7]%'

It will find the rows that have [7], but it will also get rows that clearly (I think) violate that pattern, i.e. rows that have [1,7]

Is the square bracket something special that I'm not aware of?

Yes.. They are are form of quoting. See this for your issue http://stackoverflow.com/questions/439495/how-can-i-escape-square-brackets-in-a-like-clause — Aaron Dietz, Jun 21 '16 at 21:57
also, using `LIKE` without wildcards is almost the same as using `=` — Lamak, Jun 21 '16 at 21:59

M.Ali · Accepted Answer · 2016-06-21T22:24:29.073

2

Try something like

SELECT col1, col2 FROM tbl1 
WHERE col2 LIKE  '%\[7]%' ESCAPE '\'

edited Jun 21 '16 at 22:24

answered Jun 21 '16 at 22:12

M.Ali

You only need to escape the opening bracket, the closing bracket is implied `'%\[7]%' ESCAPE '\'` – Aaron Dietz Jun 21 '16 at 22:18
@AaronD well they say everyday is a school day , I did not know this :) Thanks for pointing this out – M.Ali Jun 21 '16 at 22:24
I've learned from one (many) of your answers. Happy to pass on this small bit. – Aaron Dietz Jun 21 '16 at 22:29

1 Answers1