Questions tagged [regular-expression]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
41 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 ...
therobyouknow's user avatar
0 votes
0 answers
86 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 ...
Paula Morrison's user avatar
1 vote
2 answers
1k 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 ...
oren's user avatar
  • 13
5 votes
1 answer
654 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 ...
rdbmsNoob's user avatar
  • 449
2 votes
2 answers
412 views

Return part of a string with variable length

I have data in a table column like below: host=0.0.0.0 port=5432 dbname=database_name user=pglogicaluser host=0.0.0.0 port=5432 dbname=database_name2 user=pglogicaluser I want to write a query to get ...
Sayad Xiarkakh's user avatar
-3 votes
1 answer
1k views

How to store only US/Canada phone numbers in postgres?

I want to store only US and Canada phone numbers in a Postgres TEXT column. I don't want to use pg_libphonenumber. What CHECK constraints should I be using for this purpose?
yayaur's user avatar
  • 3
0 votes
1 answer
2k 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 ...
raphael75's user avatar
  • 244
5 votes
3 answers
12k views

How can you replace the last occurrence of a character in a string (text column)?

Let's say I have a text field with this value in Postgres: 'bar$foo$john$doe$xxx' I'd like to replace just the last occurrence of the dollar ($) character with another character, for example '-'. ...
Fmartinez's user avatar
0 votes
2 answers
1k 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 : ...
Leehan's user avatar
  • 207
7 votes
3 answers
2k views

Alternate Solution For REPLACE() In ntext Column

Overview I have a picture database that catalogue information of pictures taken with cameras. The database contains a table pictures which has 256 columns which contain information about the picture ...
John K. N.'s user avatar
  • 17.6k
1 vote
1 answer
715 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') ...
Sangathamilan Ravichadnran's user avatar
0 votes
3 answers
2k views

REGEXP_SUBSTR to split delimited values

I have a field called FREQUENCY and it has values like '10,0;30'. How do I write a regular expression to get the result like this: FREQUENCY 10 0 30
Jayachandran Jayaseelan's user avatar
1 vote
1 answer
274 views

Having regex_matches exclude cases where both capturing groups match a 0-length string

I'll trying to write a regex pattern that will help me create an array of length 2 arrays such that each inner array is matched like so: I'm looking to split sequences that begin with a positive or ...
Bobbie D's user avatar
  • 115
2 votes
1 answer
706 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 ...
Aravind's user avatar
  • 193
4 votes
3 answers
906 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 ...
Deele's user avatar
  • 143
3 votes
2 answers
543 views

Is using CLR for regular expressions safer than using external scripts?

Problem The main problem we need to use regular expression on MS SQL Server 2019, with the capability of at least the level on the POSIX Regular expression. Possible solutions This Q/A from ...
atevm's user avatar
  • 337
-1 votes
1 answer
681 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 ...
underachiever's user avatar
0 votes
1 answer
124 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 | ...
Pantea's user avatar
  • 1,412
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 | ...
Pantea's user avatar
  • 1,412
0 votes
1 answer
239 views

Problem using 'Regular expression' in order to split characters of a column when there is no delimiter between them

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 | ...
Pantea's user avatar
  • 1,412
0 votes
1 answer
100 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 &...
Matias's user avatar
  • 141
0 votes
2 answers
6k views

What is the main difference between '[[:space:]]' and '\s' in Oracle pl-sql regular expression?

I have a string like this x=y AND z=y AND p=q , I need to replace every (space)AND(space) pattern with * so that the original string turns into x=y*z=y*p=q. I'm using Regular expression for this ...
Pantea's user avatar
  • 1,412
0 votes
1 answer
25 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 ...
Pantea's user avatar
  • 1,412
1 vote
2 answers
2k views

Oracle PL-SQL "Regular expression" to replace each "(space)and(space)" with ',' in a string

I have a string like this x=y and g=h and u=1 and I need to replace each (space)and(space) with ,. I'm using the powerful regular_expression for this but it does not give me the desired result. ...
Pantea's user avatar
  • 1,412
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 ...
Pantea's user avatar
  • 1,412
14 votes
1 answer
2k views

How can I enforce that values in a table column match the Regular Expression "[axyto0-9\s]{0,2}[\s0-9]{0,10}"?

I have a table as below CREATE TABLE dbo.DemoTable ( Value VARCHAR(12) ) I would like to constrain it to only contain rows where Value matches the following pattern [axyto0-9\s]{0,2}[\s0-9]{0,10} ...
Martin Smith's user avatar
  • 84.4k
0 votes
1 answer
951 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 ...
Nina's user avatar
  • 149
0 votes
1 answer
454 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 ...
sqlearner's user avatar
-2 votes
1 answer
54 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 '...
Eysawy Mohamed's user avatar
0 votes
0 answers
254 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-...
arthur's user avatar
  • 878
