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
Oracle SQL statement to select string between '\' and '#'
I have a table in an Oracle 11g database with a column named REPORT. I'm trying to write a select statement that would display the required results below. I need to select the character string between the "\" and the following occurrence of "#". I…
Jayce Glen
- 49
- 4
1
vote
1 answer
REGEXP SUBSTR where delimiter is a combination of 2 or more characters
I want to search a string using a delimiter which is a combination of 2 or more characters.
Please find the query i tried below:
select REGEXP_SUBSTR('123$@45$6$@789','[^$@]+',1,2) from dual
Required Output:
45$6
Output:
45
I understand it is…
pOrinG
- 896
- 3
- 13
- 27
1
vote
1 answer
SQL - receiving multiple patterns from a string
Lets say I have a 2 column table, in 1st col. is an account number and in the 2nd personal client ID BUT in a long string. In that string may be few different clients IDs and each begins with "ID"
|account_no | note
|12345 |…
alektre
- 11
- 1
1
vote
1 answer
Oracle SQL: Return string from blob between two strings (probably using REGEXP_SUBSTR)
I am trying to export data from a blob to use in a report, however I am having trouble using REGEXP_SUBSTR when trying to read from it. Converting it to a varchar is helping, however getting the data point I need is quite hard as I am finding!
Here…
user8523598
- 13
- 3
1
vote
3 answers
How to apply regular expression on the below given string
i have a string 'MCDONALD_YYYYMMDD.TXT' i need to use regular expressions and append the '**' after the letter 'D' in the string given . (i.e In the string at postion 9 i need to append '*' based on a column value 'star_len'
if the star_len = 2…
Rak kundra
- 168
- 11
1
vote
2 answers
How Can I Extract String in Oracle
I would like to extract following string in Oracle. How can I do that?
Original String: 011113584378(+) CARD, STAFF
Expected String: STAFF CARD
Hakan
- 141
- 3
- 16
1
vote
3 answers
Substring using Oracle SQL regex
I've created a regex which would capture the string I need.
When I am testing regex on websites such as rubular.com then everything works, however when I put the same regex into REGEXP_SUBSTR function then it doesn't work.
Here are 2 SQL examples…
Alexander Gusev
- 295
- 1
- 9
1
vote
2 answers
Postgresql regexp_substring
I have a text as
Revision:3336179e1ebaa646cf281b7fb9ff36d1b23ce710$ modified $RevDate:10-04-2017 11:43:47$ by $Author:admin
Could you help me out with writing sql query that would get the first 6 chars of revision (333617) and date of RevDate…
Vad Boro
- 77
- 10
1
vote
2 answers
Regular Expressions in Oracle
I am trying to extract units of measure from strings in an Oracle database that houses a pharmaceutical catalog. I have been using regexp_substr to get the concentration of a medication from a string
ie.
Name col in schema:
CYCLOSPORINE 100MG 30…
John Flicker
- 13
- 4
1
vote
4 answers
REGEXP_SUBSTR with negative position
I have a query to treat comma as delimiter in a string. I want to substr the last occurrence.
example: string: a, b, c, d
string: e, f, g, h, i, j
select regexp_substr(string, '[^,]+', -1, 1)
from dual;
should return: d and j
but the…
dozel
- 127
- 1
- 3
- 9
1
vote
3 answers
Oracle regexp without white space
I need to replace all places without white space beatween 2 words, where first of them terminated with point, with this two words with white space beatween them.
For example, I have string like 'num.some' and I need 'num. some'
But if I have 'num.…
user6631556
1
vote
2 answers
Oracle regexp_substr - Find and extract all occurances in a column
I'm working with an Oracle DB and I'm trying to find and extract ALL occurrences in a string matching a specific pattern...
It's supposed to be 3 letters, 3 numbers and then maybe a letter or not
I tried this:
SELECT REGEXP_SUBSTR(my_column,…
javaBeginner
- 83
- 2
- 8
1
vote
2 answers
How to split a CLOB object using , and : delimiter in Oracle into multiple records
I have a CLOB object sample as shown below. I want to first split this by using delimiter "," and save it in a temporary table for later…
Usha
- 194
- 1
- 4
- 17
1
vote
2 answers
Oracle REGEXP_SUBSTR Parse Dollar Amounts
I'm trying to parse a dollar amount from a string.
Example strings:
*SOC 1369.00 - NCS 1239.46 = PT LIAB 129.54
*SOC 1369.00 - NCS 1239.46 = PT LIAB 140
*SOC = 1178.00
*SOC 1622.00 - NCS 209.74 = PT LIAB 1412.26 RECIPIENT AGE
*LINE #1 SOC 0.00 -…
boognish
- 50
- 8
1
vote
0 answers
mysql i want to use connect by level oracle concept
SELECT COUNT(*) num
FROM (
SELECT REGEXP_SUBSTR(PWD, '[^,]+', 1, LEVEL) AS USERPWD
FROM (
SELECT PASSWORD || ',' || LPASS1 || ',' || LPASS2 || ',' ||
LPASS3 || ',' ||…
christopher alex
- 11
- 1
- 3