Skip to main content
24 votes
Accepted

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

It's called an "un-anchored search pattern", and it looks like this in SQL. foo LIKE '%bar%' If you lack a % on either side, it is said that the search pattern anchors to the start or end of the ...
Evan Carroll's user avatar
  • 63.7k
14 votes

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

You didn't mention why the code you provided doesn't work. CONTAINS is for use with SQL Server's full text search feature. If you're not using this, then you need to use a LIKE clause with wildcards:...
Josh Darnell's user avatar
  • 29.4k
13 votes
Accepted

Find if any of the rows partially match a string

You can use LOCATE() – as in the other answer – or LIKE: SELECT * FROM site_core_antispam_banned_domain WHERE 'subdomain.com' LIKE CONCAT('%', domain_name, '%') ; The above will look for domains ...
ypercubeᵀᴹ's user avatar
10 votes
Accepted

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

An alternative approach to the existing answer is to use the CHARINDEX() function which returns the position of the specified string if exists, otherwise 0. select charindex('-','kevin-') Will ...
kevinnwhat's user avatar
  • 2,224
9 votes
Accepted

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

This is by design. When you store a document using the XML data type it is compressed and organised into a structure that Sql Server can perform operations on efficiently. One of the steps that it ...
Mr.Brownstone's user avatar
9 votes
Accepted

Multi language full text search using postgresql

You should definitely use a different column per language. The main reason is that different languages have different stop words and stemming rules, so if you index something with to_tsvector('...
Laurenz Albe's user avatar
  • 53.5k
9 votes
Accepted

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

FWIW, I don't think the previously provided duplicate questions were actually duplicate of this specific question and instead answered a more complex tangentially related question on how to improve ...
J.D.'s user avatar
  • 38.1k
8 votes
Accepted

Matching a ] (closing square bracket) with PATINDEX using the "[ ]" wildcard

My own solution, which is more of a workaround, consisted in specifying a character range that included the ] and using that range along with the other characters in the [ ] wildcard. I used a range ...
Andriy M's user avatar
  • 23.1k
8 votes
Accepted

How do I find a job that sends a specific email?

The problem with your first query is the use of unescaped brackets with the LIKE operator. When using the LIKE operator, there are a few special characters such as brackets that require escaping if ...
EzLo's user avatar
  • 3,306
7 votes
Accepted

Find records with same string with extra character

The SOUNDEX function can be applied to a column as well. But since there's thousands like that I wouldn't suggest just writing a query to join on a function to do that. This will likely not ...
Erik Darling's user avatar
  • 41.9k
7 votes
Accepted

Indexing Referenced Table Columns

Your query is a tricky one for the optimizer, for two reasons. The optimizer doesn't currently have logic to transform a disjunction (OR) across tables into a union (related Q & A). The optimizer ...
Paul White's user avatar
  • 86.4k
7 votes
Accepted

Case sensitivity not working

[A-Z] is a range of characters. The characters in the range depend on the sort order defined by the collation: SELECT SV.number, CHAR(SV.number) FROM master.dbo.spt_values AS SV WHERE ...
Paul White's user avatar
  • 86.4k
6 votes

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

Storing and processing 1TB of data with only 16GB RAM available may prove to be a challenge. 1GB per core is rather unbalanced, especially for this kind of workload. 8GB per core would be a much ...
Paul White's user avatar
  • 86.4k
6 votes

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

I recommend using a flavor of method 2 and splitting the search range into many target tables. 10000 tables is a good first attempt. For example, if you search for "012345678901" then your query would ...
Joe Obbish's user avatar
  • 32.5k
6 votes

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

If you typically search for file names, store them separately instead of embedded in fullpath. Might be better to store path, file name, and maybe extension separately depending on the queries. Will ...
user278912's user avatar
5 votes

Autocomplete too slow: possible optimizations?

