Questions tagged [string-searching]

For questions about searching a string for matches

Filter by
Sorted by
Tagged with
146 votes
8 answers
236k views

Pattern matching with LIKE, SIMILAR TO or regular expressions

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 if ...
Lucas Kauffman's user avatar
20 votes
3 answers
3k views

What is it called when you search the middle of a string instead of the beginning?

I am trying to polish up my vocabulary to better communicate with my fellow developers. We have several places in the site where we are debating if we should search for a string from the beginning '...
danielson317's user avatar
13 votes
7 answers
64k 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 ...
Ario's user avatar
  • 1,042
13 votes
4 answers
68k 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 ...
Emanuel's user avatar
  • 263
9 votes
5 answers
7k 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 ...
Andriy M's user avatar
  • 23k
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
8 votes
1 answer
25k 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?...
Bassmanjase'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
5 votes
4 answers
31k 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 ...
Evan Carroll's user avatar
  • 63.1k
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
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
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
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
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
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
4 votes
1 answer
2k views

Alternatives to LIKE and FTS for mid name search

I have a question about what are the alternatives for LIKE '%%' and Full-Text Search when it comes to searching organisation names, firstnames, surnames, etc. The LIKE '%%' causes table scan, however ...
Peter's user avatar
  • 41
4 votes
2 answers
90 views

Performing search on a column by query

Up until today I've used LIKE %term% to query for rows on particular column term. Then I've read about FULL TEXT index. And I think I misunderstood how full text index works. I have an API that ...
Dmitry Kudryavtsev's user avatar
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
3 votes
3 answers
3k views

Autocomplete too slow: possible optimizations?

The autocomplete search functionality of my website searches a varchar field which contains the model number of sale items. This field can contains strings varying from 1 to 75 characters and the ...
A_V's user avatar
  • 1,364
3 votes
1 answer
259 views

patindex parser

I am trying to parse a string column matching it against several patterns. I have code that does not produce the desired results. This is a sample table: -- demo data IF OBJECT_ID('dbo.Input', 'U') ...
Janice Denovan's user avatar
3 votes
1 answer
2k views

Using SELECT within to_tsvector call in CREATE INDEX

I'm attempting to create a GIN index for full-text search on a field that may have text in it that is one of several different languages. The languages are pre-set and we'll know which language we ...
Brooks's user avatar
  • 531
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
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
2 votes
1 answer
2k 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 ...
Marcello Miorelli's user avatar
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
2 votes
1 answer
3k 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'), ('...
Mio's user avatar
  • 641
2 votes
3 answers
3k views

Using fulltext search to find a sequence of digits

We have a column of ten digit numbers (with leading zeros). We are trying to find rows that have a specific sequence of digits. For example, all rows containing "5088" in any place in the column. ...
user115223's user avatar
2 votes
1 answer
234 views

Good databases for unanchored LIKE condition

Basically I need a database that is good with queries like LIKE %abc%. I already tried PostgreSQL with GIN indexes and it is very good, but maybe there is something even better? I also tried MongoDB ...
Davinel's user avatar
  • 131
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
2 votes
1 answer
401 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 ...
James Jenkins's user avatar
2 votes
1 answer
5k 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.) ...
xodust1's user avatar
  • 23
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
2 votes
2 answers
3k 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 ...
nullReference'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
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
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
2 votes
0 answers
46 views

How to implement a "Command T" (VIM) -like search

I am thinking of implementing a string search similar to the command-t search in VIM. The context is almost the same: Finding Files in a Webapp. It's not easy to explain how the command-t search ...
metafoo's user avatar
  • 21
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
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
1 vote
1 answer
442 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 ...
raratiru's user avatar
  • 113
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
1 vote
1 answer
2k views

Compare strings from two tables SQL Server 2014

I'm using these two tables in a SQL Server 2014 database: CREATE TABLE #FORMULAS ( [ID] [int] IDENTITY(1,1) NOT NULL, [DataSourceAddress] [nvarchar](255) NULL, [Description] [nvarchar](...
JosepB's user avatar
  • 113
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
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
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
7k 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: [email protected] OR username@domain....
jpl42's user avatar
  • 113
1 vote
1 answer
638 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 ...
Reza Paidar's user avatar
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
1 vote
1 answer
3k 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 ...
Boezie's user avatar
  • 13
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