Questions tagged [string-searching]

For questions about searching a string for matches

Filter by
Sorted by
Tagged with
0 votes
1 answer
70 views

Query Optimization | Improve Query Time with LIKE Statement in Use

I have the following query; SELECT `e`.`entity_id` FROM `catalog_category_entity` AS `e` LEFT JOIN `catalog_category_entity_varchar` AS `at_url_path_default` ON (`at_url_path_default`.`row_id` = `e`.`...
Marisa's user avatar
  • 23
4 votes
3 answers
451 views

Is there any way to improve performance on like searches that have the % to the left of the searched value?

I have a query that does a like statement on a column that stores fullpath locations of files on a computer. Example select * from table where fullpath like '%hi.exe' Which never seems to use an ...
Steven M's user avatar
2 votes
1 answer
57 views

Moving window search which stops at longest substring matches

I'm trying to create a search which searches a field for a longest matches possible and then stop. Here's the basic idea in Python for single match: import re def generate(txt: str) -> list: s ...
raspi's user avatar
  • 131
0 votes
0 answers
202 views

SQL search for a pattern in the middle of a string (infix search) without full table scan

With SQL, I need to select rows which have a pattern like %hello% (i.e. <anything>hello<anything>) in a column, with potentially 100 millions of rows or more. In the answer Infix search in ...
Basj's user avatar
  • 171
2 votes
3 answers
707 views

EXPLAIN ANALYZE does not show what takes time with my GIN index scan

Context I have a table named companies_establishments that holds ~33M rows. I created a GIN index with trigrams, so I can make LIKE queries much faster. CREATE INDEX companies_establishments_id_index ...
Madeorsk's user avatar
  • 123
0 votes
2 answers
97 views

Using a WHERE colname NOT LIKE (select * from otherTable)

So in essence what im trying to do is this: Given TBL1 (around 8,000 rows) letters abababa bcbcbcb cdcdcdc and TBL2 (around 2,000 rows) letters ab bc I want to write a query to find every row ...
Shahzad Ansari's user avatar
5 votes
1 answer
620 views

postgresql matching or converting utf-8 variant strings

Postgres 13 I am looking for a way to search UTF-8 text that may have variant character representations ( what is the proper term for this? ie 𝐋𝐈𝐅𝐄 vs life ) within postgresql. I am running into ...
unomi's user avatar
  • 153
1 vote
1 answer
632 views

Case sensitivity not working

I have an issue where I keep getting the value 'CtP_PETER_Fact' out of the query below. It should be a case sensitive where-clause. I tried it in a few different ways: setting the COLLATE statement ...
Niels Broertjes's user avatar
0 votes
1 answer
320 views

List and count words from a column

I have a column with strings containing a list of species: +----------------------------------------+ | species | +----------------------------------------+ | Dinosauria,...
Lisdengard's user avatar
1 vote
0 answers
609 views

Optimize select query with OR and ILIKE

I have the following query for my PostgreSQL 13 database: SELECT * FROM "user" INNER JOIN "comment" on "comment"."user_id" = "user"."id" ...
Kamal Karain's user avatar
2 votes
0 answers
206 views

Full text index search with character at the beginning

I am looking at tables like this (I did not design them): CREATE TABLE EventIDsToSearch (id int not null) insert into EventIDsToSearch values (1) insert into EventIDsToSearch values (92) insert into ...
w0051977's user avatar
  • 503
-1 votes
1 answer
68 views

How should I construct a "custom synonyms" table?

I have a table called diary. I insert several messages into it almost every day. I sometimes, for example, call Microsoft "Microjunk", "Micro$oft", "M$" and many other ...
Klappoklang's user avatar
0 votes
1 answer
1k 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', '...
csirmazbendeguz's user avatar
5 votes
1 answer
189 views

Match .csv values as INTs. If one value from a group in the .csv matches one in another group, then merge strings

Here we have two sets of numbers. The problem is that I can't figure out how to get from the input to the output of numbers (DDL and DML below and also in the fiddle here). current_data 1,2,3 1,4 1,5,...
user3050153's user avatar
1 vote
0 answers
107 views

Search by first and last name of the Instructor

I have the below tables in the MySQL database create table course_class ( id varchar(36) not null primary key, class_code varchar(255) not null, ...
Swapnil's user avatar
  • 119
0 votes
1 answer
1k views

string search in table with partial text and without all field names

I use postgresql database and the need is to return all the rows that contain a given string in any column of that table. And string could be like just half . For examaple search string can ve IT ...
pie's user avatar
  • 1
-1 votes
1 answer
126 views

Search string data with special characters removed

We have a table with ~6 million of products: ID Name Code 1 One 123.456.789-M 2 Two 852.789456 3 Three 1-123654.P What is a good way (with regards to performance) to filter products by code ...
Gadonski's user avatar
  • 191
0 votes
1 answer
547 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 ...
Slimshadddyyy's user avatar
1 vote
1 answer
512 views

How to do a ranked search based on number of multiple columns matched

Context I am trying to create a ranked-customer-search that will order results based on "most likely correct". We have several factors we search by, but to keep it simple I will stick with ...
Nicholas Summers's user avatar
0 votes
1 answer
153 views

String search is very slow with OR statement

I have the following tables: product, product_stock, and product_offer I need to optimize the search when looking for a product that is available in product_stock or that is currently on offer. First, ...
fareed's user avatar
  • 127
3 votes
1 answer
189 views

Indexing Referenced Table Columns

I would like to do a search across columns of joined tables. What would the best index configuration be for the query below? Table A ------------ Id ValueA -- Table A has many more columns Table B ---...
André Haupt's user avatar
0 votes
1 answer
150 views

Levenshtein function returning different result based on limit value

The Levenshtein function is not working as I would have expect. Is there anything that I minsunderstood? Here is the query: SELECT c0.id, c0.engine_type, c0.mpg, c0.kwh, c0.price, c0.make, c0.model, ...
ryanzidago's user avatar
3 votes
1 answer
2k views

Trigram similarity (pg_trgm) with German umlauts

I try to figure out how to improve Postgres 10.6 pg_trgm queries with German umlauts (äöü). In german 'ö' can be written as 'oe'. But beware: not every 'oe' can be written as 'ö'. CREATE TABLE public....
Stephan's user avatar
  • 143
1 vote
1 answer
895 views

Perform SELECT using WHERE IN clause on JSON Object in MySQL

I have a json column in MySQL which contains a list of items for eg. "style": {"style1", "style2", "style3", "style4", "style5"} I want to ...
girish's user avatar
  • 111
0 votes
0 answers
739 views

Performance of searching for hex strings in large tables (using LIKE, Full-Text Search, etc.)

I have a table with 40+ million rows in MS SQL Server Express 2019. One of the columns store pure hexadecimal strings (both binary and readable ASCII content). I need to search this table for rows ...
Johannes Mols's user avatar
2 votes
0 answers
85 views

Searching across collections based on different column name

I have following collections with fields in my mongodb database: Collection: Category [ { "cat_id": "cat_id_1", "cat_name": "education" }...
TheLittleNaruto's user avatar
0 votes
1 answer
964 views

How to replace (SIMILAR TO + regular expression) with LIKE or ~ in PostgreSQL?

I have the following PostgreSQL function which checks if p_text1 contains the word/phrase p_text2 within it: CREATE OR REPLACE FUNCTION public."Contains"( p_text1 character varying, p_text2 character ...
Nina's user avatar
  • 149
1 vote
2 answers
6k views

Case insensitive LIKE in DB2

Is there a way to do a LIKE operation in DB2 against a varchar column in a case insensitive way, without changing the underlying column definition? Or is there a workaround like SELECT * FROM mytable ...
user101289's user avatar
0 votes
3 answers
4k views

MySQL LIKE IN with Input Parameter of stored procedure Comma Separated Values

I am creating a stored procedure to search user's Towns. From the User Interface user will select multiple towns that will be sent to the stored procedure. Sample input of the towns parameter for the ...
Hamza Khanzada's user avatar
1 vote
1 answer
2k views

How to select values consisting of numerals only?

I have a column like this: WW0476091 YA0457514 547856232 856987452 254785W11 I want to get the values that contain numerals only (decimal digits from 0 to 9). For the example above, I want only ...
Arshiya Mukherjee's user avatar
6 votes
1 answer
7k views

Multi language full text search using postgresql

I am trying to implement full text search using postgresql for some images. I am storing some information about my images into a json field in my table. This json has a tags key where I have multiple ...
melokki's user avatar
  • 83
0 votes
1 answer
262 views

Like '%' search for value withing longblob column in MySql is slow

MySQL version is 5.7.25 I have table like CREATE TABLE `applications` ( `id` varchar(25) NOT NULL, `application` longblob NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; '...
David Abragimov's user avatar
3 votes
1 answer
801 views

SQL query with Like operator with Leading Wildcards

I have a query as follows: select top (30000) * from table1 where licenseplate like '%as43bv%' I have an index on licenseplate but the query will not hit the index due to leading wildcard. Since ...
SQL_NoExpert's user avatar
  • 1,049
0 votes
1 answer
739 views

How to optimize query for text array in PostgreSQL using `@>` operator

I have a column of type text[] and I want to search over this column using this SQL operator @> Note: @> operator, it filters the data as such ['a','b'] in ['a','b','c'] The ...
Fadi Bakoura's user avatar
0 votes
1 answer
1k views

trigram matching on a jsonb array

Hello I'm trying to implement some sort of fuzzy string matching using pg_trgm on my table which contains a jsonb array, here is a sample data: name ------------------ [{"firstname": "jhon", "...
seng's user avatar
  • 3
1 vote
2 answers
95 views

Get the matching condition in the SELECT list

Let's say my full text search query is below: USE AdventureWorks2012; GO SELECT Name FROM Production.Product WHERE CONTAINS(Name, '"chain*" OR "full*"'); GO I need one more column that ...
Biju jose's user avatar
  • 2,088
1 vote
1 answer
41 views

Using LOCATE() Function as SUBSTRING() parameter gives ramdom result. Extracting string from text

I have several rows with a lot of content and i'm trying to find some code and extract it. It looks like this: Some code... <div>&nbsp;</div> <div>[flv:http://submarino-libre....
gallo2000sv's user avatar
0 votes
2 answers
114 views

Get matching part from conditional pattern

I'm having a table like this: |id|key |value | ------------------------ | 0|name |Homer | | 1|surname|Simpson | | 2|town |Springfield| I'm running this query: SELECT * FROM ...
sc911's user avatar
  • 103
2 votes
2 answers
7k views

How to find exact string from a sentence

I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's ...
Skizo-ozᴉʞS ツ's user avatar
4 votes
3 answers
17k views

How to determine if a hyphen (-) exists inside a column

In a CASE expression, I'm trying to search inside a text column to identify a hyphen (-): CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS CONTAINS '-' ...
Daylon Hunt's user avatar
1 vote
0 answers
81 views

Searching for and parsing out specific text parts within the values of a column

I'm working on a CASE expression that I need to have in order to search for, and parse out, specific text values. Sometimes the relevant column will contain a person's name, and sometimes it will ...
Daylon Hunt's user avatar
2 votes
1 answer
4k views

How to search for a string in all columns of a table?

I use postgresql database and the need is to return all the rows that contain a given string in any column of that table. For example, I have a table like this. I need a query to return all rows that ...
daedalus_hamlet's user avatar
-4 votes
1 answer
88 views

which is the appropriate database for my use case [closed]

I have a set of n sentences (n >= 100k). I want to perform queries of the sort where given a set of n_i sentences i return a set of m_i words that is present in one subset of n_i sentences and not ...
arrhhh's user avatar
  • 1
0 votes
1 answer
839 views

Search for partial matches in website domain names

I have a Postgres database with information about websites and a table called sites with a column host. This already has an index for text pattern operations which is excellent for searching from the ...
Charlie Clark's user avatar
4 votes
1 answer
413 views

T-SQL LIKE Predicate failed to match with whitespace in XML converted varchar

Recently I attempt to search for a particular pattern by converting XML data into varchar(max) although I'm aware it's not the best practice and found out it's not working as expected:- Setup ...
Zephyr's user avatar
  • 203
0 votes
1 answer
69 views

duplicate entries from mysql LIKE query

with this sample dataset I have this query SELECT products.id, products.title AS product_title, products.code, products....
gstlouis's user avatar
8 votes
2 answers
1k views

Fastest way to split/store a long string for charindex function

I have a 1 TB string of digits. Given a 12-character sequence of digits I want to get the start-position of this sequence in the original string (charindex function). I have tested this with a 1GB ...
Werner Aumayr's user avatar
0 votes
1 answer
9k views

How can I search for a hex string in oracle?

There's a database record that is incorrect. The name field displays like this in a browser ( this is incorrect ): Thcˇodore And when I look at the record via SQL results, I see: Thc\xCB\x87odore I'...
cwd's user avatar
  • 105
1 vote
2 answers
1k views

Query performance of searching pattern

Posted the same question on SO, but did not get answer. As per Andrey Nikolov comment, tried with Trigram, but the table name and column(more than 50 columns) are dynamic i mean will get from front ...
MAK's user avatar
  • 283
4 votes
1 answer
539 views

Find records with same string with extra character

OK, so I have a Microsoft SQL Server 2014 database table called owner with around 90,000 records with owner information, another called vehicle with vehicle information Owner_Name ...
MindXpert's user avatar