For questions about searching a string for matches
Questions tagged [string-searching]
85 questions
149
votes
8 answers
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 there is a way to rewrite this to become more…
Lucas Kauffman
- 1,835
- 4
- 17
- 18
20
votes
3 answers
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 'running%' vs anywhere in the string '%running%.
I…
danielson317
- 343
- 2
- 11
13
votes
4 answers
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 query that will return "domain.com" as it is a…
Emanuel
- 263
- 1
- 3
- 10
12
votes
7 answers
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 baraye'
+ ' 1 saat anja bud va sepas...…
Ario
- 1,052
- 4
- 14
- 27
9
votes
5 answers
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 single characters and they include these:
{ } […
Andriy M
- 23,261
- 6
- 60
- 103
8
votes
1 answer
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? Is there one option which always outperforms…
Bassmanjase
- 83
- 1
- 1
- 3
8
votes
2 answers
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 string and a 9-digit-substring using SQL Server,…
Werner Aumayr
- 181
- 5
7
votes
1 answer
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 languages, each one with tags (keywords),…
melokki
- 93
- 1
- 8
6
votes
4 answers
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 ANY(ARRAY['foo%', 'bar%', 'baz%'];
My question is…
Evan Carroll
- 65,432
- 50
- 254
- 507
5
votes
1 answer
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 issues matching variant characters, consider
-- This works…
unomi
- 153
- 4
5
votes
1 answer
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…
user3050153
- 67
- 2
4
votes
1 answer
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 'NOT LIKE'.
USE master;
DECLARE…
Bryan Rebok
- 1,219
- 9
- 16
4
votes
3 answers
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 index and is very slow compared to doing a fullpath…
Steven M
- 53
- 5
4
votes
3 answers
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 '-'
THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)
The…
Daylon Hunt
- 189
- 1
- 4
- 16
4
votes
1 answer
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
declare @container table(
[Response] xml not…
Zephyr
- 203
- 2
- 5