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.
Questions tagged [regexp-substr]
353 questions
0
votes
0 answers
regexp_substr coalesce nulliff
I am trying to achieve extract the version number of an app. Some rows are missing the app version and i wanted to populate that as well.
post_mobileappid
app_version
My someword 13.0.0
13.0.0
MySomeWord 12.0
12.0
I have the…
Don_g_ta
- 30
- 1
- 6
0
votes
1 answer
How to remove the special characters in a string
Here is the situation
WITH q AS (SELECT '( This is Z12783)' AS sentence FROM DUAL)
SELECT REGEXP_SUBSTR(sentence,'Z[0-9].*')
FROM q;
Desired Output:
Z12783
But the Output I get is:
Z12783)
Is there a way to remove the ')' at the end within this…
StackOne
- 260
- 2
- 16
0
votes
1 answer
I want to extract a string with escaping sign using regex in ORACLE SQL
I have got a string with multiple possible ANSWERS but only one is correct:
"shop.com\nshop.net\nvouchers.com [[WIN]]"
"39 Euro [[WIN]]\n49 Euro\n59"
"Euro 7 things\n12 things[[WIN]]\n21 things"
I need to extract the right/winning…
Tobias G.
- 7
- 3
0
votes
1 answer
Equivalent to regexp_replace((^[A-Z])|([IJ])|(?<= ).) in regexp_substr
In DB2, the next query works fine:
select REGEXP_REPLACE( 'René de la Ömer-Arie IJsbrand IJsse'
,'(^[A-Z])|([IJ])|(?<= ).','') FROM "SYSIBM".SYSDUMMY1
Result: ené e a mer-Arie sbrand sse
However, I would like to achieve the…
amazereijn
- 3
- 3
0
votes
1 answer
How to use Regex with numbers
I am new to programming and building my first full stack application. In my application I have two entities, Branches and Accounts. Branches names are alphanumerical strings whereas the Accounts are just numbers for example 116003183, 16582368. The…
Ali Zeeshan
- 43
- 3
0
votes
1 answer
Finding a Field Based on a String then Extracting a Substring Value
I am looking to extract only the Serial No: value (1G20441XC454) from the following query result:
SELECT REGEXP_SUBSTR('Entry: Make: 1, Model: 6, Year: 7, Serial No: 1G20441XC454, Point of Origin: ', '') "REGEXPR_SUBSTR" FROM…
Stephen
- 1
- 2
0
votes
1 answer
REGEXP_INSTR with more than a value
I have two tables A and B, for example
A
The 1 is cat
The 2 is dog
The 3 is horse
The 4 is chicken
The 5 is hippo…
Gtman
- 1
- 3
0
votes
2 answers
How to find variable pattern in MySql with Regex?
I am trying to pull a product code from a long set of string formatted like a URL address. The pattern is always 3 letters followed by 3 or 4 numbers (ex. ???### or ???####). I have tried using REGEXP and LIKE syntax, but my results are off for…
Gordon
- 1
- 1
0
votes
1 answer
Oracle [0RA-01722 :invalid number] upon casting regexp_substr to NUMBER
We are working on Oracle Database SQL Query.
DataBase Structure:
(etd_log_id, region,sla_status,escaltion_level,added_ts)
escalation_level field has:
1,2,3,4,5,6,7,8,9,10…
Akash
- 1
0
votes
1 answer
JS Pig poem matching substring with Regexp
I have a one long string with sentences, every sentence contains a number. Every sentence is separated by /X. Having a given number, how can I match and extract only the one sentence which contains that number?
"\X The animal 0000 I really dig \X…
Lukasz Pospiech
- 15
- 5
0
votes
1 answer
Substring of a string using Oracle (SUBSTRING or REGEXP_SUBSTR) When there is multiple Matches
Hi I am trying to fetch substring in oracle for the below two strings. I want result if the pattern matches starting with S9C and the having next numbers
For Eg: for the below two inputs I need output like
Input:
1.CABLE : THERMINAL 3X2X0.25MM FPCP…
Robinhood
- 92
- 2
- 10
0
votes
3 answers
Oracle Create formula for dynamic SQL to prevent divide by zero
I have a table with formula column that have formula to fetch to dynamic SQL LIKE n5/n14+n3
My problem is when formula have divide, I should try convert the formula to prevent error divide by zero.
My current idea is convert formula to CASE when it…
Pham X. Bach
- 5,284
- 4
- 28
- 42
0
votes
3 answers
how to extract some part of string from a table in oracle using REGEXP_SUBSTR() (condition excluding preceding part of first space)
lets say we have table as
with table1
as
(
select 'CS issue-result info' col1
from dual
union all
select 'ITP decile info' col1
from dual
union all
select 'DFSD fdb-quentile-info' col1
from dual
union all
select 'EUR transcription info' col1
from…
Anil Tiwari
- 71
- 8
0
votes
3 answers
REGEXP_SUBSTR not able to process only current row
(SELECT LISTAGG(EVENT_DESC, ',') WITHIN GROUP (ORDER BY EVENT_DESC) FROM EVENT_REF WHERE EVENT_ID IN
( SELECT REGEXP_SUBSTR(AFTER_VALUE,'[^,]+', 1, level) FROM DUAL
…
Shalini Raj
- 177
- 2
- 19
0
votes
2 answers
extract values after comma's using regexp
Am using Oracle 19c database
Below is my string value
variable B1 varchar2(60)
exec :B1:='(199,''TEST121''),(156,''TEST''),(1561,''TEST99'')';
I want the output as
| ID | NAME |
| -------- | -------------- |
| 199| TEST121 …
pavankumar2028
- 43
- 4