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
1
vote
2 answers

Trying to extract pattern out of string

I have a string like set arr_set(variable.abc) {12,13} set arr_set(variable.def) {15,16} set arr_set(dont_care) {0,0} where arr_set is an array set, and variable_abc is an element of it. I have all this information stored in a file. What I want…
user3491702
  • 95
  • 8
1
vote
2 answers

Use REGEXP_SUBSTR in DB2

I want to use something like REGEXP_SUBSTR in DB2 (version 10.5). There is an example of what I tried: SELECT REGEXP_SUBSTR('hello to you', '.o') FROM sysibm.sysdummy1 I got this error : [Error Code: -420, SQL State: 22018] 09:23:12 [SELECT - 0…
Goldray
  • 61
  • 1
  • 13
1
vote
2 answers

Having issue with back reference in TCL

I have the following code: set a "10.20.30.40" regsub -all {.([0-9]+).([0-9]+).} $a {\2 \1} b I am trying to grep 2nd and 3rd octet of the IP address. Expected output: 20 30 Actual output: 20 04 0 What is my mistake here?
Bharathi
  • 63
  • 7
1
vote
1 answer

splitting comma separated list into a temp table

I want to convert the following IN query into inner join query : select country, name, rank from table person_details where country in ('india','USA','australia') and name in ('tom', 'jill', 'jack') and rank in ('first', 'third',…
user2696466
  • 650
  • 1
  • 14
  • 33
1
vote
2 answers

Splitting comma separated values in Oracle

I have column in my database where the values are coming like the following: 3862,3654,3828 In dummy column any no. of comma separated values can come. I tried with following query but it is creating duplicate results. select…
Mudit Saklani
  • 752
  • 4
  • 10
1
vote
1 answer

regex_substr: regular expression to extract the comma separated string literals

The following regular expression separates the comma separated plain values (SELECT regexp_substr(:pCsv,'[^,]+', 1, level) FROM DUAL CONNECT BY regexp_substr(:pCsv, '[^,]+', 1, level) IS NOT NULL); Example: 300100033146068, 300100033146071 ,…
Peddi
  • 92
  • 1
  • 9
1
vote
2 answers

Oracle REGEXP_SUBSTR column names for PIVOT

I am working with PL/SQL Developer v10 on Oracle 11g database. In our system we have a question and answer table that I need to 'flatten' for each customer per question answered. One set of questions had specific code (PIFQ) in description of…
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
1
vote
2 answers

Extracting strings using Oracle REGEXP_SUBSTR

I am using REGEXP_SUBSTR in Oracle 11g and I am having difficulty trying to extract the following strings. My query is: SELECT regexp_substr('CN=aTIGERAdmin-Admin, CN=D0902498, CN=ea90045052, CN=aTIGERCall-Admin,', '[^CN=]*\,', 1, rownum) line FROM…
tonyf
  • 34,479
  • 49
  • 157
  • 246
0
votes
1 answer

regexp_substr in Oracle and Mysql

I executed the same statement in MySQL and Oracle, but they returned different results. Regular expression "/?" means to return zero or more "/", but why would MySQL return me a "products", is the MySQL implementation wrong? SELECT …
hpd
  • 3
0
votes
1 answer

oracle - replace value in string by excluding case when value is between parentheses

i would like to do an automatic replace with an oracle query in multiple strings available in an oracle table, by changing fields separator ','between first occurrence of 'select' and first occurrence of 'from' without having impact in case is in…
Mathew Linton
  • 33
  • 1
  • 2
  • 8
0
votes
1 answer

How is the regex for extracting domain from URL constructed

I saw an SO answer here which has the SQL to extract domain from a URL in Redshift. I am very new to Regex. Is it possible to understand the answer step by step? REPLACE(REGEXP_SUBSTR(url,'//[^/\\\,=@\\+]+\\.[^/:;,\\\\\(\\)]+'),'//','') All I have…
ab_padfoot
  • 63
  • 1
  • 10
0
votes
1 answer

Regexp instruction in SQL

After applying function GET_JSON_OBJECT(features, '$.field') I get the values: {10:3,300:286} {300:86} {300:3,50:1} How to get the value after 300 then? Needed result is 286 86 3
bluekit46
  • 143
  • 5
0
votes
2 answers

REGEXP_SUBSTR with pipe delimiter giving unexpected results

For this data in a packed field: ONE|TWO|THREE|FOUR|FIVE|SIX|SEVEN|EIGHT|NINE|TEN|ELEVENTHEBAND|TWELVE I have this regexp: REGEXP_SUBSTR(the_packed_field_column , '((|)([[:alpha:] ]*)(|))' , 1 , 1) <--gives the expected result of TWO but this…
mojave
  • 21
  • 4
0
votes
2 answers

REGEXP_SUBSTR syntax not making sense... Examples within:

I am finding out very quickly that the REGEXP_SUBSTR help online is 100% not resonating with me (Oracle SQL). I believe I have a relatively simple use case, but I'm having a hard time tracking how one REG_SUBSTR argument I'm using is giving me…
bmax
  • 19
  • 2
0
votes
1 answer

Sybase regexp, substr, instr function to split the string

I have the below data in a table. row_num desc 1 First Name/Last Name - Middle Name 2 FirstName/LastName - MiddleName 3 FirstName/LastName I am looking for the desired output as below row_num desc_1 desc_2 desc_3 1 First…
Bruce
  • 109
  • 8
1 2 3
…
23 24