Questions tagged [pattern-matching]
The pattern-matching tag has no usage guidance.
92
questions
2
votes
2
answers
313
views
Index for searching in column where the column starts with the provided value
I have a table, where one column contains a substring of an url. It's always the leftmost part of the url without scheme.
id
domain
column1
column2
1
example.com
value1
value2
2
example.org
value3
...
- 123
0
votes
1
answer
451
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
0
votes
1
answer
19
views
MATCH AGAINST gives 0 for 'Depression' against 'Depressed'
My MATCH AGAINST is otherwise working, I am just curious what is going on under the hood that
SELECT `rfv`, MATCH `rfv` AGAINST('Depressed') FROM `rfvs`
returns a match score of 0 when rfv is '...
0
votes
1
answer
2k
views
How to match a column against an array of seach strings?
I have a table jsondocs with the following columns:
id SERIAL
json TEXT
I want to check if the columns json contains some values but not exact match.
For example I can to do something like:
select * ...
0
votes
1
answer
573
views
MySQL FULLTEXT Match exponentially slower with search phrase
I'm trying to track down some performance issues in my FULLTEXT MATCH() AGAINST() query, and it seems to be related (at least in part) to queries requiring an exact phrase.
My table stores system ...
- 3
0
votes
1
answer
366
views
* wildcard only matching whitespace in mysql match against
I am trying to set up a search function on my web site. I have a table called projects with the following content:
+----+--------+------------------------------------------------------------+----------...
1
vote
0
answers
348
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
-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?
- 3
0
votes
1
answer
740
views
Understanding Postgres's similarity algorithm
Could someone explain how Postgres's similarity algorithm works?
Why do these examples return different results?
select similarity('Foo Bar Baz', 'Foo Bar'); -- 0.8
select similarity('Foo Bar Baz', '...
- 101
0
votes
1
answer
495
views
MySQL - Search Alphanumeric string using wildcards - underscore (_) and asterisk (*)
I have a table, products, with a product_id column as follows:
product_id
110177
110177T
177
I am trying to filter down the product results that should fetch result as below but it is also fetching ...
- 103
1
vote
1
answer
312
views
Fill new column by appending suffixes to an existing field based on a condition on a third one (mapping)
I'd like to know if there is a better solution than the following to construct a new column (place_type_new) by adding a suffix to the textual information of an existing column (place_type) based on ...
- 342
0
votes
1
answer
2k
views
Find partial match with textsearch query
I have the following query that I did hard looking on this site:
SELECT *
FROM renns
WHERE
(
to_tsvector('english', coalesce(numar_admin, '')) ||
to_tsvector('english', coalesce(...
- 101
0
votes
1
answer
2k
views
How to filter a character followed by numbers string pattern in PostgreSQL?
Back in SQL Server, I use the command below to get the matching string pattern of #1234 from the column called Number
SELECT [m].[Number]
FROM [Boats] AS [m]
WHERE [m].[Number] LIKE N'[#][0-9][0-9][0-...
- 127
0
votes
1
answer
54
views
MySQL: How to match data with regular expression string
I have a postcode column in my table with values like AB45*, RD4*, ADB567 etc.
Now I want to match my data with this column. The * means that a string starting with AB45 should match with this column. ...
- 239
0
votes
1
answer
779
views
How to pattern match against Unicode characters by PostgreSQL `SIMILAR TO` expressions?
In a PostgreSQL database, I have a column named metaid of type text whose value contains digits as specified in the third edition of XML 1.0 standard:
Digit ::= [#x0030-#x0039] | [#x0660-#x0669] | [#...
- 103
1
vote
1
answer
4k
views
Postgres HAVING ilike any wildcard in array_agg
I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is:
SELECT p.id, p.name, array_agg(vo.name)
FROM product p
LEFT ...
- 23
1
vote
1
answer
2k
views
Set var="NEW.this_word_column" in a trigger function if "word" is present in a column name
Context
I'm using PostgreSQL 10.12.
I want to be able to fire a trigger function on some specific tables with geographic data.
This trigger function is designed to create a PotGIS point into a ...
- 342
0
votes
0
answers
20
views
Finding matching suggestions with multiple records from different "users"
I'm not even entirely sure how to phrase my question because it is rather complicated. I am trying to implement a feature that will help crowd source information for one of our systems, and the system ...
- 43
3
votes
2
answers
3k
views
Replace a sequential set of numbers with special character
I have a varchar(200) column that contains entries such as,
ABC123124_A12312
ABC123_A1212
ABC123124_B12312
AC123124_AD12312
A12312_123
etc..
I want to replace a sequence of numbers with a single * ...
- 33
2
votes
1
answer
593
views
Find Table Variables definitions in Stored Procedures
As part of tuning of a large database, I am first of all interested in finding all the table variables.
I am using this script:
SELECT [Schema]=schema_name(o.schema_id), o.Name, o.type
FROM sys....
- 161
0
votes
1
answer
868
views
Postgres - how to find contents of one field within another field
How do I find where the contents of one field are within another field?
I have a database table with a auto-generated sequence number in one field, and another character based field which should ...
- 1,065
3
votes
3
answers
2k
views
Postgres trigram match acting strange for specific characters
I'm using pg_trgm to run a query, but I'm getting a lot of 1 matches when searching with a symbol difference. I have the following query:
SELECT my_column, similarity('$ Hello', my_column) AS sml
...
- 133
0
votes
1
answer
679
views
Select All Instances of Substring in a String
I have a varchar(max) column that holds a JSON request string. I need a SQL statement for a report that will select all of the instances of that substring. More specifically I need to grab the LoanId ...
- 155
0
votes
1
answer
2k
views
Extract part of a string in Postgres
I have a question how to extract a substring.
Here an example string:
{{from,005021889988,,Amt},{setup-from,66,,},{alert-to,66,ConTeach Zentrale,ConTeach Zentrale},{conn-to,66,ConTeach Zentrale,...
3
votes
2
answers
4k
views
Trying to check if a string contains a number exclusively
I have been trying to write a function to check if a string contains a number without that number being part of a bigger number (in other words if the number being searched for is '6' and the string ...
- 629
0
votes
1
answer
965
views
Full text search in PostgreSQL with search term including unrelated hyphen and ending
I've a problem in my DB (PGSQL 9.6) that I've not been able to resolve and I don't really even understand what options I might have (if any). Simplified I have a DB of multi-language news articles ...
0
votes
0
answers
325
views
nText and String manipulation. SQL SERVER
Parent question - Thanks to Paul White and Tom V for answer.
I am making little modification's to Paul White's answer to suit my requirements.
I would like to change the following to replace a ...
- 1
3
votes
3
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 ...
- 163
8
votes
1
answer
1k
views
Best approach to apply a long list of LIKE patterns?
As a follow-up to this question, I have one of my own.
The original question concerns the use of a CASE statement with > than 100 options and the statement has to be used in 4 places - so obviously ...
- 29.3k
2
votes
1
answer
3k
views
Optimize a trigram search with custom sort order
I'm new to typeahead / full text searching but I'm trying to figure out an algorithm that yields the best results for my search use-case. The additional module pg_trgm is installed. I have a table ...
- 187
9
votes
5
answers
6k
views
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 ...
- 22.7k
12
votes
1
answer
7k
views
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'),
...
- 131
0
votes
2
answers
739
views
Reverse pattern matching: find the longest prefix
I have a database with the phone rates. Rather simple one.
Code Price
---- -----
1 0.005
18 0.002
1888 0.000
Now I have a phone number arriving to the database: 18127551221. ...
- 103
4
votes
2
answers
577
views
Efficient way to see what pattern matches a single word
I'm creating a function to find out what search pattern would best fit a single string. The string to match will be compared against a table of what could be possibly hundreds of possible patterns to ...
- 71
0
votes
1
answer
247
views
ORA-00933 With Match_recognize
Am trying to figure out was wrong with this simple query that uses MATCH_RECOGNIZE. Am getting the error ORA-00933: SQL command not properly ended. What could I be missing?
SELECT EMPLOYEE_ID,...
1
vote
1
answer
137
views
How to match similar records using multiple columns
Given a GUID for an event, say logins, how might I search for similar or dissimilar events, utilizing the other columns data?
For example:
Find me similar logins to [GUID] {Server:"s123", State="NY", ...
- 60
7
votes
3
answers
21k
views
Find rows in which the column doesn't contain a "space"
I'm using Postgres 9.5. I want to search for rows in which my name column does not contain a space. I'm a little murky on how to define a space to you, though. I thought it would just be the space ...
- 635
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 ...
- 635
11
votes
2
answers
18k
views
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 ...
- 277
13
votes
3
answers
85k
views
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 ...
- 2,709
6
votes
3
answers
6k
views
UPDATE with join condition on matching words in columns of another table
I have 2 tables that looks like this:
Table A:
CREATE TEMP TABLE table_a (
Column_1 text,
ID_number int
);
INSERT INTO table_a VALUES
('foo,bar,baz', 123),
('qux,quux,quuz',456),
('corge,...
- 455
0
votes
1
answer
67
views
Oracle equivalent to MS-Access character range/set
In MS-Access, I can use character ranges/sets in the where clause:
SELECT
table_name
FROM
all_tab_columns
WHERE
table_name NOT LIKE 'A[0-9]*'
AND table_name NOT LIKE 'D[0-9]*'
...
- 1,437
18
votes
1
answer
25k
views
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 ...
- 437
8
votes
7
answers
1k
views
How can I generate all trailing substrings following a delimeter?
Given a string that may contain multiple instances of a delimiter, I want to generate all substrings starting after that character.
For example, given a string like 'a.b.c.d.e' (or array {a,b,c,d,e}, ...
- 353
8
votes
1
answer
15k
views
Equivalent of UTF8_UNICODE_CI collation in PostgreSQL
I would like a column in a table inside a PostgreSQL database (I am using version 9.6). I know of the UTF8_UNICODE_CI collation on MySQL, so I tried:
CREATE TABLE thing (
id BIGINT PRIMARY KEY
...
- 659
1
vote
2
answers
65
views
Select a set of keywords from table1 and search for strings containing these keywords in table2
I have two tables: t1 and t2. There are three steps:
In t1 select a set_of_strings with id = 1 ("1a" and"3c").
From t1 select strings that correspond to OPTIONID ='A' and that contain substrings from ...
- 213
5
votes
1
answer
4k
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 ...
- 451
9
votes
4
answers
4k
views
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 ...
- 193
-2
votes
1
answer
165
views
Pattern matching and replacement in oracle
So we have a table called TEMPLATES that stores html email templates as plain text (don't ask, I wasn't around when that decision was made). There are a number of columns in this table but the one we ...
- 107
2
votes
1
answer
2k
views
How to identify consonant/vowel pattern matches in MySQL?
Evening all,
I'm having a bit of trouble with a MySQL table full of vocabulary words today. I need to be able to quickly retrieve words with the same arrangement of consonants and vowels, so my ...