Function in Oracle, Mysql, Hive, Teradata, DB2, Snowflake, Trino, Vertica, PostgreSQL, Redshift and other databases that extends the functionality of the REPLACE function by allowing searching a string for a regular expression pattern. See also REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_LIKE and REGEXP_COUNT for other functions extended to use regular expressions.
Questions tagged [regexp-replace]
1082 questions
4
votes
2 answers
how to match bigger word first, from multiple words separated by OR operator in RegExp ? using java script
I need a regex to first match a numbers with 'px' after it, and then match remaining numbers having 'p' after it.
I want to replace parts of string, that are number or a number having 'p' or 'px' after it by '*' character.
a regexp i tried…
apine
- 45
- 5
4
votes
2 answers
Dangling metacharacter * sparksql
Below regex works in Hive but not in Spark.
It throws an error dangling metacharacter * at index 3:
select regexp_extract('a|b||c','^(\\|*(?:(?!\\|\\|\\w(?!\\|\\|)).)*)');
I also tried escaping * with \\* but still it throws dangling metacharacter…
Priya
- 75
- 5
4
votes
2 answers
BigQuery REGEX get text between 2 characters
From this string :
gs://analytics_models_from_g_rdd_ga/9g653798d-7e13-4238-55443-6db72ace9911$$$$events_today.csv
I'm trying to extract this text :
9g653798d-7e13-4238-55443-6db72ace9911
and in separate REGEX function events_today
The rolls for…
idan
- 1,508
- 5
- 29
- 60
4
votes
1 answer
Hive query to replace only the first occurrence of a substring
I need to replace the first occurrence of a substring from the given string.
Eg If the string is "My name is Adam" and I want to replace the first "a" with "@".
So my desired output is "My n@me is Adam".
In MySQL, there is a function regexp_replace…
idpd15
- 448
- 2
- 5
- 22
4
votes
1 answer
Regex Replace function: in cases of no match, $1 returns full line instead of null
Test link: regexr.com/42d9c
This has been driving me crazy.
I want to extract barcodes in the lines below:
Ceres Juice Apricot 12 x 1lt unit: 6001240102022
Ceres Juice Cranberry& Kiwi Juice 12 x 1lt... unit:
Ceres Juice Guava 12 x 1lt..…
Richard Woolf
- 559
- 2
- 6
- 19
4
votes
2 answers
Consecutive Pattern replacing is not happening with REGEXP_REPLACE
I have a string as below
Welcome to the world of the Hackers
I am trying to replace the occurrences of listed strings i.e. of,to,the in between the entire string using below query, but it's not working properly if the patterns are consecutive, it…
Aspirant
- 2,238
- 9
- 31
- 43
4
votes
1 answer
Hive - Use of replace or regexp_replace function for multiple replace conditions
I am using Hive and trying to clean up data that may have multiple unwanted data. Would like to know if I can use a single regexp_replace or translate function for multiple conditions.
For Eg. If the string is 2000 Helen St - DO NOT USE
I need to…
Suraj
- 575
- 1
- 9
- 23
4
votes
2 answers
regexp_replace for oracle to replace https not working
anyone know why does RegExp works on simulators but not really in oracle?
should replace // with / except on https://
SELECT regexp_replace (url_link,'(?
aymanzone
- 135
- 13
4
votes
4 answers
Error: String contains an untranslatable character - TERADATA (for REGEXP_REPLACE operation)
I need to clean one character column and for that I am using REGEXP_REPLACE function in Teradata 14.
The same piece of code worked for some other data source (having the same LATIN encoding).
The data definition using show table has given me below…
Piyush Upadhyay
- 41
- 1
- 1
- 3
3
votes
2 answers
Find and Replace Text with RegEx in Notepad++
Does anyone have an idea how I can achieve the following with Notepad++.
For example, I have the following text:
{ 1000000003;1;Column; ;
DataSource=Search Name }
{ 10000004;1;Column; ;
DataSource=Name 2 }
Is it…
user3644868
- 127
- 1
- 1
- 6
3
votes
0 answers
Removing HTML tags from Postgres text/xml, with exceptions?
I am working with a frontend that utilizes a wysiwyg editor, and we want to be able to support specific html tags in the string input (i, strong, p, h1 tags, etc).
The frontend makes serverless queries directly to a Postgres db.
There seem to be…
Blumenthaler
- 51
- 6
3
votes
2 answers
Regexp replace any single word
I need to replace 'page.a = b' with 'page.a = node.b'. In a word, i need to turn single word 'b' into 'node.b'; In other word, the string 'page.a = b && page.a < c' shold be transformed into 'page.a = node.b && page.a < node.c', just add 'node.' as…
Lou Kai
- 31
- 2
3
votes
1 answer
Match and remove all spaces inside brackets pattern
I want to match all spaces that are inside every [[]] in a string so I could use a replaceAll method and remove them.
Example input: text text [[ ia asd ]] [[asdasd]] dfgd dfaf sddgsd [[sss aaa]]
Expected output: text text [[iaasd]] [[asdasd]] dfgd…
shinzou
- 5,850
- 10
- 60
- 124
3
votes
5 answers
Remove parts from a string using a regular expression
I have a list of strings like so:
['NN.KTXS/KTXE.FOO BAR.STACK.OVERFLOW', 'NN.WFXL.Harlan KY.Harlan.KY', 'NN.WRGB/WCWN.Los Angeles CA.Burbank.CA', 'NN.KVII/KVIH.Denver.Denver.CO', 'NN.KEYE.Denver.Denver.CO']
I am trying to use a regular expression…
artemis
- 6,857
- 11
- 46
- 99
3
votes
1 answer
Attach string for matching substring
I have the following string
test = "if row['adm.w'] is 'Bad' and row['rem'] not empty"
I want to add str(...) for those tokens which are row[...], how can I do this in regex? I came up with this and it's not working as…
Fazli
- 171
- 8