Questions tagged [pattern-matching]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
17 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
1answer
21 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] | [#...
1
vote
1answer
83 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 ...
1
vote
1answer
53 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 ...
0
votes
0answers
19 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 ...
3
votes
2answers
420 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 * ...
0
votes
0answers
27 views

Unable use LIKE operator for pattern matching data within two columns. Netezza - WORKS, Yellowbrick - DOES_NOT_WORK

This is a new problem that we have been facing when we are trying to migrate into a new data warehouse solution (Yellowbrick) from IBM Netezza I have two tables Table_Product – Contains random ...
2
votes
1answer
115 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
1answer
37 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
3answers
758 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
1answer
127 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
802 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
2k 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
278 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
239 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 ...
2
votes
2answers
4k 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 ...
6
votes
1answer
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 ...
2
votes
1answer
1k 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
4k 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 ...
9
votes
1answer
3k 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
436 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
431 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
188 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
87 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", ...
4
votes
3answers
10k 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 ...
2
votes
1answer
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 ...
8
votes
2answers
8k 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 ...
9
votes
3answers
59k 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
4k 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
62 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]*' ...
14
votes
1answer
13k 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
991 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}, ...
5
votes
1answer
8k 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
62 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
3k 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
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 ...
-2
votes
1answer
150 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
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 ...
2
votes
2answers
1k 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
216 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
1answer
20k 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
344 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
4k 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
2k 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
26 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 -----------------...
10
votes
2answers
19k 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 ...
17
votes
2answers
2k 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
7k 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
3k 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
85 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',...