CODEWITHSUNDEEP
sqlregexvertica
Chick Chirik
Chick Chirik

Reputation: 115

How to get all numbers before character?

I need to get all rows in base that satisfy the next logic:

[some text/][digits(one or more)]_[some text]

For example, 

'Main/Search/124_mobile'
'Main/Search/4_service'

Firstly I need to get these rows and then get digits before _ symbol.

I tried this type of regex:

regexp_like(event, '^[Main/Search/[1-9]+(?=_')

But it only extracts rows like:

Main/Search/1_

and doesn't extract rows with many digits before _ symbol

In the end, I expect to get digits before _ symbol. For a value 'Main/Search/124_mobile' it'll be '124'

Upvotes: 1

Views: 1107

Answers (2)

Basil Peace
Basil Peace

Reputation: 412

More efficient way:

SELECT digits
FROM (
    SELECT REGEXP_SUBSTR(event, '.*\/(\d+)\_.*', 1, 1, '', 1) digits
    FROM t
) s
WHERE digits IS NOT NULL

Also, when there are no other digits in event, you can use just REGEXP_SUBSTR(event, '\d+').

Upvotes: 0

A. Saunders
A. Saunders

Reputation: 875

What you are looking for is this.

SELECT REGEXP_REPLACE(event, '.*\/(\d+)\_.*', '\1')

First of all, I find with Vertica RegEx functions it is better to use \d (short for digits) instead of \[0-9] for numeric matching.

The pattern .*\/(\d+)\_.* matches the entire value of event, but because \d+ is inclosed in parentheses, it becomes the first captured group, which is represented as backslash 1 \1 in the replacement argument, so that even though the entire value in event is matched, only the first group \1 will be shown.

To filter so that only rows that contain that pattern show up in your query results, use REGEXP_LIKE.

The whole query will look something like this.

SELECT REGEXP_REPLACE(event, '.*\/(\d+)\_.*', '\1')
FROM table
WHERE REGEXP_LIKE(event, '.*\/\d+\_.*');

For more information see the last example (the one at the bottom about phone numbers) on this page in the documentation: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/RegularExpressions/REGEXP_REPLACE.htm

Upvotes: 0

Related Questions