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
3 answers
REGEXP_SUBSTR to return first and last segment
I have a dataset which may store an account number in several different variations. It may contain hyphens or spaces as segment separators, or it may be fully concatenated. My desired output is the first three and last 5 alphanumeric characters. …
Paul Young
- 37
- 7
0
votes
3 answers
oracle SQL: improve connect by clause
I have a table: Table_1 looks like the following:
id | column1
-----------------
10 | abc, kdm
20 | xyz, lop, nkk
What I want is to convert the table looks like the following:
id | column1
-----------------
10 | abc
10 | …
KTB
- 1,499
- 6
- 27
- 43
0
votes
1 answer
Convert several commas string into rows
I've this table (Holidays):
CCAA FREEDAYS
AND 01/01,01/03
MAD 01/01,03/03
EUS 01/01,31/12
....
and I want to obtain this other table:
CCAA FREEDAY
AND 01/01
AND 01/03
MAD 01/01
MAD 03/03
EUS …
Madmartigan
- 453
- 1
- 5
- 14
0
votes
2 answers
using Oracle SQL - regexp_substr to split a record
I need to split the record for column CMD.NUM_MAI which may contain ',' or ';'.
I did this but it gave me an error:
SELECT REGEXP_SUBSTR (expression.num_mai,
'[^;|,]+',
1,
LEVEL)
…
ITE
- 67
- 7
0
votes
2 answers
REGEXP_SUBSTR to extract fixed length string starting from a digit
Table A
ID ID_Descr
1 'DUP 8002061286'
2 'DUP 8002082667 '
3 ' 8002082669 DUP'
I would like to extract the string from the ID_Descr field with the following conditions:
String always starts with 8
String length is always 10…
Heisenberg
- 267
- 3
- 6
- 15
0
votes
1 answer
How to trim the last matching string using regexp_substr
I am trying to use regexp_substr to retrieve string within [!< & >!]. I have below statement but it returned ' To test with regexp_substr.>!]'. How can I exclude >!] and return just the string?
select regexp_substr('[!< To test with…
user4479892
- 1
- 1
0
votes
1 answer
REGEXP_SUBSTR get second match
I have this String :
"Y::_~XXXXXX XXXXXX~MIGRATION~_~_~_~_~0~JOD50.000~USD257.43::JOD"
I wanted to get the list of currencies via Regex.
So far, I can only get the first match using this query:
SELECT
REGEXP_SUBSTR('Y::_~XXXXXX…
Anonimus
- 3
- 1
- 4
0
votes
2 answers
Oracle listagg regexp_substr for code extraction and concatination
I am working with PL/SQL v10 on Oracle 11g database.
I have codes that are being stored in description column of question table that I need to extract.
To do that I was working on producing regex which works fine in 101regex but fails in oracle,
I…
Matas Vaitkevicius
- 58,075
- 31
- 238
- 265
-1
votes
1 answer
parameters in REGEXP_SUBSTR_ALL
I wanted to extract all the "name" values from this:
{"id":"1","name":"simpleword","type":"test"},{"id":"123","name":"f23ÜÜ","type":"prod"}
I was presented with the following solution;
regexp_substr_all(s, 'name":"([^"]*)"', 1, 1, '', 1)
and now I…
x89
- 2,798
- 5
- 46
- 110
-1
votes
2 answers
MYSQL SELECT QUERY, finding a letter+number word in a string
So the company I work for has its employees update the CRM with comments, which then are stored in the database. And sometime they put in usernames and passwords into those comments.
We are trying to remove all username/passwords from any comment in…
Eric Tribble
- 1
- 2
-1
votes
1 answer
Oracle SQL Regexp capture only the number from the string
I am trying to extract the number from the row.
i am trying this in Oracle 11G
CREATE TABLE test1
(
COL_NAME VARCHAR2(100)
);
insert into test1 values ('DWH_SCHEMA_BI.AB_25_BC_ORDER_STATUS');
select COL_NAME, REGEXP_REPLACE(COL_NAME,…
Data2explore
- 452
- 6
- 16
-1
votes
2 answers
Select a word from an Oracle query string using regular expression
I have to find a table name from a query dynamically. The table name in the query may or may not be preceded by the schema name. But the table name will always be followed by a "@dblink" string.
The query pattern is like : 'select c1, c2 from…
Jay
- 47
- 11
-1
votes
4 answers
How to select a string between the characters
If my columns(attribute9) contains the field as Pika~Chu~(040)-121-12334~pika78@pika.com the how can I extract the values like
contact = Pika Chu
phone_nbr = (040)-121-12334
email = pika78@pika.com
I had written the code…
satya vani
- 1
- 3
-1
votes
1 answer
Use sed command with substring to replace
I am looking to use the sed command to replace a substring value.
I have a file like this
UNIT56712423MP000000R0990
The records in this file are always of the same length.
I need to check if the 21st character is R, then replace characters 13-14…
user8855619
- 11
- 3
-1
votes
1 answer
Oracle multiple columns with comma delimited items to rows. Where first elements in all columns should go to fist row , second to second etc
I have comma delimited data in multiple columns.
Like this:
id c1 c2
100 A,B 1,2
101 C,D 3
102 E 4,5
What I need to get is:
id c1 c2
100 A 1
100 B 2
101 C 3
101 D …
woiix
- 21
- 1
- 4