The solution I went with is to build a "half-triangram" table which contains a pre-processsed version of all the model # substrings as suggested by Aaron Bertrand in the two following blog posts of ...
A_V's user avatar
  • 1,364
5 votes
Accepted

Search string data with special characters removed

Use an indexed computed column: CREATE TABLE dbo.Products ( ID integer NOT NULL UNIQUE CLUSTERED, [Name] varchar(10) NOT NULL PRIMARY KEY NONCLUSTERED, Code varchar(20) NOT NULL, ...
user230193's user avatar
4 votes

patindex parser

Your code does not work because of how the conditions are arranged in your nested CASEs. Take, for example, the first Description value in your output, PURE Santevia 6% /M33/E33/R33/. When passed ...
Andriy M's user avatar
  • 23.1k
4 votes

Matching a ] (closing square bracket) with PATINDEX using the "[ ]" wildcard

I have a probably terrible take on this from back when I had to do a lot of string splitting. If you have a known set of characters, make a table of them. CREATE TABLE dbo.characters ( character ...
Erik Darling's user avatar
  • 41.9k
4 votes

Find if any of the rows partially match a string

Use LIKE. To find a substring located in any part of the string: SELECT domain FROM mytable WHERE domain LIKE '%domain.com%'; This will do what you asked. But in your examples "domain.com" is always ...
Federico Razzoli's user avatar
4 votes

Find if any of the rows partially match a string

I think all you need is Locate function in MySQL as: SELECT domain_id,domain_name as a FROM site_core_antispam_banned_domain where Locate(domain_name,'subdomain.com')!=0; Hope this helps.
Tayyab's user avatar
  • 150
4 votes

Matching a ] (closing square bracket) with PATINDEX using the "[ ]" wildcard

I've seen approaches in the past to replace the offending character before searching, and putting it back in afterwards. In this case we could do something like: DECLARE @test NVARCHAR(MAX); DECLARE ...
George.Palacios's user avatar
4 votes

Matching a ] (closing square bracket) with PATINDEX using the "[ ]" wildcard

Since ] is only special in [...], you can use PATINDEX twice, moving ] outside of the [...]. Evaluate both PATINDEX('%[[{}:,]%', SourceString) and PATINDEX('%]%', SourceString). If one result is zero, ...
hvd's user avatar
  • 141
4 votes

TSQL - Finding Similar Values

you need to remember that in a like statement a _ is ANY single character, and a % is any number (including 0) characters on your insert for StaticFilesTbl if you use the search strings you wish for ...
Ste Bov's user avatar
  • 2,081
4 votes
Accepted

Use extra column to save an unaccented copy of a string instead of using unaccent module

You could store an unaccented version redundantly. And create index on that column to support your queries. Or, better yet, to avoid bloating your table with redundant data, create a functional index ...
Erwin Brandstetter's user avatar
4 votes
Accepted

postgresql matching or converting utf-8 variant strings

𝐋𝐈𝐅𝐄 vs LIFE is a case of Unicode "compatibility equivalence", which is defined in UAX#15 as: Compatibility equivalence is a weaker type of equivalence between characters or sequences ...
Daniel Vérité's user avatar
3 votes

ERROR: operator does not exist: text[] ~~ text

Simpler function for Evan's operator solution, and should be faster, yet: CREATE OR REPLACE FUNCTION like_in_array(_arr text[], _pattern text) RETURNS bool LANGUAGE sql IMMUTABLE AS 'SELECT EXISTS (...
Erwin Brandstetter's user avatar
3 votes
Accepted

ERROR: operator does not exist: text[] ~~ text

The Postgres manual suggests you might have a design issue: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a ...
Jack Douglas's user avatar
3 votes
Accepted

Using SELECT within to_tsvector call in CREATE INDEX

You don't. You can't create an index that references another table. To set the default see default-text-search-config Workaround You can however use functions in an index. And functions can ...
Evan Carroll's user avatar
  • 63.7k

Only top scored, non community-wiki answers of a minimum length are eligible