[SOLVED] Use of LIKE in PostgreSQL with brackets

Use of LIKE in PostgreSQL with brackets

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?

Solution

Try:

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