Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [pattern-matching]

The tag has no usage guidance.

0
votes
0answers
12 views

matching observations from two datasets

I got involved with a task to match two datasets that baffles me to no end. I have two sample datasets. The first dataset has the courses that students enrolled during the term DS1 The second ...
0
votes
1answer
45 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 ...
0
votes
1answer
125 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
2answers
121 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 ...
0
votes
1answer
73 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
0answers
137 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
vote
2answers
929 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 ...
5
votes
1answer
264 views

CASE, VIEW, CTE or JOIN LATERAL: which is the best approach?

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 ...
2
votes
1answer
516 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 ...
9
votes
5answers
1k 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 ...
6
votes
1answer
1k 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
2answers
302 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. ...
4
votes
2answers
210 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
1answer
134 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
1answer
59 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", ...
3
votes
3answers
4k 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 ...
1
vote
1answer
2k 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 ...
6
votes
2answers
4k 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 ...
7
votes
2answers
37k 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 ...
6
votes
3answers
2k 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,...
0
votes
1answer
47 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]*' ...
10
votes
1answer
6k 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 with tsv ...
8
votes
7answers
648 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}, ...
3
votes
1answer
4k 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
2answers
59 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 ...
4
votes
1answer
2k 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 ...
7
votes
3answers
2k 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 ...
-2
votes
1answer
121 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
1answer
613 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 ...
2
votes
2answers
768 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: ...
2
votes
0answers
166 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 ...
1
vote
1answer
12k 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
1answer
237 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
1answer
3k 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 ...
4
votes
2answers
1k views

How to match all words of one column against words in another column

I have two tables. Table A: Company_name ------------------------------- Tata consultancy Infosys tech Atm service Air force firm Table B: Company1_name | Id -----------------...
2
votes
0answers
23 views

How to match all words of one column against words in another column? [duplicate]

I have two tables. Table A: Company_name ------------------------------- Tata consultancy Infosys tech Atm service Air force firm Table B: Company1_name | Id -----------------...
5
votes
2answers
11k views

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 ...
16
votes
2answers
1k views

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)) (...
3
votes
1answer
6k views

Find substrings within between 2 string fragments

I am trying to populate a view in PostGIS in Postgres 9.0 and in this view I want it to contain a substring based on 2 string positions. See below for my code. CREATE OR REPLACE VIEW vw_actions AS ...
3
votes
1answer
2k views

Index on column with data type citext not used

In PostgreSQL 9.4, with following schema: CREATE TABLE people ( id INTEGER PRIMARY KEY, name TEXT, junk CHAR(1000) ); INSERT INTO people(id, name) SELECT generate_series(1,100000), md5(...
1
vote
1answer
76 views

Pattern matching in PostgreSQL? [closed]

I have a string in which need to extract the date, Example: S01_XXX001_000_min_01_20150526T123440583.arc , S01_XXX001_000_20150526T123440583_min_01.arc based on the pattern like 'Numeric T Numeric',...
7
votes
1answer
5k views

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 (...
1
vote
1answer
178 views

Match all in a full text search

Using PostgreSQL 9.4, is there anyway that we can specify the tsquery to match all? I have tried: SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ '*'::tsquery; But it doesn't seem to ...
1
vote
2answers
680 views

Find and return items based on matching strings across 17 tables

I must start by saying that I am at best a sql novice. I am doing some data analysis and consolidation for my company and need to find if items match across any of the tables. The info in the tables ...
0
votes
1answer
1k views

SQL update multiple columns matching a pattern

I have a table in a postgresql database in which I have multiple columns matching a pattern /\w*(?:_open|_close)/g. Those are timefields. I would like to substract 1 hour from all the rows on those ...
3
votes
2answers
192 views

sql querying for a pattern

I was hoping someone could steer me in the right direction with this one. I have a table that has student grades. Create Table StudentGrades ( StudentID references Student(StudentID) int not null, ...
7
votes
1answer
812 views

Problem with pattern matching on unicode characters

I'm writing a piece of code that uses pattern matching on unicode characters but I'm running into an odd problem. Some characters work and some don't. DECLARE @Pattern nvarchar(50) = N'%[^a-z]%' -- ...
2
votes
1answer
110 views

Find string of decimal digits in Pi

I am trying to find the first occurrence of "12345" in the decimal expansion of $\pi$ by using MySQL. Possibly by using the Instr function? But I am stuck because it only displays the first 7 ...
4
votes
1answer
367 views

PL/pgSQL - Indexes vs Collation vs Pattern Ops

I'm using PostgreSQL 9.3. Having already read articles and answers on DBA stackexchange: Why would you index text pattern ops on a text column PostgreSQL documentation: Indexes - Opclass Pattern ...
3
votes
1answer
3k views

MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1

I've set ft_min_word_len = 1 to my.cnf file, also SHOW VARIABLES LIKE 'ft_min_word%' displays ft_min_word_len as 1. Then I've reloaded all database tables and it still doesn't search for one character ...