Styjun

How often should alkaline batteries be checked when they are in a device?

Ultraproduct of Dividing Lines

Has Peter Parker ever eaten bugs?

What kind of world would drive brains to evolve high-throughput sensory?

Host telling me to cancel my booking in exchange for a discount?

If I have the Armor of Shadows Eldritch Invocation do I know the Mage Armor spell?

Company requiring me to let them review research from before I was hired

Chemistry Riddle

How can I deal with someone that wants to kill something that isn't supposed to be killed?

How can I calculate the cost of Skyss bus tickets

What is "ass door"?

What is the best word describing the nature of expiring in a short amount of time, connoting "losing public attention"?

Does Impedance Matching Imply any Practical RF Transmitter Must Waste >=50% of Energy?

Does switching on an old games console without a cartridge damage it?

Why do people say "I am broke" instead of "I am broken"?

Are there any documented cases of extinction of a species of fungus?

Pgfplots fillbetween and Tikz shade

Is it ethical to tell my teaching assistant that I like him?

How did pilots avoid thunderstorms and related weather before “reliable” airborne weather radar was introduced on airliners?

What is a "staved" town, like in "Staverton"?

How am I supposed to put out fires?

Considerations when providing money to one child now, and the other later?

Why does the salt in the oceans not sink to the bottom?

Character Arcs - What if the character doesn't overcome the big lie, flaws or wounds?

How to get all numbers before character?

How does database indexing work?How to validate an email address in JavaScriptWhat is the difference between UNION and UNION ALL?How can I prevent SQL injection in PHP?A comprehensive regex for phone number validationHow to validate an email address using a regular expression?Get list of all tables in Oracle?How do I make the first letter of a string uppercase in JavaScript?How to replace all occurrences of a string?How do I UPDATE from a SELECT in SQL Server?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

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'

sql regex vertica

share|improve this question

edited Mar 26 at 14:57

a_horse_with_no_name

324k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 14:28

Chick ChirikChick Chirik

5088 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Which DBMS are you using?
  
  – FDavidov
  Mar 26 at 14:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @FDavidov Vertica
  
  – Chick Chirik
  Mar 26 at 14:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Didn't have the pleasure to get acquainted with it. Sorry. The only thing I can tell you is that if you are going to perform a select in which the WHERE includes regex's, you might need to wait for a long time for the result (here I'm assuming that your table might be quite big). I would suggest you first get a subset of the matching records (e.g. records that contain one or more digits in the particular field), and then perform a second scan using your regex expression.
  
  – FDavidov
  Mar 26 at 14:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @FDavidov thanks for advice! and maybe you could help me with regular expression? I think it doesn't change too much from DBMS to DBMS
  
  – Chick Chirik
  Mar 26 at 14:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you always have two / to separate the elements?
  
  – a_horse_with_no_name
  Mar 26 at 15:00
  

  
  
  
  

 | 
show 3 more comments

1

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'

sql regex vertica

share|improve this question

edited Mar 26 at 14:57

a_horse_with_no_name

324k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 14:28

Chick ChirikChick Chirik

5088 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Which DBMS are you using?
  
  – FDavidov
  Mar 26 at 14:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @FDavidov Vertica
  
  – Chick Chirik
  Mar 26 at 14:37
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Didn't have the pleasure to get acquainted with it. Sorry. The only thing I can tell you is that if you are going to perform a select in which the WHERE includes regex's, you might need to wait for a long time for the result (here I'm assuming that your table might be quite big). I would suggest you first get a subset of the matching records (e.g. records that contain one or more digits in the particular field), and then perform a second scan using your regex expression.
  
  – FDavidov
  Mar 26 at 14:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @FDavidov thanks for advice! and maybe you could help me with regular expression? I think it doesn't change too much from DBMS to DBMS
  
  – Chick Chirik
  Mar 26 at 14:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Do you always have two / to separate the elements?
  
  – a_horse_with_no_name
  Mar 26 at 15:00
  

  
  
  
  

 | 
show 3 more comments

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'

sql regex vertica

share|improve this question

edited Mar 26 at 14:57

a_horse_with_no_name

324k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 14:28

Chick ChirikChick Chirik

5088 bronze badges

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

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

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

Main/Search/1_

share|improve this question

edited Mar 26 at 14:57

a_horse_with_no_name

324k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 14:28

Chick ChirikChick Chirik

5088 bronze badges

share|improve this question

edited Mar 26 at 14:57

a_horse_with_no_name

324k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 14:28

Chick ChirikChick Chirik

5088 bronze badges

edited Mar 26 at 14:57

a_horse_with_no_name

324k5151 gold badges503503 silver badges602602 bronze badges

edited Mar 26 at 14:57

a_horse_with_no_name

324k5151 gold badges503503 silver badges602602 bronze badges

asked Mar 26 at 14:28

Chick ChirikChick Chirik

5088 bronze badges

asked Mar 26 at 14:28

Chick ChirikChick Chirik

5088 bronze badges

1 Answer
1

active

oldest

votes

0

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

share|improve this answer

edited Mar 26 at 16:38

answered Mar 26 at 15:17

A. SaundersA. Saunders

30811 gold badge11 silver badge1212 bronze badges

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55359608%2fhow-to-get-all-numbers-before-character%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

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

share|improve this answer

edited Mar 26 at 16:38

answered Mar 26 at 15:17

A. SaundersA. Saunders

30811 gold badge11 silver badge1212 bronze badges

add a comment | 

0

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

share|improve this answer

edited Mar 26 at 16:38

answered Mar 26 at 15:17

A. SaundersA. Saunders

30811 gold badge11 silver badge1212 bronze badges

add a comment | 

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

share|improve this answer

edited Mar 26 at 16:38

answered Mar 26 at 15:17

A. SaundersA. Saunders

30811 gold badge11 silver badge1212 bronze badges

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

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

share|improve this answer

edited Mar 26 at 16:38

answered Mar 26 at 15:17

A. SaundersA. Saunders

30811 gold badge11 silver badge1212 bronze badges

answered Mar 26 at 15:17

A. SaundersA. Saunders

30811 gold badge11 silver badge1212 bronze badges

answered Mar 26 at 15:17

A. SaundersA. Saunders

30811 gold badge11 silver badge1212 bronze badges