CODEWITHSUNDEEP
sqlpostgresqlpattern-matchingsql-like
Nick
Nick

Reputation: 8493

LIKE match string ending with a number

I've got a column that has resource paths in it that look like this:

/apiv3/widgets/100
/apiv3/widgets/search
/apiv3/widgets/new

I'm trying to get a LIKE or REG_EX like that will only match a string that ends in a number

I've tried a few things such as:

LIKE '\/apiv3\/widgets\/[0-9]%'
LIKE '\/apiv3\/widgets\/[^0-9]%'

How can I match a path that only ends in a numeric value of any arbitrary length?

Upvotes: 3

Views: 8540

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659207

Use a regular expression with the ~ operator.
For strings ending with a digit:

... WHERE value ~ '\d$'

\d ... shorthand class for digits
$ ... matches at the end of a string

E'\d$' would be incorrect. This is not an escape string.

For strings ending with only digits after the last /:

... WHERE value ~ '/\d+$'

+ ... one or more atoms
/ ... literal '/'

Quick test:

SELECT x, x ~ '\d$' AS one,  x ~ '/\d+$' AS two
FROM  (
    VALUES
     ('/apiv3/widgets/100')
    ,('/apiv3/widgets/search')
    ,('/apiv3/widgets/new')
    ,('/apiv3/widgets/added_test17')
    ) AS t(x);

              x              | one | two
-----------------------------+-----+-----
 /apiv3/widgets/100          | t   | t
 /apiv3/widgets/search       | f   | f
 /apiv3/widgets/new          | f   | f
 /apiv3/widgets/added_test17 | t   | f

Upvotes: 14

Chris Allen
Chris Allen

Reputation: 194

See section 9.7.2 for 'SIMILAR' regular expressions. LIKE does not support them.

http://www.postgresql.org/docs/current/static/functions-matching.html

Examples:

'abc' SIMILAR TO 'abc'      true
'abc' SIMILAR TO 'a'        false
'abc' SIMILAR TO '%(b|d)%'  true
'abc' SIMILAR TO '(b|c)%'   false

Upvotes: 0

Related Questions