Use of LIKE in PostgreSQL with brackets

Question

I try to be so specific as possible.

Currently I use MS SQL Server 2012.

A simplified table PlanMission contain these rows

|---------------------|---------------------|
|      Bold_Id        |   MCurrentStates    |
|---------------------|---------------------|
|      10776          |[original[scheme]    |
|---------------------|---------------------|
|      10777          |[operative][inproc]  |
|---------------------|---------------------|
|      10778          |[operative][closed]  | 
|---------------------|---------------------|
|      10779          |[operative][planopen]|
|---------------------|---------------------|

The Bold_id column is just an ID that is unique. The column MCurrentStates is a VARCHAR column containing states as substrings.

A state is simply a string surrounded by brackets like [planopen] So the column may be empty or have many states like example above.

IN MS SQL if I do like this

SELECT  Bold_Id, MCurrentStates 
FROM PlanMission 
WHERE MCurrentStates LIKE '%[planopen]%'

it don't work. It just list all rows that are not empty in MCurrentStates.

It is solved by insert []

SELECT  Bold_Id, MCurrentStates 
FROM PlanMission 
WHERE MCurrentStates LIKE '%[[]planopen]%'

That works fine.

Now I want to do this also for PostgreSQL. Simple solution is to remove the brackets.

But my question is how can I do this with a query that is the same for both MS SQL and PostgreSQL?

You should not store multiple values in a single column like that to begin with. — , May 07 '18 at 07:34
_"... is the same for both ..."_ DB engines don't use the same dialect of sql. So the short answer is that you write code using the common level of ansi sql supported by those engines you use or you write different code for the different engines. You choose. And, of course, normalize your tables properly to avoid issues like the one you now have. — SMor, May 07 '18 at 12:50

score 3 · Accepted Answer · edited May 07 '18 at 07:33

3

Try:

SELECT  Bold_Id, MCurrentStates 
FROM PlanMission 
WHERE MCurrentStates LIKE '%/[planopen/]%' ESCAPE '/';

edited May 07 '18 at 07:33

Laurenz Albe

209,280
17
206
263

answered May 07 '18 at 07:07

Killer Queen

776
9
20

Use of LIKE in PostgreSQL with brackets

1 Answers1