Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
1answer
21 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')) ...
2
votes
2answers
44 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
22 views

Cleaning up bad data with REGEXP_REPLACE too aggressive with non latin chars

Trying to use REGEXP_REPLACE to cleanup 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
72 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
21 views

how to filter through HTML in PostgreSQL query

I have a table with a name snapmail_logs. which has a column body in which I have been saving Email Templates as an HTML. There are a few things included in this body, to determine which email was ...
0
votes
1answer
139 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 ...
0
votes
0answers
40 views

how to extract single column into multi column in mysql?

i have a column bfi_name and values like this i want to extract this table into this format how can i do this in mysql?
1
vote
1answer
108 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
181 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 ...
0
votes
3answers
235 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
136 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
309 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 ...
1
vote
2answers
1k 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
212 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
585 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, ...
19
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
99 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
126 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
85 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
532 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}/...
13
votes
1answer
7k 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, ...
4
votes
3answers
2k 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
223 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
2k 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
736 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
1k 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
310 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 ...
3
votes
2answers
5k 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 %...
4
votes
2answers
1k 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....
1
vote
1answer
4k 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
84 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-...
2
votes
1answer
1k views

Oracle: REGEXP_LIKE: Strings with angle brackets aren't found (whether they are escaped or not)

The following question will answer my original question, plus many others. What would the correct syntax be to do a REGEXP_LIKE search (in Oracle Database 12c) for this string (which exists somewhere ...
0
votes
1answer
472 views

Oracle: How to create secondary index based on every word in a CLOB

I have a Table where each record is made up of a VARCHAR2 and a CLOB. The VARCHAR2 is the primary key. There are currently 1.5 million records and growing. The table is only ever used for REGEX ...
1
vote
2answers
6k views

Oracle regexp_like square brackets inside character set

This questions is in regards to Oracle, pl/sql, and the regexp_like function. I am trying to build a character set that will match on all typical special characters. My character set currently looks ...
1
vote
2answers
628 views

PostgreSQL return word after matching first character

I would like to return the words beginning with ':' from 'select :barns, :fowl, :cows from farm' using regexp_matches. So far I have: select regexp_matches('select :barns, :fowl, :cows from farm', '(...
3
votes
2answers
4k views

How to split numbers and text in MySQL

I have searched over the internet looking for a function like REGEXP_REPLACE in Oracle, regexp_replace in PostgresSQL but I haven't find one similar in MySQL just REGEXP and RLIKE, but these operators ...
0
votes
2answers
3k views

Faulty oracle11g regexp_replace on removing duplicates

I have a comma separated list of values (as one single string), which contains duplicates, and I want to remove them. I tried with REGEXP_REPLACE but for certain values it gives me a wrong result: /* ...
0
votes
1answer
5k views

Postgresql regex match all digits in column

How do I extract all digits from a string with regex in pgsql? select regexp_matches('dsa8a552a5a2a5?', '\d+'); Will output only 8. Need to get 8552525
2
votes
1answer
471 views

How To Exclude Queries Returned From mysqldumpslow Command

I would like to ignore queries in the mysql-slow log that originate from running the command mysqldumpslow by using SQL_NO_CACHE as the trigger word. My experience using regex are limited at best and ...
2
votes
1answer
924 views

Order by where conditions

SELECT * FROM table WHERE key REGEXP 'some long regular expression' OR key REGEXP 'some other long regular expression' ORDER BY key REGEXP 'some long regular expression' DESC, key REGEXP 'some other ...
2
votes
1answer
116 views

How to store a 64-digit key of alpha numerical values into MySQL Database

I am student studying in Database Administration (first year), one of our task requires a to design a login form for corporate and regular users. Below are the requirements for the username and ...
0
votes
1answer
236 views

Mysql select using regexp

I have some user input i am receiving from some forms exactly in this format Names#Age#Gender#City The problem is,some forms come with incomplete data which is acceptable during the filling of the ...
1
vote
0answers
220 views

Import expanded csv file into mysql

I need to import a spreadsheet that was being viewed with the expand/collapse feature into mysql. As shown in the screenshot, when an item (WMIMCART1 in this case) is expanded, it shows more info ...
2
votes
1answer
583 views

How to apply a regex on a column fetched from DB

I am using IBM DB2. So, I am retrieving a column named SSN from a table. I have to apply the following regex on that column: ([1-57-8][0-9]{2} |0([1-9][0-9] |[0-9][1-9]) |6([0-57-9][0-9] |[0-9][0-57-...