The operator ~~ is equivalent to LIKE, while ~~* corresponds to ILIKE. There are also !~~ and !~~* operators represent NOT LIKE and NOT ILIKE respectively.
in addition:
~ Match regular expressions, case-related 'thomas' ~ '.*thomas.*'
~*Search Match regular expressions, case irrelevant 'thomas' ~* '.*Thomas.*'
!~ Does not match regular expressions, case-related 'thomas' !~ '.*Thomas.*'
!~* does not match regular expressions, case irrelevant 'thomas' !~* '.*vadim.*'
Supplement: The key to Postgresql string retrieval is the meaning and difference between the symbols LIKE, ESCAPE, ILIKE, ~, ~*, !~, !~*
Recently, I was working on a project related to postgreSQL. I encountered some processing on string retrieval in my work. The following is my understanding of the key to string retrieval and symbols LIKE, ESCAPE, ILIKE, ~, ~*, !~, !~*. Please point out the wrong points.
LIKE
Select * from test where varchar like ‘_a%';
varchar matches any string with a character preceded by a.
'_' and '%' are wildcard characters. '_' matches any character, '%' matches 0 to more than one character.
ESCAPE
Select* from test where varchar like ‘b_a%'escape ‘b';
varchar matches any string starting with '_a'.
ESCAPE 'escape_character' allows searching for wildcards in strings instead of using them as wildcards.
Please note that backslashes already have special meanings in string text, so if you write a pattern constant containing backslashes, you have to write two backslashes in SQL statements.
Therefore, writing a pattern that matches a single backslash actually requires four backslashes in the statement.
You can avoid this by selecting a different escape character with ESCAPE; the backslash is no longer a special character for LIKE.
But it's still a special character for the character text analyzer, so you still need two backslashes.
We can also turn off the escape mechanism by writing ESCAPE '', at this time, we cannot turn off the special meaning of underscores and percent signs.
example:
Find the name of the table in the database (find the name of the table starting with tbl_)
select tablename from pg_tables where tablename like 'tbl!_%'ESCAPE '!';
Use escape: The above SQL statement is to convert ! as an escape character, and the function is to convert "_" into ordinary "_"
ILIKE
Select * from test where varchar ilike ‘_a%';
varchar matches any string with a character before A or A.
Compared to Like, ILike ignores upper and lower case.
Regular expression matching operator
~ Match regular expressions, case-related
Select * from test where varchar ~ ‘ab‘;
varchar matches any string containing 'ab'.
~* matches regular expressions, case irrelevant
Select * from test where varchar ~‘AB‘;
varchar matches any string containing 'ab', 'aB', 'Ab', 'AB'.
Compared to ' ~', case is ignored.
!~ Does not match regular expressions, case-related
Select * from test where varchar ~ ‘a‘;
varchar matches any string that does not contain 'a'.
!~* Does not match regular expressions, case irrelevant
select * from test where varchar !~* ‘A‘;
varchar matches any string that does not contain 'a' and 'A'.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.