CODEWITHSUNDEEP
sqlregexpostgresql
Paul Kruger
Paul Kruger

Reputation: 2304

SQL Substring \g

I would just like to know where do I put the \g in this query?

SELECT project, 
SUBSTRING(address FROM 'A-Za-z') AS letters, 
SUBSTRING(address FROM '\d') AS numbers 
FROM repositories

I tried this but this brings back nothing (it doesn't throw an error though)

SELECT project, 
SUBSTRING(CONCAT(address, '#') FROM 'A-Za-z' FOR '#') AS letters, 
SUBSTRING(CONCAT(address, '#') FROM '\d' FOR '#') AS numbers 
FROM repositories

Here is an example: I would like the string 1DDsg6bXmh3W63FTVN4BLwuQ4HwiUk5hX to return DDsgbXmhWFTVNBLwuQHwiUkhX. So basically return all the letters...and then my second one is to return all the numbers.

Upvotes: 3

Views: 810

Answers (2)

Paul Kruger
Paul Kruger

Reputation: 2304

So this is not pure SQL but Postgresql, but this also does the job:

SELECT project,
       regexp_replace(address, '[^A-Za-z]', '', 'g') AS letters,
       regexp_replace(address, '[^0-9]', '', 'g') AS numbers
FROM repositories;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246523

The g (“global”) modifier in regular expressions indicates that all matches rather than only the first one should be used.

That doesn't make much sense in the substring function, which returns only a single value, namely the first match. So there is no way to use g with substring.

In those functions where it makes sense in PostgreSQL (regexp_replace and regexp_matches), the g can be specified in the optional last flags parameter.

If you want to find all substrings that match a pattern, use regexp_matches.

For your example, which really has nothing to do with substring at all, I'd use

SELECT translate('1DDsg6bXmh3W63FTVN4BLwuQ4HwiUk5hX', '0123456789', '');

         translate         
---------------------------
 DDsgbXmhWFTVNBLwuQHwiUkhX
(1 row)

Upvotes: 2

Related Questions