Questions tagged [like]
83 questions
27
votes
1 answer
Why does searching for LIKE N'%�%' match any Unicode character and = N'�' match many?
DECLARE @T TABLE(
Col NCHAR(1));
INSERT INTO @T
VALUES (N'A'),
(N'B'),
(N'C'),
(N'Ƕ'),
(N'Ƿ'),
(N'Ǹ');
SELECT *
FROM @T
WHERE Col LIKE N'%�%'
Returns
Col
A
B
C
Ƕ
Ƿ
Ǹ
SELECT…
Martin Smith
- 87,941
- 15
- 255
- 354
22
votes
5 answers
Select multiple values in LIKE Operator
I have a SQL query given below, I want to select multiple value using like operator.
Is my Query correct?
SELECT top 1 employee_id, employee_ident, utc_dt, rx_dt
FROM employee
INNER JOIN employee_mdata_history
ON …
l.lijith
- 918
- 4
- 9
- 27
21
votes
4 answers
How to do a case-insensitive LIKE in a case-sensitive database?
My vendor requires the data warehouse database to be case sensitive, but I need to do case-insensitive queries against it.
In a case-sensitive database, how would you write this to be case-insensitive?
Where Name like '%hospitalist%'
James
- 2,668
- 5
- 28
- 51
13
votes
3 answers
Overcome LIKE character length limitation
By reading this LIKE character length limitation here, it looks like I can't send a text longer than ~4000 characters in a LIKE clause.
I'm trying to fetch the query plan from query plan cache for a particular query.
SELECT *
FROM…
Dan Dinu
- 231
- 1
- 4
9
votes
4 answers
Matching left and right single-quotes used as apostophes
I have four columns containing names and want to search these using a LIKE in a Microsoft SQL Server environment.
The complication comes that names may include left and right single-quotes / angled apostrophes (i.e. ‘ and ’, char(145) and…
JLo
- 193
- 1
- 6
6
votes
3 answers
mysql - order by first condition in where clause
I have a table consists of the following fields
id, name
with the following values
1, ciro
2, test ciro
3, ciprox
4, other
5, other
i would like to get all the values that begin with "ci" or contain "ci" but show me the results before they start…
ciro
- 171
- 1
- 1
- 4
6
votes
4 answers
Counting occurrence of words in table is slow
Consider these simplified tables:
CREATE TABLE dbo.words
(
id bigint NOT NULL IDENTITY (1, 1),
word varchar(32) NOT NULL,
hits int NULL
)
CREATE TABLE dbo.items
(
id bigint NOT NULL IDENTITY (1, 1),
body varchar(256) NOT…
palloquin
- 61
- 1
- 2
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
6
votes
3 answers
Like predicate to match a whole word only
I have a SQLite database with a table named minecraft.
+----+----------------------+
| id | name |
+----+----------------------+
| 1 | Pocket Mine MP |
| 2 | Open Computers |
| 3 | hubot minecraft skin |
| 4 |…
Farhad
- 161
- 1
- 4
6
votes
2 answers
Postgresql: Pattern match against array elements?
Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have an aggregate function that, among other things, returns an array of elements, like:
SELECT
lognum
…
ibrewster
- 211
- 2
- 7
5
votes
1 answer
SQL server Cardinality Estimation for LIKE query
I have this statistics histogram vector for my non-clustered index made on LastName column of a table named AspNetUsers.
If I run a query as SELECT * FROM dbo.AspNetUsers WHERE LastName = 'Baker' it returns 6 rows as estimated rows, cause Baker is…
Dhanuka Jayasinghe
- 185
- 8
5
votes
1 answer
Use square brackets on extended events filter
I want create an extended events session and use the like_i_sql_unicode_string operator to filter the phrase [demo], with the square brackets.
I've started with:
CREATE EVENT SESSION [demo] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(
…
Jorge Bugal
- 218
- 1
- 6
5
votes
2 answers
Records greater than epoch timestamp using only LIKE operator
I have the following query so far and unfortunately, I cannot use regexp or greater than operators, I can only use the LIKE keyword.
The whole column is in a json string, I can't use json_value or regexp because I'm on SQL Server so I'm stuck with…
Elite298
- 61
- 6
5
votes
1 answer
How can I improve the performance of where clause with LIKE %abc?
I had below select statement that turned out very slow (41s)
SELECT DISTINCT a.Doc_Resource_ID
,a.Parent_ID
,a.Logical_Path
,a.lvl
,CAST(SUBSTRING(a.Security_Level, 21, 1) AS TINYINT)
FROM Doc_ACL_Detail_D a
…
HChau Le
- 59
- 2
5
votes
1 answer
Repeating pattern X amount of times in LIKE
I took a look at the documentation for patterns from Microsoft (can be found here), and from what I understood, it doesn't say a way for a pattern to repeat a limited number of times. It's either the wildcard (which goes on indefinitely) or looking…
Salmononius2
- 441
- 2
- 6
- 15