I have a like below data : so i wanted to remove all the value after = and before | except for 1234. expected data : Answer You can use: Which, for the sample data: Outputs: VALUE 1234=A||1456=||1789= 1245=||1234=V 1234,1133 1456=||1234=1,234||1234 sqlfiddle here
Tag: regex
Oracle View loop on specific tables only
I got many tables of which some end with digits (date). Due to technical reasons I had to create a view on every table (just a workaround) The current state is something like: All it does is creating views for every table which begins with ‘XXX_’. Its almost what I need. But now, sometimes tablena…
How to regex match multiple items
I have a reviews table as follows: r_id comment 1 Weight cannot exceed 40 kg 2 You must not make the weight go over 31 k.g 3 Don’t excel above 94kg 4 Optimal weight is 45 kg 5 Don’t excel above 62 kg 6 Weight cannot exceed 7000g What I want to select is the weight a r_id’s cannot exceed.
Extract a desired character from an alphanumeric column using concise REGEX in Oracle SQL
I am going through a “clean-up” process of a character column that holds employee rankings that should have a single character: 0-5 or M or U but instead it has up to 3 characters that are either numeric, alpha or alphanumeric. I spent the better part of two days researching online (Regex, Stack o…
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 …
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 …
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 ca…
Oracle sql REGEXP to split a string at certain point so as to maintain the meaning- Suggestions required
I have a string for more than 40 characters and wanted to split it into two meaning full strings to set it into two different fields. Example: SELECT ‘THIS LONG STRING HAS EIGHT WORDS SEVEN SPACES’ …
How can I extract all text between single full stops (i.e. ignore `…`) with a re2 regex?
How can I extract all text between single full stops (i.e. ignore …) with a re2 regex? I am using the REGEXP_EXTRACT_ALL function in BigQuery which uses https://github.com/google/re2/wiki/Syntax. From the following example: This is… a.. sentence. It is just an example. I would like the query to ex…
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 y…