I have a column where most values are numeric but a few contain letters or special characters: Records 90000 5200 unknown approximately 25 areas TBC 5000 (approx) I would like to remove any non-numeric entries and replace them with null like this: Records 90000 5200 NULL NULL NULL NULL I tried to remove non-n…
Tag: regex
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…
SQL query to get / delimited column value in single row
I have this output: Table 1 Requisition_number per_id per_name Job_title Interview TAS_EMAIL_ADDRESS TAS_FNAME 22021 1097 Chad Manager This is a comment abc.g@gmail.COM abc 22021 1097 Chad Manager This is a comment xyz.g@gmail.COM xyz I want the output to look like this: Requisition_number per_id per_name Job…
REGEXP_SUBSTR is inconsistent with extracting digits before a comma/decimal point [closed]
Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 14 days ago. Improve this question my regex looks like this: this returns the results just fine: regex value 11 11,2 5 5,2 11 1…
SQL – trimming values before bracket
I have a column of values where some values contain brackets with text which I would like to remove. This is an example of what I have and what I want: I have only come across approaches that use the number of characters or the position to trim the string, but these values have varying lengths. One way I was
BigQuery SQL Regex_extract repeated pattern
New to regexp, below is the sample query and our try as below union all select ‘https://www.this-is-pqrs.com/<some_text>/ab.abc.ef.gh.ij/123456.csv’ str union all select ‘https://www.this-is-pqrs.com/<some_text>/ab.abd.ef.gh.ij/123456.csv’ str union all select ‘https:…
Snowflake Regular Expression
I have this string in Snowflake column: I need to get names in this format regardless of the number of company names: “SpecTra, Signal Capital Partners”. In other words, I need to extract company names and concatenate them. I have tried this : and regexp_substr() function, but did not get the desi…
translate query from Oracle to Postgres
I need to translate this query from Oracle to Postgres: can someone help me? thanks in advance for your attention and support Answer In postgres the operateur ~ performs a regex comparison as thus replaces the Oracle function regexp_like(). Your query therefore becomes. I would like to alert your attention th…
SAME SQL regexp_extract, different impala and hive output. Why?
The same SQL command has two different output on Hive and Impala: Hive output: ff Impala output: ffff Why such difference? Please explain difference in terms of each engine’s method of processing and outputting characters space-by-space, from left to right or right to left, step by step, and the reasoni…
How can I split a string into two based on a character inbetween using REGEXP_EXTRACT?
I need to split a string into two based on a character and I need to do this without using SPLIT command. What I have: string fruit=orange fruit=apple vegetable=onion What I need: splitstring1 splitstring2 fruit orange fruit apple vegetable onion How can I solve this with REGEXP_EXTRACT? Answer Consider the b…