Select only letters which are followed by a number

Question

I am trying to select some codes from a PostgreSQl table. I only want the codes that have numbers in them e.g

GD123 
GD564

I don't want to pick any codes like `GDTG GDCNB

Here's my query so far:

select regexp_matches(no_, '[a-zA-Z0-9]*$')
from myschema.mytable

which of course doesn't work. Any help appreciated.

Answer 1

The pattern to match a string that has at least 1 letter followed by at least 1 number is '[A-Za-z]+[0-9]+'.

Now, if the valid patterns had to start with two letters, and then have 3 digits after as your examples show, then replace the + with {2} & {4} respectively, and enclose the pattern in ^$, like this: '^[A-Za-z]{2}[0-9]{3}$'

The regex match operator is ~ which you can use in the where clause:

SELECT no_
FROM myschema.mytable
WHERE no_ ~ '[A-Za-z]+[0-9]+'

Answer 2

You may use

CREATE TABLE tb1
    (s character varying)
;

INSERT INTO tb1
    (s)
VALUES
    ('GD123'),
    ('12345'),
    ('GDFGH')
;

SELECT * FROM tb1 WHERE s ~ '^(?![A-Za-z]+$)[a-zA-Z0-9]+$';

Result:

Details

• ^ - start of string
• (?![A-Za-z]+$) - a negative lookahead that fails the match if there are only letters to the end of the string
• [a-zA-Z0-9]+ - 1 or more alphanumeric chars
• $ - end of string.

If you want to avoid matching 12345, use

'^(?![A-Za-z]+$)(?![0-9]+$)[a-zA-Z0-9]+$'

Here, (?![0-9]+$) will similarly fail the match if, from the string start, all chars up to the end of the string are digits. Result: