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
57
votes
2 answers
Laravel preg_match(): No ending delimiter '/' found
Im working on Laravel 4.2. Im trying to use Validator to validate a name field with regex, here is my rule below:
public static $rules_save = [
'class_subjects' => 'required|regex:/[0-9]([0-9]|-(?!-))+/'
];
But as soon as I call the…
Arlind Hajredinaj
- 8,380
- 3
- 30
- 45
33
votes
5 answers
Extract numbers from a field in PostgreSQL
I have a table with a column po_number of type varchar in Postgres 8.4. It stores alphanumeric values with some special characters. I want to ignore the characters [/alpha/?/$/encoding/.] and check if the column contains a number or not. If its a…
user1538020
- 515
- 1
- 9
- 25
18
votes
2 answers
how to use Regexp_replace in spark
I am pretty new to spark and would like to perform an operation on a column of a dataframe so as to replace all the , in the column with .
Assume there is a dataframe x and column x4
x4
1,3435
1,6566
-0,34435
I want the output to be…
user3420819
- 181
- 1
- 1
- 3
17
votes
3 answers
REGEXP_REPLACE capturing groups
I was wondering if someone could help me understand how to use Hive's regexp_replace function to capture groups in the regex and use those groups in the replacement string.
I have an example problem I'm working through below that involves…
jatal
- 790
- 1
- 10
- 19
16
votes
2 answers
Pyspark removing multiple characters in a dataframe column
Looking at pyspark, I see translate and regexp_replace to help me a single characters that exists in a dataframe column.
I was wondering if there is a way to supply multiple strings in the regexp_replace or translate so that it would parse them and…
E B
- 1,073
- 3
- 23
- 36
12
votes
7 answers
Python 3.7.4: 're.error: bad escape \s at position 0'
My program looks something like this:
import re
# Escape the string, in case it happens to have re metacharacters
my_str = "The quick brown fox jumped"
escaped_str = re.escape(my_str)
# "The\\ quick\\ brown\\ fox\\ jumped"
# Replace escaped space…
Steele Farnsworth
- 863
- 1
- 6
- 15
7
votes
4 answers
Replace repeated characters by number of repetitions in string
I'm trying to extract the number of times a given character is repeated and use it in the string to replace it for.
Here's an example :
before = c("w","www","answer","test","wwwxww")
after = c("w{1}","w{3}","answ{1}er","test","w{3}xw{2}")
Is there…
blofeld _
- 75
- 4
7
votes
4 answers
Convert upper chars to lower and lower to upper (vice versa)
I need to convert all lower characters to upper and all upper to lower in some string.
For example
var testString = 'heLLoWorld';
Should be
'HEllOwORLD'
after conversion.
What is the most elagant way to implement this, without saving temp…
user4881271
6
votes
4 answers
SQL Regex - Replace with substring from another field
I have a database table (Oracle 11g) of questionnaire feedback, including multiple choice, multiple answer questions. The Options column has each value the user could choose, and the Answers column has the numerical values of what they chose.
ID_NO …
Pete Rennard-Cumming
- 1,588
- 10
- 19
6
votes
5 answers
converting the data with regexp in oracle sql
I have a data like below with tab limited among them. I have represented them with a view here
with t_view as (select '6-21 6-21 6-21 6-21 6-21 6-21 6-21 ' as col from dual
union
select '6-20 6-20 6-20 6-20 6-20 ' from dual
union
select…
arunb2w
- 1,196
- 9
- 28
6
votes
3 answers
Retrieving first X words from a string in Oracle Select
I need to select the first X words in a string, where x can be any number from 0-100. Is there an easy way to do this? I found the following example to select the first 2 words from a string:
select regexp_replace('Hello world this is a test',…
Robert Smith
- 779
- 1
- 10
- 28
6
votes
2 answers
regex to replace backslash and single quote with single quote using postgres regexp_replace()
Just as the title states, I'm not the best w/ regex, so can anyone provide the appropriate regex for the following:
UPDATE table SET column = REGEXP_REPLACE(column, {regex}, '''');
Basically, I'd like to replace any instances of backslashes (\)…
William Orazi
- 1,694
- 6
- 23
- 36
5
votes
3 answers
Python regex string escaping for re.sub replace argument?
Using re module it's possible to use escaping for the replace pattern. eg:
def my_replace(string, src, dst):
import re
return re.sub(re.escape(src), dst, string)
While this works for the most-part, the dst string may include "\\9" for…
ideasman42
- 42,413
- 44
- 197
- 320
5
votes
6 answers
Reverse only numerical parts of string in sql server
With T-SQL, I'm trying to find the easiest way to reverse numbers in string. so for string like Test123Hello have Test321Hello.
[Before] [After]
Test123Hello Test321Hello
Tt143 Hello Tt341 Hello
12Hll …
jozi
- 2,833
- 6
- 28
- 41
4
votes
3 answers
Snowflake : REGEXP replace with uppercase of capture group
I want to replace the very first letter after a comma(,) with uppercase of it in snowflake database. Below given is what I tried, but it did not work.
eg:
Apple,ball,cat --> Apple,Ball,Cat
Bulb,LED,tube --> Bulb,LED,Tube
SELECT …
Teresa Rosemary
- 73
- 4