Questions tagged [pattern-matching]
94 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
36
votes
2 answers
Why would you index text_pattern_ops on a text column?
Today Seven Databases in Seven Weeks introduced me to per-operator indexes.
You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as long as the values are indexed in lowercase.
CREATE…
Iain Samuel McLean Elder
- 2,408
- 5
- 26
- 39
30
votes
2 answers
How to create an index to speed up an aggregate LIKE query on an expression?
I may be asking the wrong question in the title. Here are the facts:
My customer service folk have been complaining about slow response times when doing customer lookups on the administration interface of our Django-based site.
We're using Postgres…
David Eyk
- 537
- 1
- 5
- 11
25
votes
3 answers
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect each record, executing a regular expression or…
Nick
- 353
- 1
- 3
- 5
21
votes
2 answers
PostgreSQL LIKE query on ARRAY field
Is there any way to have a Postgres LIKE query on a ARRAY field?
Currently I want something like that:
SELECT * FROM list WHERE lower(array_field) LIKE '1234%'
Currently lower is not needed that much. However it should find ONE matching field…
Christian Schmitt
- 443
- 2
- 5
- 13
20
votes
1 answer
Get partial match from GIN indexed TSVECTOR column
I would like to get results by query this:
SELECT *
FROM (
SELECT id, subject
FROM mailboxes
WHERE tsv @@ plainto_tsquery('avail')
) AS t1
ORDER BY id DESC;
This works and return rows with tsv containing 'Available'. But if I use…
xangr
- 457
- 1
- 5
- 10
18
votes
2 answers
Trigram search gets much slower as search string gets longer
In a Postgres 9.1 database, I have a table table1 with ~1.5M rows and a column label (simplified names for the sake of this question).
There is a functional trigram-index on lower(unaccent(label)) (unaccent() has been made immutable to allow its use…
P.Péter
- 911
- 1
- 9
- 20
18
votes
1 answer
Best index for similarity function
So I have this table with 6.2 millions records and I have to perform search queries with similarity for one for the column.
The queries can be:
SELECT "lca_test".* FROM "lca_test"
WHERE (similarity(job_title, 'sales executive') > 0.6)
AND…
bl0b
- 363
- 2
- 5
- 9
16
votes
3 answers
Algorithm for finding the longest prefix
I have two tables.
First one is a table with prefixes
code name price
343 ek1 10
3435 nt 4
3432 ek2 2
Second is call records with phone numbers
number time
834353212 10
834321242 20
834312345 30
I need write a script…
Korjavin Ivan
- 285
- 1
- 2
- 9
13
votes
3 answers
SQL Server: Replace with wildcards?
Does Microsoft SQL Server natively support some sort of replace function using wild cards? I gather that Regular Expressions are not available natively.
I note that there is a PATINDEX function which can be used to bolt together a solution — is…
Manngo
- 3,065
- 10
- 38
- 61
12
votes
1 answer
How to search hyphenated words in PostgreSQL full text search?
I have to search for hyphenated words like 'good-morning', 'good-evening', etc.
My query is:
select id, ts_headline(content,
to_tsquery('english','good-morning'),
'HighlightAll=true MaxFragments=100…
user3098231
- 131
- 2
- 7
12
votes
2 answers
PostgreSQL full text search on many columns
I need an advice with searching of record based on specified string.
Search strings can contain values from these columns. Values in this string don´t have to be strictly identical given in the correct order and also the values of some columns in…
Denis Stephanov
- 287
- 1
- 3
- 10
9
votes
5 answers
Matching a ] (closing square bracket) with PATINDEX using the "[ ]" wildcard
I am writing a custom JSON parser in T-SQL†.
For the purpose of my parser, I am using the PATINDEX function that calculates the position of a token from a list of tokens. The tokens in my case are all single characters and they include these:
{ } […
Andriy M
- 23,261
- 6
- 60
- 103
9
votes
4 answers
Matching left and right single-quotes used as apostophes
I have four columns containing names and want to search these using a LIKE in a Microsoft SQL Server environment.
The complication comes that names may include left and right single-quotes / angled apostrophes (i.e. ‘ and ’, char(145) and…
JLo
- 193
- 1
- 6
8
votes
3 answers
Sort by match of LIKE
I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the results ordered by the best match of asked query. …
adopilot
- 2,443
- 8
- 31
- 46