Questions tagged [regular-expression]
The regular-expression tag has no summary.
142 questions
0
votes
1
answer
17
views
Negative lookahead in table-matching pattern for psql or pg_dump
I'm trying to do a schema-only pg_dump of my database, however with data from one or two specific tables included. This means I can't use the --schema-only or --data-only options of pg_dump, and I can'...
- 604
0
votes
3
answers
2k
views
Remove spaces and replace characters with regexp_replace()
I wish to ...
... remove spaces
... delete apostrophes
... replace 'é' and 'è' with 'e'
I use the function regexp_replace().
For the moment, I can delete the spaces but poorly. Indeed, when the ...
- 125
0
votes
1
answer
228
views
Query for converting a column of numbers matching a regex in MySQL
I have a column which contains string values of the format e.g AB12345678 - two letters followed by 8 numeric digits.
I need to write a MySQL query to insert a 5 before each set of 4 digits thus ...
- 133
0
votes
0
answers
107
views
Counting Rows From a Value in a SQL text box - please please can anyone help?
I just can't find an example of this anywhere and hoping someone can help.
I have a query which returns a list of care services and other fields based on the care service table values and calculated ...
1
vote
2
answers
2k
views
Replace every character in each word, except the first two characters
Due to privacy concerns, I need to hide personal information, while remaining somewhat understandable. I propose to achieve that by hiding everything except the first two characters. The word length ...
- 13
5
votes
1
answer
1k
views
PostgreSQL - Index efficiently on REGEX_REPLACE()
I have a query which is designed to loop and search addresses for duplicates, the query uses REGEX_REPLACE. I am trying to index on the regex as on doing an explain and its doing a sequential scan on ...
- 459
4
votes
1
answer
1k
views
Index on expression (regular expression pattern matching) is not used in query
In my PostgreSQL 12.8 database, I have a relatively simple table the_table with a column value with type varchar:
CREATE TABLE public.the_table (
id uuid DEFAULT gen_random_uuid() NOT NULL,
...
- 143
-1
votes
1
answer
6k
views
REGEXP_SUBSTR equivalent in MSSQL
I migrate from Oracle DB to Postgres/MS-SQL and I want to use REGEXP_SUBSTR equivalent with these databases. I need to rewrite this statement to these databases:
SELECT b.id,
b.name,
b....
- 121
0
votes
1
answer
3k
views
postgres error: invalid regular expression: brackets [] not balanced
On the postgres command line, I tried this:
select
regexp_matches('file?device_id[]=555', 'device_id\[\]=(\d+)', 'g') as dev_id;
I get this error:
ERROR: invalid regular expression: brackets [] not ...
- 244
0
votes
2
answers
2k
views
constraint check values with regular expression
In a table with a field code, I want to authorize the user to choose one value among a list.
Here is a sample of the values of the list :
A|AG|AL|AS|B|C|D|DA
I added a constraint on code like this :
...
- 205
0
votes
1
answer
1k
views
Regex replace to ignore certain pattern at the beginning if exists and format the rest
How to use regex replace to ignore country code if it exists, I tried to use
select REGEXP_REPLACE(phone_number,'^/(?:011.44.)([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})$',
'\1-\2-\3') ...
- 113
0
votes
1
answer
15k
views
Look for a list of specific characters using RegEx
I'm trying to write a query (Postgres 8.2.15) which is checking whether the value in column1 consists of:
English alphabet only (A-Z), there should be no specific characters with umlaut/ablaut coming ...
1
vote
1
answer
2k
views
Filtering Escape Characters in PostgreSQL
I am trying to filter distinct serial number from a table and join two tables. Current my code looks like
SELECT DISTINCT ON (t1.serial_nbr) t1.*, q1.serl_nbr
FROM ( select
serial_nbr,...
0
votes
1
answer
846
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
1
answer
515
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 ...
- 115
4
votes
3
answers
1k
views
Large hexadecimal PRIMARY KEY - how to index substrings of the PK field?
I have a requirement for a very large (in terms of the number of rows, not data) database, and need to use a very long, unique, hexadecimal PRIMARY KEY instead of the usual AUTO_INCREMENT integer.
As ...
- 143
0
votes
1
answer
840
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? ...
- 281
1
vote
2
answers
88
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=...
- 475
0
votes
1
answer
245
views
Seperate value of a specific column with "," delimiter in Oracle using "Regular Expression"
I have a table with below structure:
create table CUSTOMER_INFO
(
cust_id NUMBER,
cust_name VARCHAR2(50),
cust_address VARCHAR2(50)
)
Example Data :
cust_id | cust_name | ...
- 1,502
0
votes
1
answer
4k
views
Using 'Listagg' function and 'Regular Expression' to REVERSE the value of a specific column in Oracle
I have a table with below structure:
create table TBL_TEST
(
col_id NUMBER,
col_name VARCHAR2(500)
)
Some example data :
col_id | col_name
-----------------
1 | aetnap
2 | ...
- 1,502
1
vote
1
answer
592
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 ...
- 141
0
votes
1
answer
155
views
replace string in field
I have these entries in a field in one table under a DB running Postgres 9.4:
copy schema_name.table_name to '/path/path2/path3/path4/reports/export"
and would like to replace ONLY the word &...
- 141
0
votes
1
answer
33
views
Query to select 'Customer_Num' with digit characters using regular expression
I have a table with below structure:
Example_table(Customer_Num varchar2(50))
Some Example data in this table :
Example_table
----------------------------
Customer_Num
...
- 1,502
1
vote
1
answer
2k
views
Error "ORA-12726:unmatched bracket in regular expression" while trying to separate a part of a string from other part
I have a string like this :
v_sgtring:'sum:[sum(x)>1 and sum(y)>2 and sum(z)>3],where:[n=1 and m=2]'
and I need to separate sum:[] from where:[] and assign them to different variables like ...
- 1,502
0
votes
1
answer
1k
views
How to replace (SIMILAR TO + regular expression) with LIKE or ~ in PostgreSQL?
I have the following PostgreSQL function which checks if p_text1 contains the word/phrase p_text2 within it:
CREATE OR REPLACE FUNCTION public."Contains"(
p_text1 character varying,
p_text2 character ...
- 159