4 votes
2 answers
2k 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 ...
J.D.'s user avatar
  • 37.1k
2 votes
2 answers
2k views

How to use "regular expression" to separate specific strings in Oracle

I have a string '(1:30,2:4,52:0,8:1)', and I need to use a regular expression to have this output: field1 field2 level 1 30 1 2 4 2 52 0 3 8 1 ...
Pantea's user avatar
  • 1,412
0 votes
1 answer
150 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')) ...
SQLPRODDBA's user avatar
  • 1,908
0 votes
1 answer
9k views

How can I search for a hex string in oracle?

There's a database record that is incorrect. The name field displays like this in a browser ( this is incorrect ): Thcˇodore And when I look at the record via SQL results, I see: Thc\xCB\x87odore I'...
cwd's user avatar
  • 105
1 vote
1 answer
681 views

Compare The length of two string variables inside an Expression task in SSIS

Hope you're doing well. I need to compare the Length of two string variables inside an expression task in SSIS so that if they have the same length the package will execute the next step and if not it ...
user avatar
0 votes
1 answer
2k views

Timestamp and substring with regular expression

I am using postgresql and I have the following table Table "public.test" Column | Type | Modifiers --------+--------------------------+----------- name | text ...
ibrasec's user avatar
  • 13
2 votes
1 answer
9k views

How to Extract SubString between two positions

I have a text column (emailbody) in a table (notification) which keeps a record of the HTML that went out for a particular email. Somewhere inside that HTML file, there is a line of text that contains ...
daniel9x's user avatar
  • 361
2 votes
1 answer
1k views

Postgres not using expression index

I am using Postgres 9.5. It is possible this is related to the changes in 9.6 pointed out by PostgreSQL not using index during count(*), but I cannot be sure yet. I have a table that has a character ...
Logan's user avatar
  • 121
0 votes
1 answer
6k views

oracle regexp_replace to remove duplicate duplicate strings and double separators

Been using the following regexp_replace to create a comma separated string for those values in 'col_name' and remove any duplicates. REGEXP_REPLACE(RTRIM(XMLAGG(XMLELEMENT(E,col_name,',').EXTRACT('//...
pgfitzpatrick's user avatar
1 vote
1 answer
819 views

Implement check constraint for varchar (7) to enforce YYYY-MM

I am considering the following with regards to implementing a check constraint on a column I am calling year_month. My objective is to have a check constraint to enforce YYYY-MM. My intention is to ...
BernardV's user avatar
  • 165
1 vote
1 answer
2k views

Select Statement with RegEx Replace - DB2 9.7

In a db2 9.7 select statement's field, I need to perform a global regular expression that replaces multiple white spaces (between words) with exactly one space. The regular expression would be base on ...
Lonnie Best's user avatar
0 votes
1 answer
336 views

Using REGEXP_REPLACE in Oracle

Am trying to use backreference expression to take out three subexpressions from a search, and reposition each subexpression as follows: SELECT REGEXP_REPLACE('AAA 123 ppp','(^[[:alpha:]]+) ([[:digit:...
Nicholas Namacha's user avatar
6 votes
1 answer
812 views

Postgres function index doesn't work correctly with regular expressions

I have this Postgres function. It forces text to be lowercase and all nulls to become empty strings, so that I more cleanly perform searches for things that don't match something, etc: CREATE OR ...
Karew's user avatar
  • 172
20 votes
1 answer
24k 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, ...
Suncatcher's user avatar
1 vote
2 answers
482 views

How to Get the Second Sequence of Characters using Regular Expressions

I have a field with an address in it (ex. 68 TIDAL BREEZE DR) and this regular expression gets everything before the last sequence (ex. 68 TIDAL BREEZE): substring (address from '(.*) ') My question ...
Matt's user avatar
  • 113
2 votes
1 answer
4k views

Select rows from my table where a character column matches a pattern

I'm using Postgres 9.5. I have a name column of type "character varying". I wonder how to select rows where the column begins with a number, followed by a space, followed by any other data. So I ...
Dave's user avatar
  • 663
7 votes
3 answers
5k 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 ...
z64's user avatar
  • 71
1 vote
1 answer
905 views

using regexp substitution part in postgres string functions

I hit this question trying to answer one in SO. When I pass \1 as first argument to lpad(string text, length int [, fill text]) function, it looks like it interprets \1 as regexp substitution part ...
Vao Tsun's user avatar
  • 1,193
1 vote
2 answers
2k 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 ...
Ivar's user avatar
  • 121
1 vote
1 answer
7k views

SSRS error - A scope is required for all aggregates in the page header or footer which reference fields

I had the following textbox expression in my SSRS report and it was working fine. = First(Fields!FirstName.Value) +" " + First(Fields!LastName.Value) + ", MD" When I moved the textbox to the ...
Juan Velez's user avatar
  • 3,245