Skip to content

Tag: regex

How to extract everything after parentheses in BigQuery?

How can I extract all the characters after the first word? For example, I would like to have (084M) in its own column with the parentheses included. I’ve tried to SPLIT and REGEXP_EXTRACT but I have ran into issues. Table: Name Elizabeth (084M) Elizabeth (084M) Elizabeth (084M) Pittston (14KN) Pittston (14KN) Pittston (14KN) Cheektowaga (14ON) Image of Table: Answer use

Extract number between two characters in Hive SQL

The query below outputs 1642575.0. But I only want 1642575 (just the number without the decimal and the zero following it). The number of delimited values in the field varies. The only constant is that there’s always only one number with a decimal. I was trying to write a regexp function to extract the number between ” and .. How

Perform a particular ILIKE query on Postgresql

I have a problem with a query in Postgres. I have a column with values such as: id@@@Name1@@@other text@@@other_text@@@other_text@@@CODE@@@other_text### id@@@Name2@@@other text@@@other_text@@@other_text@@@CODE@@@other_text### id@@@Name3@@@other text@@@other_text@@@other_text@@@CODE@@@other_text### Each row can have different string suchs as the above. The @@@ separates each part of the string-block. The ### separates each string-block. @@@ and ### are so a “references”. With my query I have to find,

How to select values between a XML tag in SQL Query

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 you have is not properly

Regexp search SQL query fields

I have a repository of SQL queries and I want to understand which queries use certain tables or fields. Let’s say I want to understand what queries use the email field, how can I write it? Example SQL …

Convert from sql query condition to REGEXP_LIKE

I am converting an SQL query condition like: I tried to execute query in Oracle using condition belove but no result return: Result look like for example 169011310 so _ are numeric characters Answer The $ in regex means end of string, you need to remove it because % in TSQL means any text, any zero or more chars, and