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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
63 views

Replace every character in each word, except of first first two charachters

because of privacy in Databases, i need somehow hide personal informations, but it should stay a litle understandble. Want do it with hiding everything except of first two charachters. Word length ...
  • 3
0 votes
0 answers
28 views

Which is faster between regex and string length function, in searching string of different length?

I have a table named table1 having 2 columns named as user_id and col1. The table contains 5 million rows. This column col1 contains 3 types of values: first ending with char 'x' and string length 13; ...
0 votes
1 answer
112 views

How to get exact match when the column used for filtering is comma-separated list in postgresql

I have a column which will have values like: FC FCW FC,FCW FX,FC My input used for filtering should give correct result (match the exact value in the column) ex: FC like 'FC,FCW' --> true FC like '...
  • 1
5 votes
1 answer
138 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 ...
  • 389
0 votes
1 answer
317 views

How to replace repeating characters in a string with a single instance of the same character?

I have a column of urls with some of the urls like www.google.com/// Need them to be www.google.com/ instead.
4 votes
1 answer
296 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, ...
0 votes
0 answers
240 views

How to regex_replace strings with a table of regex/replacement pairs

I'm using Postgres. I have a table of poorly formatted data: ID Email 1 person1@gmial.com 2 person2 at gmial.com 3 pErSoN3@wRoNgDoMaIn.CoM I want to allow users to upload regular expressions ...
-2 votes
1 answer
2k 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....
0 votes
1 answer
565 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
1 vote
1 answer
509 views

To trim particular value in a column in Oracle sql

I have a regular expression select statement like below : SELECT REGEXP_REPLACE(nvl(l.text_1, l.text),'^'||21810||'=|\|' ||21810||'=', '|'||21810||'='||'B1') ...
0 votes
1 answer
5k 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 ...
0 votes
1 answer
23 views

Renaming all occurances of an HTML class name inside a table with SQL

In a MYSQL database of a CMS all occurances of a specific HTML class name should be pre-fixed. Let's say the table of interest is table_article and the HTML code inside this table is like this: <...
  • 103
1 vote
1 answer
883 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
240 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
301 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 ...
  • 105
2 votes
1 answer
125 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 ...
  • 193
0 votes
1 answer
260 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? ...
  • 275
-1 votes
1 answer
435 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 ...
  • 57
1 vote
1 answer
49 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=...
  • 455
0 votes
1 answer
121 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, ...
  • 113
1 vote
1 answer
1k 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
1 answer
365 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 ...
  • 131
3 votes
1 answer
943 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 =...
  • 101
0 votes
1 answer
371 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
1 answer
51 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
0 answers
195 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-...
  • 848
1 vote
0 answers
227 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 (...
  • 111
4 votes
2 answers
1k 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 ...
  • 30.6k
0 votes
1 answer
94 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
1 answer
121 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
1 answer
101 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
1 answer
1k 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 ...
  • 105
3 votes
0 answers
189 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 ...
  • 131
0 votes
1 answer
129 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')) ...
  • 1,810
3 votes
2 answers
1k 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[...
  • 131
0 votes
1 answer
1k 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 '%?%' ...
  • 143
2 votes
1 answer
5k 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.
  • 21
0 votes
1 answer
2k 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
1 answer
403 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 | ...
  • 25
0 votes
1 answer
941 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
-1 votes
3 answers
2k 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
1 answer
952 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
1 answer
1k 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
2 answers
7k 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 ...
  • 153
0 votes
2 answers
1k 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 ...
  • 113
5 votes
2 answers
2k 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, ...
  • 359
20 votes
3 answers
3k 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
2 answers
436 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 ...
  • 15
0 votes
1 answer
325 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,153
1 vote
1 answer
221 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, ...
  • 563