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
0
votes
2 answers

Need to extract data from string by regexp_substr

I have a ["1101124","1101123","123456"], I need to get the end result as rows for the numbers which are in the bracket. How can I achieve this by using regular expression in Oracle.
Harish
  • 15
  • 4
0
votes
1 answer

search substring in string

I'm looking for a regexp to get the correct output For my example: SELECT regexp_substr('brablcdefghig', '[^(bl)]+$') FROM dual; I expect evth what is follow 'bl': cdefghig and it's OK, But when I modify input and add 'b' charcter I've NULL in…
Kilro
  • 39
  • 1
  • 4
0
votes
1 answer

regexp_substr ignore when punctuation in middle of string (oracle)

I am looking at large strings in text. In the text alot of times have . or especially ? ? that serves as delimiters. So what I want from the string 'Delivery Note ?Patient was at home. Delivered at home' is Delivered at home BUT NOT Delivery Note…
Lawrence Block
  • 71
  • 9
0
votes
1 answer

Use connect by by in REGEXP_SUBSTR without breaking result to multiple rows

SELECT CHR(91)||'a-zA-Z0-9._%-'||CHR(93)||'+'|| listagg(REGEXP_SUBSTR('aaa@yahoo.com, bbb@hotmail.com', '@'||CHR(91)||'^,'||CHR(93)||'+', 1, LEVEL), ', ') within group (order by level) as domain FROM DUAL CONNECT BY REGEXP_SUBSTR('aaa@yahoo.com,…
user2102665
  • 429
  • 2
  • 11
  • 26
0
votes
1 answer

redshift regexp_substr all occurrences

I am trying to return all results that match a specific regex in Redshift database. Sample query WITH aa AS (SELECT DISTINCT id, record, regexp_substr(record, '(#{2})([A-Z]{2,3})',1,1), REGEXP_COUNT(record, '(#{2})([A-Z]{2,3})') FROM…
Pericles Faliagas
  • 636
  • 9
  • 29
0
votes
2 answers

Oracle regex to extract string between first pair of < and > brackets

I am have been assigned a task to parse a string (which is essentially in XML format) and I need to extract the name of the first tag in the string eg: string 'string.............' or …
kunal kavthekar
  • 27
  • 5
0
votes
2 answers

Trouble running MySQL REGEXP_SUBSTR within STR_TO_DATE to extract date from text

I'm having trouble running MySQL REGEXP_SUBSTR within STR_TO_DATE to extract a date from a text field. If can run the following query, and use REGEXP_SUBSTR to properly extract the date string. SELECT REGEXP_SUBSTR('12/9/97 - Ependymoma!',…
jayurbain
  • 429
  • 1
  • 5
  • 11
0
votes
3 answers

I want to extract text before second dot(.) using regexp function from string like :

I want to extract text before second dot(.) from string like: Input - XYZ.ABC.MNO.CZ Output- XYZ.ABC Input - AWQ.QA Output- AWQ.QA
Singh Manjot
  • 11
  • 2
0
votes
3 answers

oracle sql extract the word before a specific word

Using oracle, how to get a specific word before a word. i got a sample script that i found here but its in reverse(it gets the word after a specific word which is the CITY) select regexp_substr ('TEXAS CITY CALIFORNIA',…
tan
  • 19
  • 4
0
votes
1 answer

ORACLE SQL: how to get text before a specific word

Using Oracle, how can i get the text before a specific word(CITY) including the word CITY Sample: 22nd street westlake 1378 california city 32nd street texas 1111 houston city Result: California city Houston city Removing the city from…
tan
  • 19
  • 4
0
votes
1 answer

REGEXP_SUBSTR: Unmatched Parentheses in regular expression error

Background: I have 3 columns: Item, Description, Description2. An item can be replaced with another item number, and is indicated in the description or description2 columns. Description and Description2 contains descriptions for the item, and…
kyleuser1
  • 1
0
votes
1 answer

postgresql : regexp_substr - get sub string between occurrence of delimiters

I have these strings: [{"Name":"id","Value":"Window_Ex_kebklipecbcegiocpa_widget_open" [{"Name":"id","Value":"Window_Ex_kebklipecbcegiocpa_widget_close" [{"Name":"id","Value":"Window_Ex_kebklipecbcegiocpa_widget_mid_value" and I'm trying to extract…
Itay Av
  • 69
  • 3
  • 10
0
votes
2 answers

Oracle SUM( TO_NUMBER( REGEXP_SUBSTR WITH DECIMAL NUMBER

I have query like this SELECT SUM( TO_NUMBER( REGEXP_SUBSTR( '2,0,0,0,0,0,8.36,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0', '\d+', 1, LEVEL ) ) ) AS SUM_TOTAL FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(…
dj_killer13
  • 9
  • 6
0
votes
1 answer

Oracle Regular Expression-To match exclude numbers

Hi I am trying to exclude the numbers from my case statement in REGEXP_LIKE but i am not getting the desired result Query SELECT OWNER, OBJECT_NAME, REGEXP_REPLACE(OBJECT_NAME, '^([A-Z0-9$]{1,})_([A-Z0-9$]{1,})_.*','\1_\2')AS …
Data2explore
  • 452
  • 6
  • 16
0
votes
2 answers

Oracle regular expression match string from last occurence

I'm still learning regexp in oracle and stuck with below error. Below is my sample code SELECT DISTINCT COALESCE(TO_NUMBER(regexp_substr(USERNAME, '[^.]+', 1, 2)), ID) ID , COALESCE(regexp_substr(USERNAME, '[^.]+', 1, 1), USERNAME)…
What's In A Name
  • 1
1 2 3
…
23 24