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
1
vote
1 answer
How to replace empty slots with a custom message using REGEXP_SUBSTR in SQL in Oracle
I have an assignment where I have a table of "suppliers" and each supplier comes with a description. Some descriptions provide an email address while the rest do not. I need to display JUST the email provided, and if there isn't one I need to…
user147219
- 355
- 1
- 4
- 13
1
vote
2 answers
How to find multiple strings from one column value using Oracle SQL
Below is the CLOB column value in one of my table. This value contains many images paths.
Kishore
- 11
- 2
1
vote
2 answers
Oracle: Connect by Level & regexp_substr
Hi I have an Oracle query that I did not write and I also do not have access to run in an Oracle environment.
Could someone please explain what this query is doing please?
WITH tableName AS
( SELECT regexp_substr(fieldName,'[^,]+',1,level) as…
Amy
- 591
- 3
- 10
- 23
1
vote
2 answers
Fast splitting string
I have a series of lines that I read from a file (over 2700) of this type:
A = '1; 23245675; -234567; 123456; ...; 0'
A is a string with ; as the delimiter for data.
To split the string I used the strsplit function first, but it was too slow to…
Sara Savio
- 49
- 1
- 7
1
vote
3 answers
Using regexp_substr on tab delimited record with spaces in fields
Using Oracle 12c, how can I use regexp_substr to separate a tab delimited record whose fields may contain spaces? The record has four fields. The third field contains words with spaces.
I'm using this as my reference:Oracle Regex
Here's my…
zundarz
- 1,540
- 3
- 24
- 40
1
vote
3 answers
Using Oracle REGEXP_SUBSTR to extract uppercase data separated by underscores
sample column data:
Failure on table TOLL_USR_TRXN_HISTORY:
Failure on table DOCUMENT_IMAGES:
Error in CREATE_ACC_STATEMENT() [line 16]
I am looking for a way to extract only the uppercase words (table names) separated by underscores. I want the…
Sibusiso Maphumulo
- 13
- 4
1
vote
3 answers
PL/SQL Oracle. Best way to implement an IS_CONTAINED operator
I am newbie so that maybe this question has been made one or two million times, but it is not findable / searchable in the knowledge database.
In Oracle PL/SQL, it is normal to query as follows:
select a,b,c
from table_foo
where c in (select k…
comrlr
- 11
- 2
1
vote
1 answer
Regexp substr starting position in different strings in PL\SQL
I have strings like these:
16.09.D 25435 11141 Joseph Martin Smith 3333 Kairo
Bristol 2223 Anna Franklin
Soul 23333333 Henry Bayes
I want to get just the names from these strings. The fix thing is the names are always after the numbers, but as…
Looz
- 377
- 2
- 14
1
vote
1 answer
regexp mysql group
I try get name of city's from string '{"travelzoo_hotel_name":"Graduate Minneapolis","travelzoo_hotel_id":"223","city":"Minneapolis","country":"USA","sales_manager":"Stephen Conti"}'
I try this regexp:
SELECT…
Вовчик Николаенко
- 13
- 3
1
vote
2 answers
Issue with REGEXP_SUBSTR
I have text in a column like /AB/25MAR92/ and /AB/25MAR1992/. I am trying to extract just 25MAR92 and 25MAR1992 from the column for a date calculation that I have to work on. Can you please help with the REGEXP_SUBSTR function for this…
Jude92
- 167
- 2
- 6
- 20
1
vote
2 answers
Oracle REGEXP_SUBSTR | Fetch string between two delimiters
I have a string Organization, INC..Truck/Equipment Failure |C. I want to fetch the sub-string after organization name (after two '..' characters) and before pipe character. So the output string should be - Truck/Equipment Failure.
Can you please…
Amit Jagtap
- 121
- 1
- 1
- 9
1
vote
2 answers
Extract Specific Set of data from a String in Oracle
I have the string '1_A_B_C_D_E_1_2_3_4_5' and I am trying to extract the data 'A_B_C_D_E'. I am trying to remove the _1_2_3_4_5 & the 1_ portion from the string. Which is essentially the numeric portion in the string. any special characters after…
NottyHead
- 181
- 4
- 18
1
vote
2 answers
Group by substr in Oracle
Below is an example query:
select acct_no, month, sum(amount), substr(charge_type, 1, 3),
case when (charge_type in ('CRE1', 'CRE2')
then 'electronic payment'
else 'cash'
end as 'payment_type'
from …
ComputersAreNeat
- 175
- 1
- 1
- 11
1
vote
3 answers
Reg Exp - insert a special character (~) between the first and the third character and only when an exact match is present
My table has a column MSG_INFO with sample data:
Party is carrying gold in a car which is made of ,gold,
Party is carrying whitegold in a car which made of gold
I need a SQL expression that searches for gold and if there is an exact match then I…
user3442679
- 59
- 1
- 1
- 11
1
vote
1 answer
Oracle Regular Expression to show string a string after a special character
I am using regexp to get all the sub string after a special character ':' appear-
Given String-
Reason for creating the box: Order done by me (100005 - Error Format:
Value ABC000001606 of EmpId is not valid)
Expected Output-
Order done by me…
Praveen Verma
- 41
- 1
- 10