Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [regular-expression]

141 questions
149
votes
8 answers

Pattern matching with LIKE, SIMILAR TO or regular expressions

I had to write a simple query where I go looking for people's name that start with a B or a D: SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering if there is a way to rewrite this to become more…
Lucas Kauffman
  • 1,835
  • 4
  • 17
  • 18
21
votes
1 answer

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, and Postgres version is 9.6.2. When I search for…
Suncatcher
  • 377
  • 2
  • 4
  • 12
20
votes
3 answers

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 'running%' vs anywhere in the string '%running%. I…
danielson317
  • 343
  • 2
  • 11
16
votes
2 answers

How to use SUBSTRING using REGEXP in MySQL

I have the following situation. I have to substring regular expression from description using MySQL. Description: Lorem D9801 ipsum dolor sit amet Where D9801 is REGEXP. Every strong text description has different content but my regexp should…
Marek Andrzejak
  • 161
  • 1
  • 1
  • 4
14
votes
1 answer

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} Start of string A single character present in the…
Martin Smith
  • 87,941
  • 15
  • 255
  • 354
8
votes
3 answers

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 is it not working? CREATE TABLE test ( id…
z64
  • 81
  • 1
  • 2
8
votes
2 answers

Is there a T-SQL equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters?

Is there a T-SQL equivalent of the [0-9] and [a-z] patterns that will let me pull values from a column that contains punctuation? For example: Create Table #Test ( Value VarChar(10) ) Insert Into #Test Values ('123a'), ('456b'),…
pix1985
  • 474
  • 1
  • 5
  • 14
7
votes
1 answer

How can I query names using regular expressions?

How could I search for a specific pattern in a column? For example, I like to get all user names that start with either letter A or B. (btw: this is tagged oracle, but it might be interesting in other RDBMS as well).
Sebastian Roth
  • 1,356
  • 3
  • 15
  • 23
7
votes
6 answers

Emulate REGEXP like behaviour in DB2 SQL

I posted the same on stackoverflow (please, let me know if I have to delete one). I'm working on a DB2 database and as far as I can see regexp is not supported (without additional libraries). So I cannot implement something similar to what is…
tmow
  • 173
  • 1
  • 1
  • 6
7
votes
3 answers

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 that has been taken. One column Comments is…
John K. N.
  • 18,854
  • 14
  • 56
  • 117
6
votes
1 answer

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 REPLACE FUNCTION magic_text(txt text) RETURNS…
Karew
  • 182
  • 8
5
votes
1 answer

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 the user_property table with a filter on the…
rdbmsNoob
  • 459
  • 9
  • 30
5
votes
3 answers

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 '-'. After the replacement, the contents of the field…
Fmartinez
  • 69
  • 1
  • 1
  • 4
5
votes
2 answers

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, although my skills are scarce, I thought it was a…
Joe
  • 369
  • 1
  • 3
  • 12
5
votes
1 answer

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 wildcard (which goes on indefinitely) or looking…
Salmononius2
  • 441
  • 2
  • 6
  • 15
1
2 3
…
9 10