Questions tagged [like]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-2 votes
3 answers
48 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 '...
Daniel Blake's user avatar
-2 votes
1 answer
43 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 [...
Eyespi20's user avatar
0 votes
0 answers
25 views

SQL Select based on pattern

I have a SQL column in my system that has a bunch of file names. I am attempting to separate the file names based upon a matching file name pattern. Some files have a middle initial and some dont. ...
user1879840's user avatar
0 votes
2 answers
119 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 ...
Taras's user avatar
  • 147
1 vote
1 answer
835 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 ...
Taras's user avatar
  • 147
5 votes
1 answer
283 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 ...
Dhanuka Jayasinghe's user avatar
5 votes
1 answer
549 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 ...
Jorge Bugal's user avatar
5 votes
2 answers
1k 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 ...
Elite298's user avatar
-1 votes
1 answer
1k 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='...
Blackbam's user avatar
  • 215
1 vote
1 answer
164 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 ...
Olivier's user avatar
  • 13
0 votes
1 answer
2k 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. ...
underachiever's user avatar
1 vote
2 answers
1k 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 ...
Nirusu's user avatar
  • 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, ...
HChau Le's user avatar
1 vote
1 answer
146 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 ...
nfdavenport's user avatar
0 votes
2 answers
260 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 ...
Fatdrfrog's user avatar
-2 votes
1 answer
49 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 ...
Marina Olaechea's user avatar
1 vote
2 answers
58 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 ...
Geo_explorer's user avatar
1 vote
0 answers
102 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, ...
Swapnil's user avatar
  • 119
0 votes
1 answer
2k 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(...
CoderCoder42's user avatar
0 votes
1 answer
2k 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-...
hunterex's user avatar
  • 147
0 votes
1 answer
35 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 ...
Hopkins's user avatar
1 vote
1 answer
485 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 ...
Matias's user avatar
  • 141
1 vote
1 answer
4k 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 ...
koi's user avatar
  • 23
0 votes
1 answer
431 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 ...
J. M. Becker's user avatar
-1 votes
1 answer
54 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 ...
david's user avatar
  • 1
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
0 votes
3 answers
4k 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 ...
Hamza Khanzada's user avatar
3 votes
2 answers
689 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,...
Mister October's user avatar
3 votes
1 answer
785 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
253 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 ...
Svízel přítula's user avatar
-2 votes
1 answer
41 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 ...
Dupinder Singh's user avatar
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 ...
palloquin's user avatar
4 votes
1 answer
408 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
0 votes
1 answer
82 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 ...
Acidon's user avatar
  • 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 ...
igelr's user avatar
  • 2,120
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
0 votes
2 answers
450 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 ...
Exostrike's user avatar
  • 173
19 votes
4 answers
99k 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-...
James's user avatar
  • 2,612
-2 votes
4 answers
248 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? ...
Lucy's user avatar
  • 299
1 vote
1 answer
1k 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, ...
Steve L's user avatar
  • 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 %...
Data Engineer's user avatar
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`,...
always-a-learner's user avatar
1 vote
1 answer
7k 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 ...
Stefan K.'s user avatar
  • 113
5 votes
4 answers
30k 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
  • 62.7k
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
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
1 vote
2 answers
468 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 ...
Roots's user avatar
  • 15
0 votes
1 answer
236 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 ...
Niel Lat's user avatar
0 votes
3 answers
1k 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 ...
dessalines's user avatar
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 ...
Dan Dinu's user avatar
  • 231