Select Where Like regular expression

Question

I need to create a SQL Query. This query need to select from a table where a column contains regular expression.

For example, I have those values:

TABLE test (name)

XHRTCNW
DHRTRRR
XHRTCOP
CPHCTPC
CDDHRTF
PEOFOFD

I want to select all the data who have "HRT" after 1 char (value 1, 2 and 3 - Values who looks like "-HRT---") but not those who might have "HRT" after 1 char (value 5).

So I'm not sure how to do it because a simple

SELECT * 
FROM test 
WHERE name LIKE "%HRT%"

will return value 1, 2, 3 and 5.

Sorry if I'm not really clear with what I want/need.

plsql is for Oracle only.

jarlh
– jarlh

2016-01-13 14:15:13 +00:00
Commented Jan 13, 2016 at 14:15 — jarlh
– jarlh, Commented Jan 13, 2016 at 14:15

Gary - Stand with Ukraine · Accepted Answer · 2016-01-13 14:19:18Z

2

You can also change the pattern. Instead of using % which means zero-or-more anything, you can use _ which means exactly one.

SELECT * FROM test WHERE name like '_HRT%';

answered Jan 13, 2016 at 14:19

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

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

3 Comments

Dabeliuteef Over a year ago

Oh, this way is also working thanks ! Is there any other char that might means other value than "_" ?

Gary - Stand with Ukraine Over a year ago

The LIKE function is quite restricted and only uses "_" and "%" as wildcards. There are other functions available that do full regular expression matching etc. Details available at: postgresql.org/docs/current/static/functions-matching.html

Dabeliuteef Over a year ago

Thanks for the tip !

Vamsi Prabhala · Accepted Answer · 2016-01-13 14:13:48Z

1

You can use substring.

SELECT * FROM test WHERE substring(name from 2 for 3) = 'HRT'

answered Jan 13, 2016 at 14:13

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

1 Comment

Dabeliuteef Over a year ago

Cool ! It's working with this function. Is there any link where I can find "All the function" available in the Where section?

Dom Vito · Accepted Answer · 2016-01-13 14:16:59Z

0

Are the names always 7 letters? Do:

SELECT substring (2, 4, field) from sometable

That will just select the 2-4th characters and then you can use like "%HRT"

answered Jan 13, 2016 at 14:16

Dom Vito

5771 gold badge12 silver badges44 bronze badges

2 Comments

Dabeliuteef Over a year ago

Nop sadly, it's not always 7 letters

Dom Vito Over a year ago

Regardless, all three answers will return the entries you are looking for.

Collectives™ on Stack Overflow

Select Where Like regular expression

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related