I have the string “Serenity Lane – Barbur Boulevard 10920 SW Barbur Blvd Portland, OR 97219” and I want to select the first 13 characters from it :10920 SW Barb Is there a way to select only the first 13 characters after the first numeric character? In this example, the first thirteen charac…
Tag: regex
Using regex in sql to define new variable
I need to derive the “new” column from the “old” column using sql and regex if possible. I’m using Oracle SQL Developer. If I were using regex in R or Python, I would use this recipe to get the “new” column: Thanks. Use this: Answer Here’s one option:
Filter out records that are not in this date format oracle
How do i filter records that can only be converted to date using to_date(’31-May-2019 00:00:00′, ‘DD-MON-YYYY HH24:MI:SS’) from the above sample query i am expecting results below how do i achieve this in oracle. Reason why i want to do this: i am receiving raw data and loading it to a…
Postgres pattern matching middle letters of a string
How would I match a name, such that one of the middle letters of the name must come from the word ‘qwerty'(thus containing either of the letters ‘q’, ‘w’ , ‘e’, ‘r’, ‘t’,’y’)? I am curious how you can determine what the middle lette…
How to extract text between two words in Oracle
This is the source string: random foobar “name” : “Jack Ryan”, other random stuff In Oracle query, how do I extract Jack Ryan from it? I guess I am looking for whatever is between “name” : ” and “,…
Oracle SQL regexp_substr number extraction behavior
In a sense I’ve answered my own question, but I’m trying to understand the answer better: When using regexp_substr (in oracle) to extract the first occurrence of a number (either single or multi digits), how/why do the modifiers * and + impact the results? Why does + provide the behavior I’m…
Regex to match duplicate/alias e-mails in MySQL
I am trying to come up with some regular expression to check whether an e-mail exists in a database. What is more specific here is that I want to find e-mails that are the same, but might be written differently. E.g. john.doe@example.com is the same as johndode+123@example.com and j.o.h.n.d.o.e@example.com. U…
Django Reverse Regex Match
I have a table (Django Model) and each row/object contains a regular expression that I should evaluate over a given input to filter relevant objects. Is there any Django ORM method to do this? In Postgre it will be: and the opposite of what I am searching for is: I know that evaluating the regular expressions…
SQL Query with REGEXP to change URL strings dynamically
My DB table named “post” does look as follows So not every message row does contain an url and not every message with a [LINK]-tag does contain a proper url. Also there are enrties which have a longer ID, they should not be changed. Now i have to change every entry which has an ID length between 4…
SQL and Oracle query to extract every thing before last two periods
I need to extract every thing before last two periods eg. Input: AA.BBB.12.11.cc Output: AA.BBB.12 Following is the sample query I am using but that returns only the characters before first period, but that is not I needed. Answer I would use REGEXP_REPLACE here: The regex pattern .[^.]+.[^.]+$ will match sta…