369 questions
0
votes
3
answers
90
views
Use REGEXP_SUBSTR in Oracle
I would like to extract a string from file name as below.
File name: L8_P0_TOPSIDE12_AA-MPS-K-PCE.JPG
Expected Output: AA-MPS-K-PCE
I am write my code as below butI cannot get what I expected. Need ...
- 17
1
vote
4
answers
105
views
String separation with oracle regexp
Need to separate the following long string using oracle regexp -
Row1 has following value-
'van dam is brother of Prince Charles(12345). Mathew Perker is son of Prince Charles(12345).'
Row2- 'Madam ...
0
votes
1
answer
35
views
REGEXP_SUBSTR assistance
This is probably pretty simple for most, but I'm just not great at regular expressions.
I have a set of strings with this type of format:
SYND_YAHOO_7BEST_RANKADDITIONS_268_2357425
So basically I ...
- 853
2
votes
4
answers
95
views
Split String by delimiter position using oracle SQL 28.9 X 30.6
I have a string which contains a '48.6 X 90.6' at COLUMN DIE_SIZE. I want to split this string into size_x and size_y. I can only find a way to split the die_y
Example Strings:
DIE_DIZE
48.6 X 90.6
77....
- 21
2
votes
1
answer
397
views
Using REGEXP or REGEXP_SUBSTR in an MariaDB query to extract all of the image source links
I'm trying to use REGEXP in an MariaDB 11.0.2 query to extract all of the image source links within the HTML and text of the field post_content that is in each row in wp_posts. I've looked at related ...
- 573
0
votes
0
answers
29
views
How can I extract a digit from a string using regexp_substr in ORACLE [duplicate]
I have a test-string like this: "2 Test123 H: 6 Test23 2 H:2 H :5 H : 5"
I want to get only the digit after exactly "H: ". Not "H:", "H : " or "H :".
...
- 11
0
votes
1
answer
182
views
How to remove special character without knowing the type of special character?
I am using snowflake and I am parsing an email message for a certain field. I found that only 2 records have a whitespace in the front. I have used trim, tried various types of char replace (9,10,13,...
- 17
0
votes
3
answers
82
views
Split string using REGEXP_SUBSTR in Oracle SQL gone wrong
Hello i have 3 strings that i want to split and they are all separated with delimiter
string_1 contains names of fields for example: ID, DUE_DATE
string_2 contains values of fields for example: 80781,...
- 59
0
votes
1
answer
99
views
REGEXP_SUBSTR, REPLACE, and TO_NUMBER
I have two tables like this:
| MOVIE_ID | BUDGET |
|----------+-------------|
| 1904269 | $850,000 |
| 1809508 | NLG 800,000 |
| 1988471 | $40,000 |
| 2119404 | $3,266 |
| ...
- 121
0
votes
1
answer
56
views
Oracle REGEXP_SUBSTR removing substring
From every String I want to remove the substring "withHugo"
I tried
SELECT REGEXP_SUBSTR('STRING-EXAMPLEwithHugo','(.+)withHugo') "REGEXPR_SUBSTR" FROM DUAL;
but it returns "...
0
votes
3
answers
133
views
Extract value between two strings in SQL
I'm trying to extract price for each day from a tag list in SQL Developer.
This is my list (I can use another list format if needed):
monday12monday, tuesday567.12tuesday, friday87friday
I'm looking ...
- 25
0
votes
1
answer
112
views
Filter parts with prefixes of different length using REGEXP_SUBSTR
Basically, I have an input String in PL/SQL that looks like the following:
input varchar2(100) := R985AD5768N2
This string is composed of several information preceded by a certain prefix each and ...
-1
votes
2
answers
1k
views
Extract everything between 2 strings in snowflake using regular expression
I have a json like below stored in col1 of a snowflake table table1 from which I want to extract the dump portion into a column dump in a snowflake table table2. I am trying to use regular expression ...
- 564
0
votes
2
answers
52
views
Oracle REGEXP_REPLACE string delimiter handling not working
I have incoming data which is delimiter by a string token:
Data 1__##__Data Two__##__Third Data__##__4th Data__##__
I would like to split this by the delimiter __##__ to give:
Data 1
Data Two
Third ...
- 4,034
0
votes
2
answers
99
views
BigQuery - Extract substring from column
I have column let's say called "assets" with results looks like this
//bigquery.googleapis.com/projects/ABC/datasets/123
//bigquery.googleapis.com/projects/BlaBla-something/datasets/12345
/...
0
votes
1
answer
68
views
Capture the 4th words using regexp_substr
How can I capture the word after 3rd gaps using regexp?
SELECT REGEXP_SUBSTR(DATA,’\s{3}[A-Z{3}\d+\.\d+’) FROM TBL
Data:
Y091234ABC 100 DEF100.25 DEF200 DEF300 DEF10000
...
- 5
0
votes
2
answers
67
views
Substr instr help needed in sql oracle
i am trying to fetch some text from HTML string from a table. the text are like this and i am trying to get the output
ID,TEXT
1,<font face="Microsoft Sans Serif" size="8" color=...
- 599
0
votes
2
answers
237
views
SQL oracle query to select part of string between two delimiters
I am using REGEXP_SUBSTR to select part of a string. The full string is either of the form
text (more_text (other_text)) or text (more_text (other_text)) (the difference here is the extra space ...
- 11
0
votes
1
answer
203
views
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 &...
- 3
0
votes
1
answer
66
views
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 ...
0
votes
1
answer
431
views
How is the regex for extracting domain from URL constructed [duplicate]
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,'//[^/\\\,=@\...
- 143
1
vote
1
answer
376
views
finding duplicate words throughout the text using regex
I wanted to find all sets of words that are repeated in a text. for example:
string21="we read all sort of books, we read sci-fi books, historical books, advanture books and etc."
Now regex ...
0
votes
1
answer
56
views
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
- 153
1
vote
3
answers
4k
views
REGEXP_SUBSTR in Snowflake, regex that will extract the string after the period (which occurs between 0-2 times)
I'm trying to use the REGEXP_SUBSTR method in Snowflake to extract a string after the period. I have the following possibilities:
ALTER TABLE DATABASE_NAME.SCHEMA_NAME.TABLE_NAME ADD COLUMN ....
...
- 115
0
votes
2
answers
187
views
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) &...
- 31
0
votes
2
answers
11k
views
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 ...
- 19
1
vote
1
answer
926
views
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
...
- 111
1
vote
1
answer
371
views
Specify the number of characters that should match a LIKE REGEX in T-SQL
I've done a ton of Googling on this and can't find the answer. Or, at least, not the answer I am hoping to find. I am attempting to convert a REGEXP_SUBSTR search from Teradata into T-SQL on SQL ...
- 33
0
votes
2
answers
183
views
Returning a specific substring from ORACLE SQL string using REGEXP_SUBSTR
I am having a difficult time trying to return a specific section of string from a field (BSE.NOTES) using REGEXT_SUBSTR. For my query, I have a VARCHAR2 field with a specific text that I would like to ...
0
votes
0
answers
141
views
Using REGEXP_REPLACE in Update MariaDB
Having this field in my database "description" (MariaDB, PHPMyAdmin):
<ul>
<li><a href="https://onedomain.com/name-of-product-18-8433290610079.html">Product 1 <...
- 1
1
vote
2
answers
492
views
Snowflake regexp_substr to fetch folder name as a column from s3 bucket
Can someone help me fetch "Client name" using regexp_substr from below path in Snowflake. The client names here can have spaces or underscores
s3://student-com/student-to-employee/student1/...
- 43
0
votes
2
answers
238
views
Teradata SQL Regular expression - dealing with consecutive delimiter
I am trying to use regexp_substr to break up table data held in one cell to the individual fields.
the data is , delimited.
individual cells can also contain , within quotes
and finally some cells can ...
- 1
-2
votes
1
answer
568
views
Extracting string between two characters in sql oracle database
I need to extract a string that will located between two characters, with always the same pattern
sample string:
A CRN_MOB_H_001 a--> <AVLB>
What is in bold AVLB is what I want to extract, ...
- 9
0
votes
1
answer
639
views
Oracle SQL How to extract a string on the first occurrence between two specific fixed strings of "//"
I have data in an Oracle column in the below format:
Ch99// 4// Sub// 586915 16// jkc 12/12/22
And I need to extract the substring of "4" that will always be between the first two ...
- 3
2
votes
1
answer
697
views
How to extract only numerics from string in PostgreSQL 8.0.2 Amazon Redshift
I only want the numeric part of this string column:
identity
student:1234
student:56
student:789
id:driver_license-111-AZ
id:learner_permit-222-NY
So that the output should be:
wanted
1234
56
789
111
...
- 900
0
votes
1
answer
193
views
Redshift REGEXP_REPLACE options
I have a procedure that is actually making a replace of '#$#' by '', the value that contains '#$#' it could be, like this one: 'AAA#$#DEFAEFGAA', with no defined length.
But what is really needed is ...
- 1
1
vote
3
answers
488
views
How to split string words with regexp_substr in Oracle SQL?
select
regexp_substr('a-b--->d--->e f','[^--->]+',1,1) col1
,regexp_substr('a-b--->d--->e f','[^--->]+',1,2) col2
,regexp_substr('a-b--->d--->e f','[^--->]+',1,3) col3
,...
- 21
0
votes
1
answer
244
views
Elasticsearch7 query match_phrase with IP or Number does not work
I am trying to parse an error message in ES7. The message contains IPs and Numbers.
I tried with regex and with simple search inserting the first part of the IP.
Both are not working.
This my simple ...
- 592
0
votes
1
answer
887
views
SQL WHERE clause to use multiple wildcards
I have to write a query to fetch rows for 100+ diagnosis codes. What can be an optimal SQL query ? Below are the details of what I have:
Table:
Diagnosis_Cd
Description
A00
Cholera
A000
Cholera due to ...
- 81
0
votes
1
answer
89
views
regExp to cover the entire range of hours
I need my regexp to cover every hour range that is below.
07:00 - 15:30
16:00 bis 20:30
7:00-15:00
7.00 Uhr 16.30 Uhr
7 - 16.30 Uhr
7 - 16 Uhr
7:30 - 16 Uhr
7:15 Uhr bis 16:30 Uhr
7-16.15 Uhr
I need ...
- 1
0
votes
2
answers
5k
views
How to Select a substring in Oracle SQL from and up to some specific characters?
i am using oracle sql. i would like to substr starting from characters XY0 and include 2 or 3 more characters until '-' sign in the string
These characters may be anywhere in the string.
Original
...
- 1
-1
votes
1
answer
493
views
parameters in REGEXP_SUBSTR_ALL
I wanted to extract all the "name" values from this:
{"id":"1","name":"simpleword","type":"test"},{"id":"123",&...
- 3,532
0
votes
1
answer
132
views
regex expression to include german characters
I have a string that looks like this:
{"id":"1","name":"simpleword","type":"test"},{"id":"123","name":"...
- 3,532
0
votes
1
answer
341
views
apply regexp_substr to each row separately
I have a table with a column "TAGS". Each row in this column has a bunch of dictionaries separated by commas. It looks like this:
Row 1:
{
"id":"334",
"name&...
- 3,532
0
votes
1
answer
215
views
extract nested values as a list
I have a table with a column "TAGS". Each row in this column has a bunch of dictionaries separated by commas. It looks like this:
{
"id": "334",
"name": ...
- 3,532
0
votes
2
answers
575
views
How to use regexp_substr() to return the numbers after a specific word in a string
I have a table column full of strings like this:
'top-level:volume(1):semifinished(21491628):serial(21441769)'.
I would like to return just the numbers after 'serial' (i.e. '21441769') using ...
- 25
1
vote
1
answer
61
views
Regex to find sub-string inside nested bracket followed by a specific string
I am trying to find all the top level 'except' clauses from the below string.
where (
param1 equals value1
and
param 2 equals valu2
except (
param3 equals value3
where (
param4 equals ...
0
votes
2
answers
188
views
What's the best way to extract 2nd number from a string
I have something like below stored in a table column. I need only 133 extracted from this.
015.133.Governmental Affairs
When I do
select regexp_substr('015.133.Governmental Affairs', '\.*+[[:digit:]]+*...
- 55
1
vote
1
answer
203
views
Oracle Regular Expressions - Replace all occurrence of string matching pattern
Using Oracle regular expression particularly regexp_substr and replace functions, I'm trying to find a way to replace strings that matches the pattern. To explain better, here's a sample data.
<p&...
- 1,743
0
votes
1
answer
254
views
REGEXP_SUBSTR Redshift - how to find the last substring
I have a question regarding REGEXP_SUBSTR in redshift. I have a string, and I want to always return the last substring 'STATUS CHANGE FROM ''something'' TO ''another thing''. There might be multiple ...
- 127