Questions tagged [string-searching]
For questions about searching a string for matches
65
questions
1
vote
1answer
26 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 ...
0
votes
1answer
23 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, ...
0
votes
0answers
22 views
Is instant text search possible in big table with HTMLs?
I have a pretty big table with HTMLs (about 500K rows, average length of text is ~150K).
It is required to make fast search (less than 1s) rows which match let say '%<meta name="twitter:app%'.
...
3
votes
1answer
113 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
---...
0
votes
1answer
29 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, ...
2
votes
1answer
173 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....
1
vote
0answers
101 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 ...
0
votes
0answers
64 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 ...
2
votes
0answers
38 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"
}...
0
votes
1answer
94 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 ...
0
votes
2answers
898 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 ...
0
votes
3answers
1k 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 ...
0
votes
1answer
24 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 ...
5
votes
1answer
1k 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 ...
0
votes
1answer
47 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;
'...
3
votes
1answer
280 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 ...
0
votes
1answer
334 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 ...
0
votes
1answer
286 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", "...
1
vote
2answers
76 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 ...
1
vote
1answer
28 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....
0
votes
2answers
31 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 ...
2
votes
2answers
1k 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 ...
4
votes
3answers
5k 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 '-'
...
1
vote
0answers
33 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 ...
0
votes
1answer
112 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 ...
-4
votes
1answer
80 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 ...
0
votes
1answer
295 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 ...
3
votes
1answer
255 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
...
0
votes
1answer
19 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....
8
votes
2answers
740 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 ...
0
votes
1answer
3k 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'...
1
vote
2answers
362 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 ...
4
votes
1answer
215 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 ...
0
votes
1answer
45 views
Which option is better to search in two tables in varchar columns? [duplicate]
I have a performance problem with searching in varchar columns in SQL Server 2016. Considering these tables have plenty of rows, for example 60000 rows each one.
Tables
CREATE TABLE Persons (
...
1
vote
1answer
64 views
Use extra column to save an unaccented copy of a string instead of using unaccent module [closed]
I am heavily using the unaccent module in a table with 500.000 rows where each row contains proportionally a lot of accented characters.
The issue I am facing is that my queries have become time ...
2
votes
1answer
187 views
How to search Query Store for a specific word or command in a query?
I have an Enterprise Edition of SQL 2016 SP1, that has a database with Query Store active.
I want to search for a specific word or command in the saved queries. I have googled around and not seen ...
0
votes
1answer
2k views
Can a Postgres LIKE statement return an exact match?
I'm trying to search a Postgres table using LIKE and %, but the query is not returning results.
The column local_username will contains values formatted like: username@domain.local OR username@domain....
2
votes
0answers
2k views
Use LIKE in column with arabic
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 ...
0
votes
1answer
43 views
TSQL - Finding Similar Values
I have a static table containing filenames (StaticFilesTbl).
Each day a file is sent in that is stored in DailyFilesTbl.
The filenames in DailyFilesTbl will be different from StaticFilesTbl in that ...
2
votes
1answer
2k views
Search entire database(210 tables) for a unique Id (PK) and get the table name
Rewritten Question:
I need to look through an entire database, 210 tables for a unique ID that is a number.
I have limited access to my database through things called nodes (Visual programming.)
...
2
votes
1answer
1k views
How do I find a job that sends a specific email?
I had a job sending an irrelevant email to a group of users.
The title of the email was Staging -> [AUPAIR] arrivalDate trigger issue.
When I query the jobs table in the msdb database using the ...
1
vote
1answer
152 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)....
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
4answers
54k views
Find if any of the rows partially match a string
I want to see if a table contains any sub-string of a given string.
Let's say I have a string
somedomain.com
In database I have:
blabladomain.com
testdomain.com
domain.com
I need to make a ...
0
votes
1answer
1k views
why my split string functions are not working with large OBJECT_DEFINITION?
I have in one of my databases a stored procedure
called PROCEDURE [repl].[usp_upd_repl_out_application], that is not exactly a very clever stored procedure but it has over 389 lines.
As I need to ...
5
votes
4answers
10k views
ERROR: operator does not exist: text[] ~~ text
We have an easy syntax that allows us to look into an array for a single scalar,
SELECT 'foo' = ANY(ARRAY['foo', 'bar', 'baz']);
We can use the same method to match with LIKE
SELECT 'foobar' LIKE ...
1
vote
1answer
2k views
Using LIKE to search case insensitive over language specific characters (PostgreSQL)
Using a PostgreSQL database (Collation C, Encoding UTF8) we store data from various languages. This means we also get the special characters for free (e.g. Å, å, ...)
Within an application we now ...
1
vote
2answers
2k views
Return results that contain or do not contain punctuation
Is there a way within MySQL to return results using a LIKE statement that contain or do not contain punctuation characters such as apostrophes and hyphens?
For example if I have one of the following ...
1
vote
1answer
573 views
Count number of occurrences of a phrase in ntext
I am trying to create a function for counting the number of occurrences of a phrase in a ntext value.
This my function's code:
alter FUNCTION [dbo].[PhraseCounter] ( @pInput NTEXT, @pSearchPhrase ...
2
votes
1answer
1k views
Search on concatenate columns
I have this :
CREATE TABLE users
(
first_name varchar(60) NOT NULL,
last_name varchar(60) NOT NULL
);
INSERT INTO users
(first_name, last_name)
VALUES
('Sylvia ', 'Hoeks'),
('...