PostgreSQL Regexp - Find records with single char

Question

I am trying to get from table all records, that have in column only one -.

select ta.name, ta.created from tol.order ta 
where ta.name similar to '%-{1}%' and ta.created > '2018-05-01'

But have no success.

For instance I should get all records with name like:

'test-testtest'

but NOT like

'test-test-test'

Thanks for any advise and help.

I've added an answer for you. Let me know is that worked for you or not? — A l w a y s S u n n y
– A l w a y s S u n n y, Commented Dec 27, 2018 at 15:24

A l w a y s S u n n y · Accepted Answer · 2018-12-27 15:28:41Z

1

A non-regex way with PostgreSQL's built in function. See Array Function

SELECT ta.name, ta.created from tol.order ta 
WHERE (array_length(string_to_array(ta.name, '-'),1)-1) = 1 
    and ta.created > '2018-05-01'

answered Dec 27, 2018 at 15:23

38.8k9 gold badges68 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andie2302 · Accepted Answer · 2018-12-27 15:31:22Z

0

Have you tried sth like:

SELECT ta.name, ta.created from tol.order ta WHERE ta.name ~ $rxb$^[^-]*?-[^-]*$$rxb$ and ta.created > '2018-05-01';

answered Dec 27, 2018 at 15:00

Andie2302

4,9254 gold badges26 silver badges46 bronze badges

not selects orders with name like 'test-testtest', just selects nothing

Hambone · Accepted Answer · 2018-12-27 16:08:01Z

0

I would take the opposite approach and do a regex to exclude anything that had:

a hyphen: - followed by any characters (including none): .* followed by another hyphen: -

select ta.name, ta.created
from tol.order ta 
where ta.name !~ '-.*-' and ta.created > '2018-05-01

answered Dec 27, 2018 at 16:08

Hambone

16.5k8 gold badges54 silver badges79 bronze badges