Questions tagged [regex]
The term REGEX is a shortening of the phrase "regular expression". A regex is typically a sequence of characters that forms a search pattern, mainly for use in pattern matching with strings.
63
questions
0
votes
1answer
14 views
Using caret (^) for pattern list matching negation in SIMILAR TO and ~ queries in Redshift
I'm quite new to using POSIX regex in Redshift. I honestly think that this pattern must be compatible.
[^A-Za-z0-9\\.\\,-]+
However, as I've tried a query like this
SELECT * FROM my_table WHERE ...
0
votes
1answer
18 views
Optimizing regex based query in sqlite
I'm using an sqlite database to store manually created labels for some data automatically queried from a live system. The data from the live system consists primarily of an address, comprised of 3 ...
2
votes
1answer
46 views
Replacing a specific string in oracle
I've a database value 'INS. Company Cancelled' where multiple values are separated by '.'
I'm trying to replace 'INS' with 'INSEXP' my expected result is 'INSEXP. Company Cancelled'
I've tried below ...
0
votes
1answer
26 views
What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT?
What's the difference between REGEXP_SUBSTR and REGEXP_EXTRACT?
As far as I can tell, they seem to do the same thing. Is there some difference in side-effects, or how they perform for certain inputs? ...
-1
votes
1answer
60 views
Postgresql Regex - escape possessive quantifiers [closed]
I have a query that is formed in Java
SELECT * from COMPONENTS WHERE name ~* = 'searchTerm';
This query hits PostgreSQL via JDBC and returns the results. I have to use ~* because the user from the UI ...
1
vote
1answer
34 views
How to extract certificate attributes from a single column and split into many?
In my table I've a field (let say cert_attr) which stores Certificate X.509 Attributes.
Here is the example of 3 rows (each line corresponds to a field):
"CN=User1, OU=Eng, O=Company Ltd, L=D4, S=...
0
votes
1answer
20 views
How to call a function in regex_replace with matches?
I'd like to use some processing function instead of replacement text in regex_replace function. Is there possible to use a function with matches as arguments, like:
SELECT REGEXP_REPLACE( description, ...
1
vote
1answer
246 views
How to sum the values of a json column filtered with regex?
How to sum the values of a json column filtered with regex?
Columns:
A.name: Varchar with name product
B.products: JSON with time active By product
Query:
select
A.name as product,
sum(((...
1
vote
1answer
28 views
Count numbers in scientific notation (E-notation)
I'm trying to check if a part number field has scientific notation values and came up with this simple solution:
SELECT count(*) as overall,
count(*) filter (where partnumber ilike '%E+%') as ...
3
votes
1answer
312 views
Convert column value stripping html tags into sql view with rows and columns
I have a table named data with a column pid & display_data.
Pid is the serial number of all rows.
The value of display_data column is as below :
For each row display_Data has values in the format =...
0
votes
1answer
137 views
SQLite, ASCII Characters 0x20 to 0x7E Check Constraint
I have the following table which I'm trying to limit the column name to ASCII characters ranging from 0x20 to 0x7E. Why is it not working?
CREATE TABLE test
(
id INTEGER NOT NULL,
name TEXT ...
-2
votes
1answer
47 views
oracle find to first hyphens after last vertical bar
oracle find to first hyphens after last vertical bar using REGEXP_REPLACE
SELECT REGEXP_REPLACE ('sadsa|dd|g - g|hh|yyyyy - tttt - ooo', '.*(\|)(?!.*\|).*?\-{1}', '' ) FROM dual;
string input
'...
0
votes
0answers
36 views
How to check if a string contains a sequence of binary regexp chars in postgres
I am trying to filter all non-UTF characters in a field of a table in postgres. It looks to me that I need to filter anything that is not:
$$[\09\0A\0D\x20-\x7E]|$$|| -- ASCII
$$[\xC2-...
1
vote
0answers
54 views
MariaDB REGEXP_REPLACE Invalid utf8 byte sequence
I'm working with a fairly old (15 years) old database. It started as a MySQL3, was upgraded several times, the frontends had problems with proper encoding, the whole database was converted to utf8mb4 (...
4
votes
2answers
645 views
Is there an operator or an easy way to match an expression one or more times with the LIKE operator in SQL?
For example if I run the following query, the expression in the WHERE clause will only match on the first letter before a number but is there a way to match on one or more letters first (e.g. in the ...
0
votes
1answer
33 views
How to select any couple of digits except '00' using mysql regex subset syntax?
I just found out that mysql doesn't support full regular expressions syntax. I have to select any couple of digits except of 00 (so, 11, 54, 02, are ok, but not 00).
Normally I would use a negative ...
0
votes
1answer
41 views
How to query mysql tables with regular expressions?
I'm using a correct regex, already tested with other tools. But in mysql seems not to work properly. Say this data:
And I want to select all the records which start with 2 non-digits followed by a ...
0
votes
1answer
35 views
Using to_base64 function and regexp_replace with backreferences in MariaDB
I am using MariaDB 10.3. I want to replace a string using REGEX and encode part of the result into base64.
For example, I want to first invert the "a" and "b" strings in this example:
MariaDB [(none)...
0
votes
1answer
364 views
Date pattern regex doesn't match the records in PostgreSQL query?
I am working with a dataset in PostgreSQL which is quick messy. I have written a few regex operations to find date patterns in the records of a table. Since I don't have a generalized REGEX defined ...
2
votes
0answers
113 views
check if MySQL is using the Henry Spencer implementation for REGEX
Prior to MySQL 8.0.4, MySQL used the Henry Spencer implementation for REGEX and you could use this for word boundaries:
'[[:<:]]' and '[[:>:]]'
From 8.0.4 MySQL is using the ICU implementation ...
0
votes
1answer
65 views
Need help with Regex in SQL Server query editior
I have a file with thousand lines of codes with index details like below:
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))
...
3
votes
2answers
506 views
Convert Ruby regex to Postgres regex, for selecting invalid email addresses
I've done some internet trawling, and some reading of the Postgres regex docs, but I'm at the stage where I think asking a question here is the best thing to do.
I have the following Ruby regex:
/\A[...
0
votes
1answer
243 views
Cleaning up bad data with REGEXP_REPLACE too aggressive with non latin chars
Trying to use REGEXP_REPLACE to clean up a text field in a table.
Here's my query:
select title original ,REGEXP_REPLACE(title, '[^a-zA-Z0-9]+', ' ') clean
from tmp_table2
WHERE txn_title like '%?%'
...
2
votes
1answer
2k views
SQL regex to identify alphanumeric values
I am trying to exclude values like 3708.DK in sql. I'm using pattern like LIKE '%0-9.%' and '%[0-9.A-Z]%' and its not working .
Please suggest.
0
votes
1answer
846 views
Regex replace with empty string results in NULL characters in between
Replacing a character with regexp_replace results in NUL characters in between.
SELECT REGEXP_REPLACE(aColumn,'c', '')
INTO OUTFILE 'Replaced.csv';
for abcd results in:
ab d (infact: abNULd)
This ...
1
vote
1answer
309 views
Mysql Regex on where clause
I need a regular expression which will give me only those records which match a pattern.Below is table structure
auto_id (Auto-increment) | json_string (Text)
Sample records are:
auto_id | ...
0
votes
1answer
820 views
Using Regex in mySQL query
I have a database column with multiple names separated by line breaks like this:
jones, sue
jones, mark
I want to generate a query that keeps the first line and discards the rest, no matter how many ...
-1
votes
3answers
951 views
how to regexp positive lookaheads in MySQL
I want to filter as regexp like "(?=nkf)(?=bom)" in MySQL.
This example regexp works in PHP, JavaScript and other.
This regexp means both words include "nkf" and "bom".
I try the regexp in WordPress ...
1
vote
1answer
374 views
How to clean every non-digit except the + at beginning of string with regexp_replace in Postgresql?
Related to a comment, I would like to find a way to clean with regexp_replace in PostgreSQL any non-digit except the + at beginning of the string.
For example +(7)9125415501 shall become +79125415501
0
votes
1answer
611 views
Oracle regular expression replace in string ignoring first and last characters
regular expression replace in string ignoring first and last characters if they matched
my try
SELECT REGEXP_REPLACE ('TLYU TK0T23T', '[^(\A.)]T[^(.\Z)]', 'Qq' ) contain FROM dual;
result
...
2
votes
2answers
4k views
Postgres - Return default value if regex match fails
I'd like to attempt a regex match, and return null if it fails.
The following query attempts to find the first number in a string. The result ignores the entry with text 'blah'. I'd prefer it to ...
0
votes
2answers
640 views
SQL Regex String Matching of Two Words
I'm trying to write a query to show the rows containing a code that begins with 11-digit number followed by -1 or -2 then _DataSheet or _RoHS
Examples
02704198976-1_DataSheet
03448106681-1_RoHS
...
5
votes
2answers
1k views
Capitalize first letter with exceptions
I need to properly format some European addresses.
One of the steps is to capitalize the first letter, but avoiding some specific words, like 'on', 'upon', 'von', 'van', 'di', 'in', 'sul'.
Therefore, ...
20
votes
3answers
2k views
What is it called when you search the middle of a string instead of the beginning?
I am trying to polish up my vocabulary to better communicate with my fellow developers. We have several places in the site where we are debating if we should search for a string from the beginning '...
1
vote
2answers
176 views
SQL regex PostgreSQL
Let's consider a table like that:
id | name
1 | This is a test sentence
2 | is a sentence TeST
3 | This a IS test sentence
4 | iS a tEst sentence tHis
5 | This a test sentence is
How ...
0
votes
1answer
224 views
Problem to apply replacement with regexp that include look ahead in Postgres
I have this kind of string 12s2-3a abc def and I am trying to apply the regexp ^(?=.*\d) to remove the first part and having as left abc def.
I have tried with:
select substring ('12s2-3 abc def' ...
1
vote
1answer
123 views
Why is LIKE producing different results than my regex?
Using Postgres 9.5. Having some trouble using regular expressions to find matching rows. I tried
select * FROM my_object_times t where t.name ~ '/\(.*\)\s*$/'
...
(0 rows)
which returned no rows, ...
1
vote
1answer
2k views
REGEXP in a where clause
I store s3 bucket's path for files in my database. Files can be either pictures(jpg) or pdfs.
Let's suppose I have uploaded photo.jpg to my database via application, path will be like:
/{s3bucket}/...
16
votes
1answer
14k views
Querying non-ASCII rows from Postgres
Does [:ascii:] class work in Postgres at all? It is not listed in their help, however I see examples in the web which utilize it.
I have a UTF-8 database, where collation and c_type are en_US.UTF-8, ...
3
votes
3answers
3k views
SQLite, ASCII A-Z Check Constraint
I have the following table which I'm trying to limit the column "prefix" to ASCII alphabetical characters. However, I am still able to insert other characters after using the following constraint. Why ...
0
votes
1answer
443 views
Display a field using regex
I have a table with a field in with data such as:
id | path
1 | /2/9/1
2 | /2/4/11
3 | /1/3/11
4 | /5/3/2
5 | /2/1/4
I'm wanting to do a select query to show only the first number between ...
4
votes
1answer
3k views
Repeating pattern X amount of times in LIKE
I took a look at the documentation for patterns from Microsoft (can be found here), and from what I understood, it doesn't say a way for a pattern to repeat a limited number of times. It's either the ...
1
vote
2answers
1k views
How to use character classes in a Postgres regex select?
I'm trying to create a query that selects strings that contain the number 21, without hitting strings such as 210 or 121. My efforts below have shown my progress, in that I've managed to apply a ...
1
vote
1answer
2k views
Oracle index with REGEXP_REPLACE is it a good idea?
I have a table of contacts with 1 million registers and now the user wants to search in it by the telephone.
Trying to avoid a fullscan I decided to create a new index in this column, but the trick ...
2
votes
1answer
594 views
REGEXP does not show the right result?
I have in the name column:
3 boxes of frogs
7 boxes of frogs
48 boxes of frogs
When I execute:
SELECT name FROM items WHERE name REGEXP "[^1-5] boxes"
...the result is:
7 boxes of frogs
48 boxes ...
0
votes
1answer
37 views
Create new table values based on partial column value - access
I am trying to remove a ton of unnecessary rows in this table.
Currently there are 1.3m of them which are all an individual transaction id - with the country and destination assigned for each which ...
2
votes
2answers
11k views
How to use LIKE with subquery?
In a SQLITE3 DB, I have a table A with a column 'word' containing search words that partially match a column in another table B.
I'd like to SELECT all rows FROM table B WHERE B.name LIKE %...
3
votes
2answers
2k views
Update fields that contains certain prefix by lookup table
I have a temporary table #NAMEOLD_NAMENEW which contains a column NAMEOLD and a column NAMENEW where every row contains the old and new value of a value to be updated. This table consists of roughly 2....
2
votes
1answer
6k views
Unicode error with \u0000 on COPY of large JSON file into Postgres
I'm using Postgres 9.4 on Ubuntu 14.04.
I have a 30GB JSON file that I'm trying to COPY into Postgres.
But I keep getting the following error:
COPY <table>(comment_jsonb) FROM '<json file&...
1
vote
1answer
267 views
Using REGEX_LIKE to Implement a Check Constraint
Background
I am working on the database design for an application that tracks information about database development projects at my company. The project management office (PMO) assigns a psued-...
