postgres regex substring escape character

Question

The data contains a series of names of the format:

ABC123456 XYZ987654 DE-234567

The query

select distinct(substring(field, '([A-Z]{1,3})')) pref, count(*) tot from table;

identifies entries with characters. The question is: what is the escape character pull the '-' character along with the alpha characters. The '-' character is used in the select character sequence [A-Z]. How can that selection sequence be extended to include the '-' character.

distinct is NOT a function

user330315
– user330315

2016-06-04 16:30:13 +00:00
Commented Jun 4, 2016 at 16:30 — user330315
– user330315, Commented Jun 4, 2016 at 16:30

Sergey Kalinichenko · Accepted Answer · 2016-06-04 16:27:12Z

1

The '-' character is used in the select character sequence [A-Z]

Dash - is interpreted as a range only when it is in the middle of other characters. If you put it at the beginning or at the end of a character class, it becomes just a regular character:

select distinct(substring(field, '([A-Z-]{1,3})')) pref, count(*) tot from  table;
--                                     ^

[-A-Z] would work as well.

answered Jun 4, 2016 at 16:27

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

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

Collectives™ on Stack Overflow

postgres regex substring escape character

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related