Questions tagged [pattern-matching]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
13 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
78 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,...
-3 votes
1 answer
384 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
170 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', '...
0 votes
1 answer
222 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 ...
1 vote
1 answer
39 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 ...
  • 260
0 votes
1 answer
776 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(...
0 votes
1 answer
988 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
39 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. ...
0 votes
1 answer
311 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
2k 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
531 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 ...
  • 260
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
2k 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
362 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....
0 votes
1 answer
422 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 ...
3 votes
3 answers
1k 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 ...
0 votes
1 answer
498 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
1k 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
3k 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 ...
  • 619
0 votes
1 answer
713 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
279 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
2 votes
2 answers
6k 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 ...
  • 153
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 ...
  • 27.9k
2 votes
1 answer
2k 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 ...
  • 177
9 votes
5 answers
5k 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 ...
  • 21.6k
12 votes
1 answer
6k 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'), ...
0 votes
2 answers
608 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
522 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 ...
0 votes
1 answer
220 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
115 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
17k 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 ...
  • 515
2 votes
1 answer
3k 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 ...
  • 515
11 votes
2 answers
14k 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 ...
12 votes
3 answers
77k 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,377
6 votes
3 answers
5k 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,...
  • 445
0 votes
1 answer
66 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,387
17 votes
1 answer
21k 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 ...
  • 427
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}, ...
  • 343
8 votes
1 answer
12k 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 ...
1 vote
2 answers
64 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 ...
8 votes
4 answers
3k 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 ...
  • 183
-2 votes
1 answer
153 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 ...
2 votes
1 answer
1k 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 ...
3 votes
2 answers
2k views

Trigram index for ILIKE patterns not working as expected

I've got a simple but slow query: SELECT DISTINCT title FROM ja_jobs WHERE title ILIKE '%RYAN WER%' AND clientid = 31239 AND time_job > 1457826264 ORDER BY title LIMIT 10; Explain analyze: ...
's user avatar
2 votes
0 answers
230 views

Why does score for mysql boolean match give different score?

I am attempting to use MySQL's fulltext search using Boolean match on multiple columns, with multiple search phrases. I cannot understand the score returned though. The score returned is typically an ...
3 votes
1 answer
25k views

regexp_replace() vs. replace() to replace all matches in a string

By default, in PostgreSQL, regexp_replace() replaces the first occurrence of a substring in a string, while replace() replaces all occurrences of a substring in a string. Why is the default behavior ...
3 votes
1 answer
414 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE UNACCENT(LOWER(a.name)...
2 votes
1 answer
5k views

SQL MATCH AGAINST incomplete string with space

I have MySQL database of places, and I want to use the MATCH (...) AGAINST (...) function to find places with two-part names, such as New York. This is a problem because the match against syntax will ...
's user avatar