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
1 answer

Separate house number and addition in oracle SQL

I have a problem with my Oracle SQL string and don't get the correct result. I have a table with Housenumber and addition in one field, i.e. 16f As result I want it in 2 Fields: Housenumber Addition 16 f Housenumber is a Number (1 or…
embi
  • 1
0
votes
3 answers

Oracle SQL - Query to select string between curly Brackets

I'm looking for an query to select string between curly Brackets , I can utilize in Oracle SQL or PL/SQL. Here is an example TABLE1 to work with: ID LIST 1 Depto:={x1,x2} Code:={house} 2 Depto:={y1,x1}…
paolo ricardos
  • 65
  • 4
0
votes
1 answer

Separating Text by Delimiter using regexp_subtr

I am using plsql to separate parts of my text. The text: 'a^b^c^d^e' declare test varchar2(10); begin select 'a^b^c^d^e' into test from dual; dbms_output.put_line('1. '|| regexp_substr(test, '[^^]+', 1, 1)); dbms_output.put_line('2. '||…
Raymond Kelly
  • 23
  • 2
0
votes
1 answer

Grouping in REGEXP_SUBSTR() in MYSQL 8.x

I am trying regexp as I know it but doesn't seem to be working in MYSQL v8.0. I am trying to extract the filename (something.txt) from the some_str variable. It keeps returning NULL. Not sure what I am doing wrong in the pattern. set @some_str =…
Buddy Li
  • 35
  • 5
0
votes
1 answer

regexp_substr in sql to separate numbers from text field

I have a sql that returns comments based on employee feedback. As you can see with the comments below, the formatting can be a bit different. Is there a way that i can extract the numbers out? Examples : W.C. 06.07.2022 change from 7 to 5 wk com…
Jonathon Chau
  • 23
  • 3
0
votes
1 answer

CONNECT BY LEVEL hangs

I'm puzzled why my query hangs. I have a simple regexp statement. The count works. The substr works. But when I add connect by syntax, the query hangs. Query to find first occurrence, returns virtualDomains.GZATPAC_Get_Test_IDs" select…
Aaron
  • 51
  • 6
0
votes
1 answer

Oracle 11g how to store multi values to one column but get ok performance

Hi， My database is Oracle11g. For some reason, I need to store one or several values in column1. In this case I use '||' as a delimiter. So the sql query is trying to answer, e.g. if value 310-G01-000-000-000 in column1? I want a solution, for…
TonyRen
  • 11
  • 4
0
votes
1 answer

How to use REGEXP_SUBSTR for searches between two tables with Snowflake?

I am currently using the following code to be able to do an intelligent text search between two tables, none of the tables have any relationship that joins them. The goal of my SQL is to be able to find the text of table_A that is in table_b,…
Ser
  • 59
  • 1
  • 5
0
votes
1 answer

Separate CLOB Field into Separate Columns in Oracle SQL Based on Characters (regexp_substr a possible solution?)

I have the data in a CLOB field shown below. [1/13/2022-12:43:36-u12178-s320323]Advance Drop[1/14/2022-00:33:15-u0-s0]Drop
I use Oracle DBMS. I want to separate the first date, that is 1/13/2022, into its own column. I also want to separate the…
Nelson
  • 1
  • 1
0
votes
2 answers

MYSQL REGEXP_SUBSTR: get string or ip out of text

I struggle through a complex query and need help with a REGEXP_SUBSTR command being used in the WHERE CLAUSE: In deed this are two questions. given three possible records, I want to get the part between the brackets, but only if they match…
Markus N.
  • 312
  • 2
  • 7
0
votes
0 answers

Use regexp inSQL/PL for no repetitions in digits

I have a table below and need to use a regular expression to display the correct course digits ( 3 digits being the max # of digits) and also max sure no number appears twice. the following code I have created is this: select…
soloGHost7198
  • 23
  • 4
0
votes
1 answer

Updated Question-Oracle case statement to extract value for all occurance associated with a substring in a single line

I want to write a case statement which can extract value for a particular substring from a column named details which has multiple occurrences for [address] is it possible with REGEX along with case? sample Data in the column: [address]:kattengat…
Linnea
  • 65
  • 1
  • 6
0
votes
1 answer

oracle regular expression split but workaround brackets

I have a string like : "Att1:Val1,[Txt1,Txt2:Txt3]:Val2" Using Oracle Sql, I would like to achieve a split into rows & columns as below : lvl substr2 substr3 1 Att1 Val1 2 [Txt1,Txt2:Txt3] Val2 I have tried below code, but can't figure…
Vlad
  • 3
  • 2
0
votes
1 answer

regexp_substr literal max length limit in Oracle

I need to extract values delimited by semicolon in a text. I use regexp_substr which is very powerful but the string has a limit. I have sometimes a text with 10 000 characters so is not possible to use such string in this inbuild function. the text…
Pato
  • 153
  • 6
0
votes
2 answers

How to use regexp_count with regexp_substr to output multiple matches per string in SQL (Redshift)?

I have a table containing a column with strings. I want to extract all pieces of text in each string that come immediately after a certain substring. For this minimum reproducible example, let's assume this substring is abc. So I want all subsequent…
Ricardo Francois
  • 752
  • 7
  • 24
1 2 3
…
23 24