Stack Overflow
Stack Overflow

Questions tagged [regexp-substr]

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.

353 questions
0
votes
5 answers

regexp_substr strip text between first forward slash and second one

/abc/required_string/2/ should return abc with regexp_substr
dreambigcoder
  • 1,859
  • 4
  • 22
  • 32
0
votes
2 answers

regexp_substr to bring back data before a foward slash

I have the following pattern of characters in a dataset. I need to manipulate the data & cross refer it to another table. I'm trying to write a regexp_substr to bring back data before a foward slash starting from the left. for…
Lee Murray
  • 315
  • 1
  • 6
  • 19
0
votes
3 answers

ORA-01722: invalid number - value with two decimals

I'm trying to get the max value from a text field. All but two of the values are numbers with a single decimal. However, two of the values have something like 8.2.10. How can I pull back just the integer value? The values can go higher than 9.n,…
CraigP
  • 1,789
  • 2
  • 12
  • 4
0
votes
2 answers

REGEXP_SUBSTR in Teradata

I am having data in a column like XXX/XXXX/XXXX/XYYUX/YYY. I am trying to extract only the first two digits after the 3rd backslash(/) in the column which is 'XY' in this example. Can you please help? Thanks!
Jude92
  • 167
  • 2
  • 6
  • 20
0
votes
2 answers

Reg_exp in stored procedure needs "into"

I have this statement in an Oracle stored procedure select regexp_substr('hello,world', '[^(,|;|\s|&)]+', 1, level) from dual connect by regexp_substr('hello,world', '[^(,|;|\s|&)]+', 1, level) is not null; However, the compiler complains that I…
Johnny Wu
  • 1,297
  • 15
  • 31
0
votes
1 answer

Issue with regexp substr function in oracle

I have a SELECT statement which contains regexp_substr in it SELECT REGEXP_SUBSTR ('hello, main.proc.standarad_name(ename),main.proc.standarad_val(eno)', '[,](.*)[(]eno[)]', 1, 1, 'i', 1 ) FROM…
Rak kundra
  • 168
  • 11
0
votes
5 answers

Extract data using REGEXP_SUBSTR

Hi there I'm trying to extract from an oracle database part of the text contained within a single field using the REGEXP_SUBSTR function. The text in question is shown in bold text below "BRS14774366". The good news is the pattern of the data i'm…
jason Palmer
  • 43
  • 5
0
votes
2 answers

regexp_substr Finding two letter words

I have a address field in Oracle from where I need o extract the State Code. select '111 BROADWAY ~ ST LOUIS, WA 58585' from dual union select '111 BROADWAY ~ ST LOUIS, WA' from dual union select '111 BROADWAY ~ ST LOUIS,58585 WA' from…
user6650224
  • 11
  • 4
0
votes
1 answer

regexp_substr get last two words from end of the sentence in Oracle SQL

I have a string: ON P6B 0B8. The output I need is: P6B OB8. I can use regexp_substr('ON P6B 0B8','[^ ]+$',1) to get the last word from the end of the sentence. But how would I get the word after the space—the second word from the end? How do I tell…
Ravi Chilakamarthi
  • 15
  • 1
  • 3
0
votes
1 answer

split into rows and columns Oracle

I am creating a function that returns a table type object based on the split of the chain, the query is the following: WITH COLUMNA AS ( SELECT ROWNUM COL_ID, REGEXP_SUBSTR ('A,B,C:D,E,F:','[^:]+',1,LEVEL) COL FROM DUAL CONNECT BY…
sotocast
  • 102
  • 1
  • 9
0
votes
3 answers

REGEXP to capture values delimited by a set of delimiters

My column value looks something like below: [Just an example i created] {BASICINFOxxxFyyy100x} {CONTACTxxx12345yyy20202x} It can contain 0 or more blocks of data... I have created the below query to split the blocks with x as (select …
pOrinG
  • 896
  • 3
  • 13
  • 27
0
votes
2 answers

regexp_substr- extract numbers in multiline text

I have a string in oracle table as below. I need to extract the amount after the text "Monthly Tax Amount(Property Taxes) : ". Each of the item is in new line and amount corresponding to the item is mentioned in the same line next to the…
Sini K
  • 13
  • 4
0
votes
3 answers

Regexp_substr find string not matching a group of characters

I have a string like mystr = 'value1~|~value2~|~ ... valuen". I need it as one column separated on rows like this: value1 value2 ... valuen I'm trying this select regexp_substr(mystr, '[^(~\|~)]', 1 , lvl) from dual, (select level as lvl from dual…
C.B.Luca
  • 1
  • 2
0
votes
1 answer

regexp_substr to get String between a string value and pipe

need regexp_substr for finding a string value between a string and a pipe Example 1 'blah,blah...|text=1234|nmbnxcm' Result 1: 1234 Example 2 'test,test...|text=4321|testing' Result 2 4321
dreambigcoder
  • 1,859
  • 4
  • 22
  • 32
0
votes
2 answers

regular expression find a white-space character and non-white-space character

Help me fix my regular expression:: (REGEXP_SUBSTR(WORD, '#time\S[0-9]{1,2}' )) as reg I want to get the result: '#time 52' '#time20' If I write: (REGEXP_SUBSTR(WORD, '#time\S[0-9]{1,2}' )) as reg result: #time20 If I write:…
Arthur Brown
  • 5
  • 1
  • 3
1 2 3
…
23 24