Stack Overflow
Stack Overflow

Questions tagged [regexp-substr]

Oracle function that extends the functionality of the SUBSTR function by allowing searching a string for a regular expression pattern. See also REGEXP_INSTR, REGEXP_REPLACE, REGEXP_LIKE and REGEXP_COUNT for other functions extended to use regular expressions.

353 questions
19
votes
1 answer

How to extract a substring pattern in Postgresql

I have a column with a lot of inconsistent strings. Some of them contain a substring with a consistent pattern of '2015mmdd_AB_CD_EFG_(text)_(text)_HIJ' which I would like to extract. I feel this is a cross over case of regexp and a substring…
thenaturalist
  • 787
  • 2
  • 8
  • 15
6
votes
1 answer

REGEXP_SUBSTR return all matches (mariaDB)

I need to get all the matches of a regular expression in a text field in a MariaDB table. As far as I know REGEXP_SUBSTR is the way to go to get the value of the match of a regular expression in a text field, but it always returns after the first…
Driond
  • 113
  • 1
  • 4
6
votes
4 answers

SQL Regex - Replace with substring from another field

I have a database table (Oracle 11g) of questionnaire feedback, including multiple choice, multiple answer questions. The Options column has each value the user could choose, and the Answers column has the numerical values of what they chose. ID_NO …
Pete Rennard-Cumming
  • 1,588
  • 10
  • 19
6
votes
3 answers

Convert "regexp_substr" (Oracle ) to PostgreSQL

I have the query In Oracle SQL: select town_name, regexp_substr(town_name, '[^A,]+', 1, 1) as c1, regexp_substr(town_name, '[^A,]+', 1, 2) as c2, regexp_substr(town_name, '[^A,]+', 1, rownum) as…
Catalin Vladu
  • 389
  • 1
  • 6
  • 17
6
votes
3 answers

Retrieving first X words from a string in Oracle Select

I need to select the first X words in a string, where x can be any number from 0-100. Is there an easy way to do this? I found the following example to select the first 2 words from a string: select regexp_replace('Hello world this is a test',…
Robert Smith
  • 779
  • 1
  • 10
  • 28
4
votes
1 answer

Redshift regular expression for domain extraction

I'm trying to form a regular expression for REGEXP_SUBSTR (Redshift) which will extract the sub-domain & domain part from any given URL. I tried many suggestions from stackoverflow: regular-expression-extract-subdomain-domain,…
Pavel D
  • 41
  • 1
  • 2
3
votes
2 answers

special characters and characters capture from a string oracle

I am trying to capture the table names starting with some patterns and find the total counts for each group but i would specifically like to capture ,below is an example I think the best possible way to capture the above is using REGEXP ? Query 1 -…
Data2explore
  • 452
  • 6
  • 16
3
votes
1 answer

Comparing comma separated values from two columns of two different tables

I want to compare the values of two columns (diff table) having comma separated values of two different Oracle tables. I want to find rows that match with all values (NAME1 all values should match with NAME2 values). Note: The comma separated…
Pramod Kumar
  • 59
  • 1
  • 6
3
votes
2 answers

How to split a string seperated by space in oracle and add each word to a different row in Oracle 11g?

I am trying to split a string and add each word to a separate rows. with data as ( select 100 as id, 'python java' as src from dual ) select id, level as lvl, regexp_substr( src || '" "' , '([[:space:]0-9/:])', 1, level, null, 1 ) as…
Ana
  • 325
  • 2
  • 11
3
votes
2 answers

Replace all slashes on a line with a match on at the beginning of the same line

I'm trying to change all slashes on a line to replace with the 3-characters block at the beginninig of each line. (PMC,PAJ, etc in below…
QAZ001
  • 109
  • 7
3
votes
3 answers

How to get file name without extension with using Regular Expressions

I have a field with following values, now i want to extract only those rows with "xyz" in the field value mentioned below, can you please help? Mydata_xyz_aug21 Mydata2_zzz_aug22 Mydata3_xyz_aug33 One more requirement I…
general46
  • 800
  • 6
  • 16
3
votes
2 answers

PL/SQL Split string by pattern

Similarly to this question... How can I use regex to split a string, using a string as a delimiter? ... I'm trying to split the following string: Spent 30 CAD in movie tickets at Cineplex on 2018-06-01 My desired output is this: ELEMENT…
Jaquio
  • 183
  • 1
  • 11
3
votes
2 answers

How to split strings using two delimiter in Oracle 11g regexp_substr functions

I have doubt to split a string using the delimiter. First split based on , delimiter select those splitted strings should split based on - delimiter My original string: UMC12I-1234,CSM3-123,VQ, Expected output: UMC12I CSM3 VQ Each value comes as…
Chidam
  • 37
  • 2
  • 12
3
votes
1 answer

Oracle - get table name from sql text

I have a column in a table with simple sql queries and I want to regexp_substr the table name from them. Examples of texts: SELECT PT.PT_PARTY_NAME VALUE,PT.PT_PARTY_NAME LABEL FROM DWH_OWNER.DWH_ACCOUNTS ACC, DWH_OWNER.DWH_PARTIES PT …
Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
3
votes
1 answer

How can I use regex to split a string, using a string as a delimiter?

I'm trying to split a string using a string as a delimiter, in an Oracle store procedure. I can use instr easily, but I'm trying to learn how to do this with regex, as I understand that it is powerful and efficient. After reading some articles, I…
Zesty
  • 2,922
  • 9
  • 38
  • 69
1
2 3
…
23 24