Questions tagged [string-searching]
For questions about searching a string for matches
85 questions
4
votes
1
answer
134
views
Odd behavior of SQL Server LIKE statement when using VARCHAR and NVARCHAR pattern
(Using SQL Server 2019 CU 31)
Why does only the CASE expression against @nvarchar_dash_after_ranges return 'LIKE' in this query? Note that the same comparison against a VARCHAR expression returns '...
- 1,219
-2
votes
3
answers
313
views
Finding specific text in any database table and column
I know the values in a column are 'active', 'cancelled',' complete'.
How can I write a query to find the name of every table and column containing these values?
It is a huge SQL Server database with ...
- 1
0
votes
0
answers
38
views
How can I get multiple results per document from full-text search?
My SQLite database for full-text search contains book contents. Each book is stored as a single document in the FTS table. At most one result is returned per book even though there are multiple ...
- 13
0
votes
1
answer
142
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`.`...
- 23
4
votes
3
answers
1k
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 ...
- 53
2
votes
1
answer
74
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 =...
- 131
0
votes
0
answers
392
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 ...
- 171
2
votes
3
answers
1k
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 ...
- 123
0
votes
2
answers
200
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 in TBL1 ...
0
votes
2
answers
491
views
How I can check whether a column contains a substring of a given searchterm?
In sqlite I have the following table
CREATE table IF NOT EXISTS redirect (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url_from TEXT not null,
url_to TEXT not null,
...
5
votes
1
answer
1k
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 ...
- 153
1
vote
1
answer
1k
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 ...
- 549
0
votes
1
answer
433
views
List and count words from a column
I have a column with strings containing a list of species:
+----------------------------------------+
| species |
+----------------------------------------+
| Dinosauria,...
- 113
1
vote
0
answers
678
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"
...
- 11
2
votes
0
answers
247
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 ...
- 513