+1 vote
in Databases by (75.1k points)
I want to select all codes from a PostgreSQL table that match the pattern "F1" followed by any digit and dot, and then any sequence of characters in SQL. If I use the LIKE operator, I need to mention all codes one by one. Is there any operator that I can use in SQL?

Codes should be something like these:

F10.34, F14.762A

1 Answer

+2 votes
by (59.8k points)
selected by
Best answer

In PostgreSQL, you can use the ~ operator for writing regular expressions for such queries:

Here is the SQL you can use:

select * 

from code_table

where code ~ '^F1[0-9]\..*';

In the above query, you can change table name for your requirement. 

In the above query:

  • ^ asserts the position at the start of the string.
  • F1 matches the characters "F1".
  • [0-9] matches any digit.
  • \. matches the literal dot character. 
  • .* matches any sequence of characters (including no characters)

Related questions