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
2 answers
Returning a specific substring from ORACLE SQL string using REGEXP_SUBSTR
I am having a difficult time trying to return a specific section of string from a field (BSE.NOTES) using REGEXT_SUBSTR. For my query, I have a VARCHAR2 field with a specific text that I would like to return:
Hospital Dept Name …
dwaynesworld0213
- 15
- 4
0
votes
0 answers
0
votes
2 answers
Teradata SQL Regular expression - dealing with consecutive delimiter
I am trying to use regexp_substr to break up table data held in one cell to the individual fields.
the data is , delimited.
individual cells can also contain , within quotes
and finally some cells can be unpopulated
My sample logic is working for…
WJF
- 1
0
votes
1 answer
Oracle SQL How to extract a string on the first occurrence between two specific fixed strings of "//"
I have data in an Oracle column in the below format:
Ch99// 4// Sub// 586915 16// jkc 12/12/22
And I need to extract the substring of "4" that will always be between the first two occurrences of "//".
So the output needed is:
4
I'm currently using…
HoulB
- 3
- 1
0
votes
1 answer
Redshift REGEXP_REPLACE options
I have a procedure that is actually making a replace of '#$#' by '', the value that contains '#$#' it could be, like this one: 'AAA#$#DEFAEFGAA', with no defined length.
But what is really needed is to take only the characters after #$#,…
JIT
- 1
- 1
0
votes
1 answer
Elasticsearch7 query match_phrase with IP or Number does not work
I am trying to parse an error message in ES7. The message contains IPs and Numbers.
I tried with regex and with simple search inserting the first part of the IP.
Both are not working.
This my simple match_phrase query. The query works fine until…
Furin
- 532
- 10
- 31
0
votes
1 answer
SQL WHERE clause to use multiple wildcards
I have to write a query to fetch rows for 100+ diagnosis codes. What can be an optimal SQL query ? Below are the details of what I have:
Table:
Diagnosis_Cd
Description
A00
Cholera
A000
Cholera due to Vibrio cholerae 01, biovar…
Geeths
- 81
- 7
0
votes
1 answer
regExp to cover the entire range of hours
I need my regexp to cover every hour range that is below.
07:00 - 15:30
16:00 bis 20:30
7:00-15:00
7.00 Uhr 16.30 Uhr
7 - 16.30 Uhr
7 - 16 Uhr
7:30 - 16 Uhr
7:15 Uhr bis 16:30 Uhr
7-16.15 Uhr
I need regExp to cover the entire range of hours, each…
Mtszbrtw
- 1
- 1
0
votes
1 answer
How to Select a substring in Oracle SQL from and up to some specific characters?
i am using oracle sql. i would like to substr starting from characters XY0 and include 2 or 3 more characters until '-' sign in the string
These characters may be anywhere in the string.
Original
column_value
1st Row - Error due to XY0066-…
Ann Mir
- 1
0
votes
1 answer
regex expression to include german characters
I have a string that looks like this:
{"id":"1","name":"simpleword","type":"test"},{"id":"123","name":"f23ÜÜ","type":"prod"}
I want to do a REGEXP_SUBSTR_ALL such that I can extract all "name" values into a list.
Something like this allows me to…
x89
- 2,798
- 5
- 46
- 110
0
votes
1 answer
apply regexp_substr to each row separately
I have a table with a column "TAGS". Each row in this column has a bunch of dictionaries separated by commas. It looks like this:
Row 1:
{
"id":"334",
"name":"A"
},
{
"id":"8999",
"name":"B"
}
Row 2:
{
"id":"334",
"name":"C"
},
{
…
x89
- 2,798
- 5
- 46
- 110
0
votes
1 answer
extract nested values as a list
I have a table with a column "TAGS". Each row in this column has a bunch of dictionaries separated by commas. It looks like this:
{
"id": "334",
"name": "A",
"synonyms": "tul",
"path": [
"179",
"1689",
]
},
{
…
x89
- 2,798
- 5
- 46
- 110
0
votes
2 answers
How to use regexp_substr() to return the numbers after a specific word in a string
I have a table column full of strings like this:
'top-level:volume(1):semifinished(21491628):serial(21441769)'.
I would like to return just the numbers after 'serial' (i.e. '21441769') using regex_substr().
select…
Drake
- 25
- 1
- 4
0
votes
2 answers
What's the best way to extract 2nd number from a string
I have something like below stored in a table column. I need only 133 extracted from this.
015.133.Governmental Affairs
When I do
select regexp_substr('015.133.Governmental Affairs', '\.*+[[:digit:]]+*',1,2) from dual;
The result is .133
If I…
Dito
- 33
- 3
0
votes
1 answer
REGEXP_SUBSTR Redshift - how to find the last substring
I have a question regarding REGEXP_SUBSTR in redshift. I have a string, and I want to always return the last substring 'STATUS CHANGE FROM ''something'' TO ''another thing''. There might be multiple substring in the string. I only need to return the…
Xixi
- 117
- 6