Like operator condition in postgresql data issue

postgres like not working
postgres like with variable
postgres like query
postgresql like multiple values
postgres like regex
postgres ilike vs like
postgres regex special characters
postgres escape underscore

I have below data in the table

HJ-DEF-ABCF010-ABC18-09-17-D
GHJ-ABC-ABFV006-ABC18-09-18-R
OH-DEF-ABFCRT2037-ABC17-01-18-R

I want to populate the value in another column like

HJ-DEF-ABCF010-ABC18-09-17-D             BET
GHJ-ABC-ABFV006-ABD18-09-18-R            BET
OH-DEF-ABFCRT2037-ABCD17-01-18-R          BET

As the mapping for

ABC18 is BET   
ABD18 is BET
ABCD17 is BET

I was using the below sql query for that

select col1,case 
when col1 like '%-ABC[1-2][0-9]-%' then BET 
when col1 like '%-ABD[1-2][0-9]-%' then BET 
when col1 like '%-ABCD[1-2][0-9]-%' then BET
else - end form table

which is working fine in SQl sever but in Pogresql we can't use [1-2] to find out the expected digit in the position. Any suggestion or idea how to achieve in Posgresql.


It works just fine. However, you think -- for some reason -- that LIKE supports character classes and other regular-expression-like features. That is simply not true in Postgres, nor in any other database other than SQL Server, Sybase, and MS Access.

Just use regular expressions.

For the original version:

SELECT *
FROM Customers
WHERE Customerid ~ '^[1-2][1-1]'; 

For the edited version:

SELECT *
FROM Customers
WHERE Customerid ~ '[-].{3}[1-2]1'; 

PostgreSQL - LIKE Clause, PostgreSQL - LIKE Clause - The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be  I want to select records using LIKE operator with multiple conditions, I can use OR between the conditions but I am looking for a short way to solve this issue. I am trying the following query:


You can actually make your current logic work with LIKE, with a bit of effort:

SELECT *
FROM Customers
WHERE Customerid LIKE '11%' OR Customerid LIKE '21%';

You seem to be using perhaps SQL Server or Sybase enhanced LIKE syntax. Postgres does not support this, but it does support its own ~ regex operator. See Gordon's answer for more on that.

The edit to your question can use this:

SELECT *
FROM Customers
WHERE Customerid ~ '-[^-]{3}[1-2]7';

Demo

Documentation: 9.0: Pattern Matching, If you must do so, it is advisable to impose a statement timeout. Like LIKE , the SIMILAR TO operator succeeds only if its pattern matches the entire As with SIMILAR TO, the specified pattern must match the entire data string, or else the This should not be much of a problem because there was no reason to write such a  The PostgreSQL LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. The percent sign represents zero, one, or multiple numbers or characters.


Used with SIMILAR TO instead of like. select col1,case when col1 SIMILAR TO '%-AB([CD]|CD)[1-2][0-9]-%' then BET else - end form table thnx @WiktorStribiżew

PostgreSQL LIKE, Not Like, Wildcards (%, _ ) Examples, The PostgreSQL LIKE operator helps us to match text values against patterns With the help of LIKE operator, it is possible to use wildcards in the WHERE clause of SELECT, Download the Database used in this Tutorial. By placing the NOT Operator in front of the PostgreSQL LIKE condition, you are able to retrieve all employees whose last_name does not start with 'J'. Example - Using Escape Characters It is important to understand how to "Escape Characters" when pattern matching. These examples deal specifically with escaping characters in PostgreSQL.


Like operator condition in postgresql data issue, It works just fine. However, you think -- for some reason -- that LIKE supports character classes and other regular-expression-like features. There are three types of pattern matching in PostgreSQL : LIKE operator, SIMILAR TO operator, and POSIX-style regular expressions. The LIKE expression returns true if the string matches the supplied pattern. and the NOT LIKE expression returns false if LIKE returns true.


PostgreSQL : Like Operator, Search conditions can contain either literal characters or numbers: An underscore (_) in pattern stands for (matches) any single character; a  In PostreSQL you can use SIMILAR TO operator : -- only digits select * from books where title similar to '^[0-9]*$'; -- start with digit select * from books where title similar to '^[0-9]%$'; share | improve this answer | follow | | | |


PostgreSQL: LIKE Condition, and examples. The PostgreSQL LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. If the condition's result is not true, any subsequent WHEN clauses are examined in the same manner. If no WHEN condition yields true, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is null. An example: