0

Back in SQL Server, I use the command below to get the matching string pattern of #1234 from the column called Number

SELECT [m].[Number]
FROM [Boats] AS [m]
WHERE [m].[Number] LIKE N'[#][0-9][0-9][0-9][0-9]'

Now in Postgres, I have tried using the command below, but no luck:

SELECT m.Number
FROM Boats AS m
WHERE m.Number ILIKE '#[0-9][0-9][0-9][0-9]'

Any idea what pattern expression should I use to get the correct filter?

1
  • 1
    LIKE doesn't support regular expressions in SQL
    – user1822
    Feb 16, 2021 at 6:11

1 Answer 1

2

You can use the operator ~ that uses the full power of regular expressions

select *
from (values ('#1234'), ('#3333'), ('#abcd')) as boats (number)
where number ~* '#\d{4}'

A stricter version of the regex would be '^#\d{4}$', so that it matches only strings that start with # then have 4, and only 4 numbers, and then there is the end of the string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.