Questions tagged [string-searching]
For questions about searching a string for matches
80
questions
-2
votes
0
answers
27
views
How to extract specific numbers from a string?
I have a field holding strings like this - "1,2,3,4,5,11,21,22,10" [each record contains a combination of figures from 1 to 20 separated by commas]
I need to extract only the records ...
- 78.8k
2
votes
3
answers
179
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 ...
- 35.6k
0
votes
2
answers
72
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 ...
- 139
5
votes
1
answer
118
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 ...
- 2,661
1
vote
1
answer
295
views
How to search for compound words, and get the word parts returned
I need to be able to search for compound words like "menneskerettighedsforkæmperens" (the human rights activist's) and find the words "menneske" (human), "rettighed" (right) and "forkæmper" (activist)....
CommunityBot
- 1
1
vote
0
answers
406
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"
...
CommunityBot
- 1
138
votes
8
answers
218k
views
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
I had to write a simple query where I go looking for people's name that start with a B or a D :
SELECT s.name
FROM spelers s
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1
I was wondering ...
- 163k
1
vote
1
answer
195
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 ...
- 78.8k
0
votes
1
answer
143
views
List and count words from a column
I have a column with strings containing a list of species:
+----------------------------------------+
| species |
+----------------------------------------+
| Dinosauria,...
- 77.2k
2
votes
0
answers
142
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 ...
- 4,954
-1
votes
1
answer
32
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 ...
- 35.6k
1
vote
1
answer
3k
views
Use LIKE in column with arabic [closed]
I have a SQL Server 2012 DB with a column that includes Arabic text. The column is nvarchar(MAX) with collation Arabic_CI_AI_KS_WS. To test if LIKE searches are working I copied some of the text ...
- 16.2k
1
vote
1
answer
662
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 ...
5
votes
1
answer
164
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,...
- 28.6k
0
votes
1
answer
439
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', '...
- 29.2k
8
votes
1
answer
23k
views
LEFT vs CHARINDEX for matching the start of a string in T-SQL
I want to match the beginning of a string in a WHERE clause. While I recognise that so often database-performance is dependant on the underlying data structure, is there a best-practice for doing this?...
- 78.8k
1
vote
0
answers
69
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,
...
- 119
0
votes
1
answer
513
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 ...
- 892
-1
votes
1
answer
83
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 ...
- 113
0
votes
1
answer
391
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 ...
- 28.6k
1
vote
1
answer
263
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 ...
- 18.6k
0
votes
1
answer
124
views
Match similar special character in filters
I have to perform a search on a table with Turkish city names.
The users would enter the first 4 characters of a city and I need to fetch the results from SQL.
For example, user wants to find the ...
CommunityBot
- 1
3
votes
1
answer
615
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 ...
CommunityBot
- 1
0
votes
1
answer
74
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, ...
- 40.7k
3
votes
1
answer
133
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
---...
- 78.8k
0
votes
1
answer
76
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, ...
- 892
3
votes
1
answer
1k
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....
- 163k
2
votes
0
answers
46
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"
}...
- 121
0
votes
1
answer
190
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;
'...
- 101
0
votes
0
answers
386
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 ...
- 141
0
votes
1
answer
645
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 ...
- 35.6k
0
votes
1
answer
952
views
Querying an array of strings with a string that represents search terms
I have an array of values like:
array ('hello world', 'foo', 'bar') -- terms here have only letters and one space at most
and some text like: 'foo hello-world hello1world'
and I would like to get ...
CommunityBot
- 1
0
votes
3
answers
3k
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 ...
- 73.8k
1
vote
2
answers
4k
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 ...
- 472
0
votes
1
answer
49
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 ...
- 22.4k
13
votes
7
answers
61k
views
How to find all positions of a string within another string
How can I find all the positions with patindex in a table or variable?
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek '
+ 'dokhtare khoshkel be disco raft va ali ...
- 4,408
6
votes
1
answer
5k
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 ...
- 40.7k
0
votes
1
answer
643
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 ...
- 35.6k
0
votes
1
answer
852
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", "...
- 3
1
vote
2
answers
87
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 ...
CommunityBot
- 1
1
vote
1
answer
37
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> </div>
<div>[flv:http://submarino-libre....
- 22.6k
0
votes
2
answers
88
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 ...
- 15.4k
2
votes
2
answers
5k
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 ...
CommunityBot
- 1
4
votes
3
answers
13k
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 '-'
...
- 78.8k
1
vote
0
answers
63
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 ...
- 8,340
2
votes
1
answer
2k
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 ...
- 77.2k
-4
votes
1
answer
87
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 ...
- 1
0
votes
1
answer
585
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 ...
- 239
4
votes
1
answer
362
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
...
CommunityBot
- 1
0
votes
1
answer
50
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....
- 33