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
Impala equivalent to regexp_substr
I have a Oracle query which needs to be converted to Impala. I know that Impala has regexp_extract to return the string based on the regular expression that I provide. What my concern is if there is more that one occurance of the same string how do…
Sifu
- 55
- 6
1
vote
2 answers
teradata sql - regexp_substr to split on ' - '
I am somewhat new to Teradata. I am more familiar with Presto SQL, where split_part is available.
I'm looking to split a string on a space, hyphen, space (' - ').
Example: 'Wal-Mart - Target - Best Buy - K-Mart - Staples'
I'm used to using…
acvbasql
- 109
- 1
- 2
- 15
1
vote
2 answers
Extract string after first '/' using snowflake query
I have an input table in snowflake with column contains data pattern as follows
city, state/LOCATION/designation
city state/LOCATION/designation
city, state/LOCATION
Want to extract only location and store in another column, can you help me…
Kavya shree
- 312
- 1
- 7
- 24
1
vote
2 answers
Oracle REGEXP_SUBSTR will not match the dot character
I'm trying to extract information from strings like:
FOO-BAR-AUDIT-DATABASE.NUPKG
FOO.BAR.DATABASE-2.0.0.NUPKG
to info like:
'FOO.BAR.DATABASE' '2.0.0'
| |
module_name version
Currently I'm not able to parse correctly…
oey
- 87
- 6
1
vote
1 answer
how can I extract the key word following by a word and preceding by a comma using REGEXP_SUBSTR
my string is like this
'{ "city": "(not set)", "cityId": "9108665", "continent": "Americas", "country": "United States", "latitude": "0.0000", "longitude": "0.0000", "metro": "(not set)", "networkDomain": "(not set)", "networkLocation": "(not set)",…
sharonwu827
- 11
- 1
1
vote
1 answer
REGEXP_SUBSTR - How to return value between ' '?
I need to return values from ' ' from below string (eg. for psOrderByClause I need value xxx_seqno). Additionally I've also need to return TRUE/FALSE values for some variables - there is no single quotes (eg. pbUseDisplayName I need TRUE).
I doesn't…
MichalAndrzej
- 77
- 1
- 11
1
vote
1 answer
R Korean regexp
I'm currently trying to remove a Korean letter where nchar is only equal to 1 in R.
For example,
have = "안녕 난 철수야"
want = "안녕 철수야"
I've found the Eng version of what it is that I'm trying to achieve.
how to remove words of specific length in a…
Brian
- 161
- 11
1
vote
1 answer
How to extract text before several specified alphanumeric whole words from string in plsql
How to remove all characters after specific alphanumeric value from string
for example "covid19 1st case" should be "covid19" if we remove string after 1st;
in case of "covid19 2d case" it should be "covid19" if we remove string after 2d
I am trying…
Chaman Shukla
- 169
- 1
- 5
- 16
1
vote
1 answer
Regex Extract Google Data Studio: Need to slice one field delimited with pipes into separate fields
I have a field in which values look like:
Field
pos1-123|pos2 xyx123|pos3-abc|pos4x350
I want to slice the field into four different calculated fields using REGEXP_EXTRACT that look like:
Calculated Field 1 : pos1-123,
Calculated Field…
Leo_Taco
- 45
- 10
1
vote
3 answers
REGEXP_SUBSTR - how to "avoid" bracket in string
I have below string in db Blanket By-Laws Coverage (Building Only) - Form:G00150 and I need to return only "G00150" value.
I'm using this REGEXP_SUBSTR (pqb.description is above string)
SELECT MAX(REGEXP_SUBSTR(pqb.description,'(Blanket By-Laws…
MichalAndrzej
- 77
- 1
- 11
1
vote
1 answer
SQL extracting date substring using regexp_subtr
I've got a field named signal_notes containing strings like the follow (this would be a single value of signal_notes):
"{ ^search_date^: ^2021-01-05^,
^filing_date^: ^^,
^expiry_date^: ^^,
^other_liens^: ^^,
^who_1st_positon^: ^^,…
Caguama Rodrigez
- 13
- 2
1
vote
3 answers
Regexp_Substr to filter rows in SQL
For the below table structure and data, I am trying to get rows that contain at least 1 date within the last 15 months for a column having string groups separated by a pattern.
CREATE TABLE TEMP(nbr NUMBER, dt VARCHAR2(4000));
INSERT INTO TEMP…
halfwind22
- 329
- 4
- 18
1
vote
1 answer
Insert a line on each findings from given file using ansible playbook
I want to add the line "*{My-Dept$2}" in file.yaml after *{My-Dept$1} this on every finding, I was able to find a line that starts with *{My-Dept$1} and inserted only one time, and ended with this trouble.
I have this in Multiple lines in the file…
ravinayag
- 33
- 4
1
vote
1 answer
Use of REGEXP_SUBSTR to get date values from string
I'm looking for the REGEXP_SUBSTR code that gets dates like format '06-11-2014 - 05-12-2014' or format '01/11/2019 - 30/11/2019' from a string. The first date being the startdate and the second date being the enddate. It would be extremely helpful…
vvanasperen
- 53
- 8
1
vote
2 answers
Oracle REGEXP_SUBSTR to ignore the first ocurrence of a character but include the 2nd occurence
I have a string that has this format "number - name" I'm using REGEXP_SUBSTR to split it in two separate columns one for name and one for number.
SELECT
REGEXP_SUBSTR('123 - ABC','[^-]+',1,1) AS NUM,
REGEXP_SUBSTR('123 - ABC','[^-]+',1,2) AS…
Karla P
- 13
- 2