Questions tagged [like]
The like tag has no usage guidance.
82 questions
0
votes
0
answers
27
views
Purely in performance terms, how do CONTAINS([MyCol], 'foo') and [MyCol] LIKE '%foo%' compare?
Consider CONTAINS([MyCol], 'foo') and [MyCol] LIKE '%foo%'. Assume the best possible indexes on [MyCol], whatever those may be (at minimum, we need a unique key and a full-text index). I know that the ...
- 823
-2
votes
3
answers
63
views
Query to find values based on a like and a not like
I have a column in a table: ID - this column can have a value of '123456' but can also have value '123456x'.
I need to return all the numbers where there is an 'x' and the same number but without an '...
-2
votes
1
answer
49
views
I want to assign a character value to a field that contains numbers without using CAST or CONVERT so as to avoid cardinality estimate warnings
I'm looking at something like
SELECT CASE WHEN MyField LIKE [0-9] THEN 'xyz'
WHEN MyField LIKE [10-99] THEN 'abc'
WHEN MyField LIKE [100-999] THEN 'def'
WHEN MyField LIKE [1000-9999] THEN 'ghi'
ELSE [...
- 1
0
votes
2
answers
163
views
Find rows with given coordinates nested in a JSON column
I have a table public.polygon_versions in my PostgreSQL 14.7 database:
CREATE TABLE public.polygon_versions (
id bigint NOT NULL DEFAULT nextval('polygon_versions_id_seq'::regclass),
entity_id ...
- 157
1
vote
1
answer
2k
views
How can I query using like on json field in PostgreSQL?
I've set up the following DB Fiddle: https://dbfiddle.uk/mzsp-4tg
How should I make a correct SQL query to use like on geo_json field of json type to get table records with string matching coordinates ...
- 157
5
votes
1
answer
321
views
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 ...
5
votes
1
answer
948
views
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
...
- 218
5
votes
2
answers
2k
views
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 ...
- 61
0
votes
1
answer
2k
views
LIKE query with leading wildcards in large MySQL production database
According to insights in Amazon RDS the following query in a large MySQL production database is causing high loads ( ~ 50.000.000 entries):
SELECT * FROM entities WHERE status='ready' AND user_id='...
- 225
1
vote
1
answer
196
views
Query hierarchical file permissions
I have a table representing some files with their paths and another table with file sharings by user and files.
Below is Table files, simplified. Example:
I have a directory A (path /A) containing ...
- 13
0
votes
1
answer
3k
views
Postgresql: ILIKE query with % as search term
I have a JDBC query that performs an ILIKE operation.
select project_name from project_table where project_name ILIKE '%test%';
The above returns all the results matching (wildcarded) the term test.
...
1
vote
2
answers
2k
views
Only SELECT rows from table when two rows from the same table fulfill the condition as a whole under the same ID
Title is a bit weird, but I am not quite sure how to formulate this problem quite right. Maybe this is why I cannot find the solution...?
I am currently crawling through a SQLite database which has ...
- 13
5
votes
1
answer
1k
views
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, ...
- 59
1
vote
1
answer
336
views
Postgresql (11.9) LIKE query not using text_ops index
I've seen related questions but I think this should be working and isn't. I have a text_ops function based index on column to do LIKE searches, but it only picks up the index with an equals:
CREATE ...
- 111
0
votes
2
answers
395
views
like in exist() xml mssql
By following question: See if XML element exists at any level in document with a specific value
Is there a possible solution to implement like clause within exist?
For example I have similar structure ...
-2
votes
1
answer
50
views
SQL QUESTION_Case When + LIKE % [closed]
I get an error but I don´t understand why.
Does this make sense in SQL?
I need the column ValidNaming to be filled with False/True if the customer (or field CommonLastName) has an invalid name.
Why ...
1
vote
2
answers
232
views
Return results with several LIKE and NOT LIKE requirements
I am trying to create a query that selects only a set of values, but excludes other ones.
The column I am selecting it's called "Participants" and the values therein are not a single text ...
- 13
1
vote
0
answers
141
views
Search by first and last name of the Instructor
I have the below tables in the MySQL database
create table course_class
(
id varchar(36) not null primary key,
class_code varchar(255) not null,
...
- 119
0
votes
1
answer
3k
views
Find partial match with textsearch query
I have the following query that I did hard looking on this site:
SELECT *
FROM renns
WHERE
(
to_tsvector('english', coalesce(numar_admin, '')) ||
to_tsvector('english', coalesce(...
- 101
0
votes
1
answer
3k
views
How to filter a character followed by numbers string pattern in PostgreSQL?
Back in SQL Server, I use the command below to get the matching string pattern of #1234 from the column called Number
SELECT [m].[Number]
FROM [Boats] AS [m]
WHERE [m].[Number] LIKE N'[#][0-9][0-9][0-...
- 147
0
votes
1
answer
38
views
SQL Server 2017: OR and LIKE Interaction
I am using SQL Server 2017 (i.e. v14).
I could use one of the following equivalent criteria in a query:
x IN ('104','504','554')
(x='104' OR x='504' OR x='554')
(x='104' OR x Like '5[05]4')
If I use ...
- 1
1
vote
1
answer
546
views
Count numbers in scientific notation (E-notation)
I'm trying to check if a part number field has scientific notation values and came up with this simple solution:
SELECT count(*) as overall,
count(*) filter (where partnumber ilike '%E+%') as ...
- 141
1
vote
1
answer
5k
views
Postgres HAVING ilike any wildcard in array_agg
I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is:
SELECT p.id, p.name, array_agg(vo.name)
FROM product p
LEFT ...
- 23
0
votes
1
answer
554
views
Mysql, Getting all rows in which field ends in a specific character, and another field exists that is the same but doesn't end in that character
I need to get all rows which end in a specific character, P for example, but in which a similar key sans the P exists. I have no idea how to approach this one in MySQL.
This is very small example, my ...
- 127
-1
votes
1
answer
66
views
Comparing the result of a subquery with LIKE
I need to find all the movies that have all of its actors' names to start with an A and have a 60.000.000 or more of gross. I have this but is not working
SELECT title, imdb_score
FROM Movie AS m
...
- 1
1
vote
2
answers
7k
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 ...
- 259
0
votes
3
answers
5k
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 ...
3
votes
2
answers
1k
views
Find rows in a single table where values in a certain column are near-duplicates
I have a Postgres 11 table on RDS containing a column email; some of the values in this column [and only that column] are clearly de facto duplicates but differ in case, i.e., different capitalization,...
3
votes
1
answer
853
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 ...
- 1,063
3
votes
1
answer
351
views
Closing bracket ']' in LIKE wildcard
I have a query that looks a bit like this:
SELECT * FROM Stuff WHERE name LIKE '[a\]]%'
My goal is to match all names starting with a or ] (closed bracket). However, this seems to match everything ...
-2
votes
1
answer
43
views
Should I use the LIKE predicate or fetch all rows and process them on the client?
I am optimizing my application, while this task, I find one query which is fetching data from database using like .
and database rows are around 10000.
So is this a good way to use like in query or I ...
6
votes
4
answers
3k
views
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 ...
- 61
4
votes
1
answer
424
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
...
- 203
0
votes
1
answer
92
views
Planning and Optimizing the table structure InnoDB MySql
I am planning to have an InnoDB MySql table with tens of millions of rows.
Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or ...
- 147
1
vote
1
answer
2k
views
How to search id from one table in another table's nvarchar column
I have 2 tables
table1 ( id int, name nvarchar)
table2 ( pid int, parameters nvarchar)
parameters in table2 contains table1.id in nvarchar format, like
"some text 12345 some text", where 12345 is ...
- 2,142
1
vote
1
answer
8k
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....
- 113
0
votes
2
answers
497
views
SSRS - select where parameter and fixed value
So there is a lot of stuff about using SSRS parameters to select multiple values but nothing about using both a parameter and a fixed value using the in select statement.
So what I'm trying to do is ...
- 173
21
votes
4
answers
108k
views
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-...
- 2,650
-2
votes
4
answers
258
views
What is the implicit ordering of a LIKE condition?
I would like to know how the LIKE operator is classified, I have a query where I have a number of chained LIKE statements. How are the results from the following prioritized when they're returned?
...
- 299
1
vote
1
answer
2k
views
Join tables based on variable value contained in column
I have two tables with dates as the primary column to join. But, I also need to join based on the value from one column that is partially contained in a column from the other table.
Table 1: date, ...
- 13
1
vote
2
answers
3k
views
Slow query performance when searching for a particular value, but fast with a different value on the same column
I have a SQL Server 2017 select statement that is executed by the front end application. The query has three joins and UNION. Each join statement has a clause where NAME LIKE '%ibm%'.
When I change %...
- 179
0
votes
1
answer
151
views
Just 4 Table Join query takes too much time and some time fail to execute
I am working on getting candidate detail from mysql database and for that i have created one query.
Is my query structure is wrong?
Here is the query:
SELECT `candidate`.`id`, `candidate`.`email`,...
- 137
1
vote
1
answer
8k
views
Postgres collate example in select?
I would like to comprehend the different behavior of like and =. Sadly, I can not reproduce the following simple example thus that both queries return a different result:
SELECT 'ä' LIKE 'ae' COLLATE ...
- 113
6
votes
4
answers
34k
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 ...
- 64.6k
1
vote
1
answer
4k
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 ...
- 13
3
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 ...
- 145
1
vote
2
answers
470
views
SQL regex PostgreSQL
Let's consider a table like that:
id | name
1 | This is a test sentence
2 | is a sentence TeST
3 | This a IS test sentence
4 | iS a tEst sentence tHis
5 | This a test sentence is
How ...
- 15
0
votes
1
answer
258
views
Search for the root words using root words with prefixes or suffixes
Is it possible to search the word "FILL" saved in the SQLite database using the words "REFILL" or "FILLED"? Not just these words specifically but you get the idea of searching the root word using the ...
- 1
0
votes
3
answers
2k
views
Postgressql text search: pg_trgm index with order by going very slowly
I have a really slow query(~30 seconds) on a large text search DB that I could really use some help with.
I'm searching for ordered text, in a long file path string. So the path could be: folder1/cat ...
- 99
13
votes
3
answers
1k
views
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 ...
- 231