Tag: regexp-replace

How to find and replace string using REGEXP_REPLACE in postgresql

I have a table of email addresses: How can I find and replace the email format so example.person@gmail.com -> example.person_gmailcom@test.com? E.g: Results in example.person@test.comgmail.com Playground here: https://dbfiddle.uk/GnIfomiO Answer This is probably most simply done by splitting the email addr…

regex to find “null” values from a column

regexp-replace snowflake-cloud-data-platform snowflake-schema sql

I have a column JSON_DATA. The values within this column have an object type. It contains a json. This query works fine: This is how the data might look like: Some of the values within this field are “null”. I want to search all occurrences of quoted “nulls” and replace/regex_replace t…

Hive regexp_extract numeric value from a string

hive hiveql regex regexp-replace sql

I have a table as: I am trying to get the numeric values from the table. The expected output is A -> 123 / B -> 124 etc I am trying to do using regexp_extract Any suggestions please? Answer If the delimiters are fixed – ‘; ‘ between key-value pairs and ‘=’ between key and v…

REGEXP_REPLACE Strings Starting and Ending with Specific Substrings in Snowflake

regex regexp-replace snowflake-cloud-data-platform sql

I am trying to create a column in a view in Snowflake that replaces any string between strings that I care about with nothing. This is essentially for the purpose of stripping html formatting out of text. As an example: Would should end up like this: Based on the patterns I am seeing, I think that if I can el…

Oracle SQL REGEXP to fetch the last numeric value in a string

oracle-sqldeveloper regexp-replace sql

I have the below string value in a column and I need to extract the last numeric value. I have used SUBSTR(ColumnA, -1, 1), but this is extracting only the last digit. ColumnA 12_23_AB245-F5 66_78_HJ378-G5567 55_16_GC761-B99898 Below is the expected result ColumnA 5 5567 99898 Answer Use REGEXP_SUBSTR here wi…

How to select values between a XML tag in SQL Query

oracle regex regexp-replace sql xml

I have a table with CLOB column storing a XML. The structure of XML is unreadable. I want to get values between few tags like <DOMAINID>; sample is shown below. XML: I am using this: Expected result: Actual XML: Answer Do not use regular expressions to parse XML; use a proper XML parser. However, what y…

Unable to unnest in bigquery when there is comma inside list which is of type string

google-bigquery regex regexp-replace sql

Unable to unnest in bigquery when there is comma inside list which is of type string. I have data in following format where value is type of string When I use following query, It is incorrectly splitting and unable to get logic right in regexp_replace With the above code, I am getting My expected output is I …