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
1 answer
Find all the substrings in a string that only have a single bracket on both sides
I'm trying to find all the substrings in a string that only have a single bracket on both sides.
Example: '(pop((hello world))(goodbye(now))(hi)jump)'
I would like to get this list: ['(hi)', '(pop((hello world))(goodbye(now))(hi)jump)']
because they…
Red
- 26,798
- 7
- 36
- 58
0
votes
0 answers
regex validating if string ends with specific set of words
In a javascript code want to match a string with regex to return boolean (if string meets the requirement).
The string should end with yes, no, or maybe non-case-sensitive and can be wrapped with (or only at one side) a double-quote. so any string…
Amir-Mousavi
- 4,273
- 12
- 70
- 123
0
votes
0 answers
Correctly use the SQL Function REGEXP_SUBSTR in Informatica
--Specific Informatica PowerCenter Qs--
I have incoming data field like this and need to extract the substrings from either side of the hyphens and store them in individual fields of the target table. I am getting the correct results from the…
Orion997
- 3
- 1
- 2
0
votes
2 answers
Oracle SQL regexp_substr to find the numbers in a string
I want use regexp_substr to return the number after the characters from a string value
eg.
String Value return
100AUS50 50
100AUS100 100
100AUS500 500
Jan-20 20
…
user15676
- 123
- 2
- 10
0
votes
1 answer
Tag key & value using Teradata Regular Expression
I have a TERADATA dataset that resembles the below :
'Project: Hercules IssueType: Improvement Components: core AffectsVersions: 2.4.1 Priority: Minor Time: 15:25:23 04/06/2020'
I want to extract tag value from the above based on the key.
Ex:
with…
pats4u
- 177
- 6
- 18
0
votes
2 answers
Extract a number between 2 strings in Teradata
I need help extracting 49 from this string: "7 DAYS LATE 49 UNDERUSE" in Teradata, I keep on messing up with STRTOK or regexp_substr. Thanks
0
votes
0 answers
extracting strings and merging using regex in oracle 12c
I Have a column value like :
{"extarctrule":[{"includescene":"includewhen","columnid":"product","columnoperator":"in","columnvalue":"cat dog"}],"includerule(text)":" {{This Rule applies When}} {{Product}} {{in}} cat…
abhilash
- 7
- 3
0
votes
3 answers
regex expression where it can extract the number positioned at the semi end in the string
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=94771b6589b01526ad0cf6e5c4d01945
I need help in extracting the number substring from a file name
currently for file format - 'monkey_eats_mango_everyday_202002.txt'
we are doing like this
select…
amarender reddy jali
- 29
- 7
0
votes
1 answer
Oracle ORA-00907: missing right parenthesis for REGEXP_SUBSTR
I am writing below stored proc and getting missing right parenthesis error.
It worked when I tried just the query but it throws the error when I run a complete stored procedure script in oracle 12c.
There error is for line where I have used…
Deepika
- 1
- 1
0
votes
1 answer
Having difficulties in understanding Regexp in TCL Language
I am new to TCL language. I am having difficulties to catch data in myFile.txt.
MyFile.txt
set obj "{Hello}"
set obj "{Bye}"
set obj "{Nice}"
set obj "{Yoh}"
I want to catch words inside the curly bracket as shown below.
Hello, Bye, Nice, Yoh
How…
Vincci
- 29
- 3
0
votes
3 answers
Oracle SQL Select Split "City, State" to two columns (w/ some instances of just state and no comma) and Trim whitespaces
I've seen this solution for data that always has commas but can't seem to get this to work when there are no commas.
Data is coming from a "location" field that looks like this... (where some data is "city, state" and other rows are "state" with no…
TimK
- 115
- 2
- 2
- 12
0
votes
2 answers
Oracle remove Name/Surname/Telephone and Address
I have an Oracle table which contains user comment. I would like to mask all words like Name/Surname/Telephone and Address
Is there a simple way to do this in Oracle?
Example :
"Hello, i'm Alex DURAND, my phone number is 0685987525"
should be…
Flatbeat
- 123
- 1
- 3
- 13
0
votes
3 answers
REGEXP_SUBSTR SPLIT Function
i want to split this into 2019/GA/0000104
select REGEXP_SUBSTR('2019/0000015,2019/GA/0000104,2cdb376e-2966-4f24-9063-f4c6f31a6f35', '[^,]+')
from dual;
Output = 2019/GA/0000104
can u guys help?
dyland
- 1
0
votes
1 answer
Getting text from multiple brackets
For Oracle SQL
i have the below tag format: {ABCDE,12345}{47,CE}
SELECT
[column a]
[column b]
[column c]
FROM
[table name]
WHERE
[*content of column d*] IN {ABCDE,12345}
[*content of column e*] IN {47,CE}
So i need…
Andrei Calin
- 13
- 5
0
votes
2 answers
Extract Date Along with Am or pm in oracle
I want to get the time pattern along with AM or PM from the given string Aaaaa_gggg_ne_A030_66788_Abcd_Oct_24_0329PM.csv
I tried the following:
Select regexp_substr(filename,'\d{4}',1,3)
From
(Select 'Aaaaa_gggg_ne_A030_66788_Abcd_Oct_24_0329PM.csv'…
user11905860
- 3
- 1