Menu
  • HOME
  • TAGS

How to match text ending with a text on DB2?

sql,db2,sql-like

DB2 can match patterns at the end of the string. The problem is probably that there are other characters. You can try: WHERE rtrim(MYFIELD) LIKE '%B' You can also look at the lengths of the field and delimit the string value to see if there are other characters: select length(MyField),...

Select ___where __LIKE ___

mysql,sql,database,select,sql-like

The underscore (_) is a single character wildcard in SQL's LIKE operator. If you want to treat it as a regular charater, you'll have to explicitly escape it: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='DB_NAME' and TABLE_NAME NOT LIKE '%\_%' ESCAPE `\`; ...

c# sql search with LIKE

c#,sql,sql-like

You missed one combination that will work: string strSQL = "SELECT * FROM TB_CA_gigs WHERE Artist LIKE '%" + Artist + "%'"; And as wahwahwah said, as I'm sure others will too: as a good practice, learn and use parameterized queries to avoid SQL injection vulnerabilities. For a simple example...

Why do we need the GLOB clause in SQLite?

sql,sqlite,android-sqlite,glob,sql-like

Case sensitivity is useful by itself, because this works better with normal indexes. Additionally, GLOB supports character classes: Globbing rules: * Matches any sequence of zero or more characters. ? Matches exactly one character. [...] Matches one character from the enclosed list of characters. [^...] Matches one character not in...

Sql Server's regex LIKE - behaviour clarification?

sql-server,sql-like

Any particular use of [set] within a LIKE expression is a check against one character in the target string. So, LIKE '%[0-9.]%' says - % - match 0-to-many arbitrary characters, then [0-9.] match one character in the set 0-9., and then % match 0-to-many arbitrary characters. Paraphrased, it says "match...

SQL find same value on multiple filelds with like operator

mysql,sql,search,like,sql-like

You are using the LIKE operator for first_name and last_name as follows: LIKE '%Natalie Fern%' This will only match strings which contain anything followed by 'Natalie Fern' followed by anything. But since the first and last name columns only contain (surprise) the first and last names, your query isn't matching...

Efficient MySQL text search

php,mysql,search,sql-like

An index on the column will help, even using the like operator, but not when you have a wildcard at the start too. So for term% an index will be beneficial, but for %term% it will not. But instead, you may have a look at FULLTEXT indexes. If you add...

Cross-database prepared statement binding (like and where in) in Golang

sql,go,prepared-statement,sql-like,where-in

What is the cross-database way to bind arguments? With database/sql, there is none. Each database has its own way to represent parameter placeholders. The Go database/sql package does not provide any normalization facility for the prepared statements. Prepared statement texts are just passed to the underlying driver, and the driver...

Cant use LIKE to extract data from mysql using php

php,mysql,sql-like

Change $sql="SELECT* FROM gamestbl WHERE game_name LIKE '%.$search.%' OR game_description LIKE '%.$search.%'"; For $search = mysql_real_escape_string($search); // Prevent from injection $sql = sprintf("SELECT * FROM gamestbl WHERE concat(game_name, game_description) LIKE '%s'", '%' . $search . '%'); sprintf is the better way to add variable in a string and concat will...

Blanks in LIKE function to validate UK postcode? [duplicate]

sql,validation,ms-access-2010,sql-like

This is a function which I have used in the past, not sure if it is the best. However I know it work(ed)s. So you might want to give it a try. http://mikeperris.com/access/VBA-code-validate-UK-postcode.html The Author of the code uses a RegEx patter to match the post code based on its...

Using CASE and LIKE to evaluate - logic issues

sql-like,case-statement,proc-sql

It looks like you're using the LIKE keyword when you really meant to say CONTAINS.

how to do a like query using mgo package for golang

mongodb,go,sql-like,mgo

Use bson.Regex to specify a regular expression value: sess.DB(db).C(cApp). Find(bson.M{"permalink": bson.RegEx{"org.*", ""}}). All(&m) ...

MySql like with %

php,mysql,sql-like

Your LIKE clause should be like '".$value."%'"); No { or }, and no need for all the extra concatenation. So the entire command would be $sql=mysql_query("Select username from users where username like '".$value."%'"); That said, the mysql_* functions are deprecated, and you are wide open to SQL injection, so you...

Query in Access using NOT LIKE

sql,ms-access,sql-like

What you actually want is AND in between: SELECT * FROM TableName WHERE ([Material Description] NOT LIKE '*LICENSE*' AND [Material Description] NOT LIKE '*LICENCE*'); You will currently select the record "LICENSE" because it does NOT contain "LICENCE" and the record "LICENCE" because it does NOT contain "LICENSE". The records you...

LIKE to select only the exact keyword

php,mysql,sql-like

You can use FIND_IN_SET for that select * from your_table where find_in_set('java', tags) > 0 But actually you should rather change your table definition. Never store multiple values in a single column. A better DB design would be posts table ----------- id title body ... tags table ---------- id name...

NSPredicate for SQL 'Like'

ios,nspredicate,azure-mobile-services,sql-like

From Apple's NSPredicate Class Reference Simple comparisons, such as grade == "7" or firstName like "Shaffiq" Case and diacritic insensitive lookups, such as name contains[cd] "itroen" Logical operations, such as (firstName like "Mark") OR (lastName like "Adderley") You can create predicates for relationships, such as: group.name like "work*" Also see...

When do we use the _ (underscore) for sql select query?

mysql,sql,sql-like

It matches any single character. The only time I ever use this in practice is when I want to find strings which contain a substring but don't end in that substring. WHERE SomeCol LIKE '%foo_%' ensures that at least one character follows "foo"...

MySQL check if row with category exists

mysql,sql,sql-like,where-in

I don't think you like to use the LIKE operator since it only searches for patterns within a column and you only wan't to return rows that really has one of your categories. Instead you should use an IN clause to check if a job has one of your categories:...

MySQL count and list the occurrence using like from another table

mysql,procedure,sql-like

Add another table. How about: users table -------------- | id | name -------------- | 1 | abc | 2 | def | 3 | ghi cars table -------------------------------- | id | name | description -------------------------------- | 1 | BMW | Good Choice | 2 | Ferrari | better choice |...

How to use two LIKE conditions?

oracle,sql-like

WHERE clause should be: WHERE (Lower(FirstName) LIKE '%a%m%' OR Lower(FirstName) LIKE '%m%a%') AND Lower(LastName) NOT LIKE '%a%' AND Lower(LastName) NOT LIKE '%m%' You were only including cases where FirstName had an a followed by an m. Similarly, you were only excluding the cases where LastName had both an a AND...

Does SQL LIKE iterate every row of table

mysql,sql,search,sql-like

If you do: where username like 'G%' and there is no index, then MySQL will scan every row. There is really no concept in SQL of "sorted tables". There is a more powerful concept of indexes. If you have an index where username is the first (or only key), then...

is LIKE, but with int representing an string on which should be checked

php,sql,filter,mariadb,sql-like

Since you saved the mapping of ints to strings for the kwaliteit values serialised as text, it's not possible to select based on the text/label in MySQL. What you could do, for example, is create a new table Kwaliteit, with 2 columns, id and label. Then you could join that...

filter table mysql using multiple colums also between

php,mysql,filtering,between,sql-like

Try this: SELECT * FROM quick_inquiry WHERE (inquiry_time BETWEEN '$start' AND '$end') AND inquiry_language LIKE '%$language%' AND service_country LIKE '%$country%' AND category LIKE '%$category%' AND replied LIKE '%$replied%' Also you can use >= and <= SELECT * FROM quick_inquiry WHERE inquiry_time >= '$start' AND inquiry_time <= '$end' AND inquiry_language LIKE...

SQL and LIKE with alias

mysql,sql,sql-like

Just refer to the original column and not its alias ? SELECT i.tag as mytag, t.id as myid FROM tags as t, images as i WHERE i.id = 15 AND i.tag LIKE concat_ws(";", "%", t.id, "%") ...

How to ignore left padding in a LIKE statement?

sql,sql-like

Functions in the where clause tend to be slow. Something like this might be quicker: where id like '123%' or id like '% 123%' ...

How use like condition in mysql with more words

php,mysql,html-form,sql-like

Use explode and implode with OR condition for each words. $descriptionArr = explode(" ", $description); if(!is_null($descriptionArr)) { foreach($descriptionArr as $search) { $descriptionQuery[] = " description LIKE '%{$search}%' "; } $condition = " WHERE " . implode(" OR ", $descriptionQuery); } $sql = "SELECT * FROM table {$condition}"; ...

Searching for any word or part of it inside string

postgresql,search,sql-like

I've used a function for search. Where p_name is a text input as 'lorem ipsum dolor es'. It finds patient records with name which includes all the words as a substring. SELECT * FROM patient WHERE patient.name LIKE ALL (SELECT DISTINCT '%' || name || '%' FROM UNNEST(STRING_TO_ARRAY(p_name,' ')) AS...

sqlite3 compound index usage for LIKE and GROUP BY

sql,sqlite3,group-by,sql-like

> CREATE TABLE mytable(foo, boo, [...]); > CREATE INDEX bfi ON mytable(boo, foo COLLATE NOCASE); > EXPLAIN QUERY PLAN SELECT foo, boo FROM mytable WHERE foo LIKE 'hi%' GROUP BY boo; 0|0|0|SCAN TABLE mytable USING COVERING INDEX bfi ...

How to filter SQL by time (greater and less than)?

sql,sqlite,sql-like,spatialite

Thanks to your posts and this answer I came up with this solution: SELECT * FROM data WHERE DATETIME( substr(time,1,4)||'-'|| substr(time,6,2)||'-'|| substr(time,9,2)||' '|| substr(time,12,8) ) BETWEEN DATETIME('2014-04-25 18:00:00') AND DATETIME('2014-04-25 19:00:00'); ...

SQL LIKE exclusive substring

sql,database,sql-like

You have made a fundamental mistake in the design of the database. The correct solution is to replace the column column with a separate table. This table will have two columns, the user ID and the product ID and will have one separate row for each product a user has...

Use variable in sqlalchemy ilike statement

python,sqlalchemy,sql-like

replace "%received_input%" with '%{}%'.format(received_input)

Innacurate query with LIKE statement

mysql,mariadb,sql-like

Based on your code, and your comment showing the issue, it looks like when last and first are empty on the form, they are still being passed through to the script. This means that the isset() check is passed, but they have no value (ie, empty string, ie, ''). When...

VBA Like Operator

vba,pattern-matching,outlook-vba,sql-like

Look for the exceptions, then compare with the wildcard: Select Case UCase$(Left$(subject, 3)) Case "RE:", "AW:": '// is reply Case Else If subject Like "*XXX*YYY*" Then MsgBox "Hello" End If End Select ...

How to filter based on the alias instead of the column name?

sql,database,ibm,informix,sql-like

You can wrap your query in another SELECT and use alias from emp_name the first SELECT as a field name in it : SELECT x.* FROM (SELECT a.emp_num , a.emp_num ||'-'|| a.name AS emp_name FROM Employees a ) x WHERE x.emp_name LIKE '' If you don't want to use a...

how to use like clause for a value saperated with comma in oracle

sql,oracle10g,sql-like

I have used regexp_like instead of only like and it worked for me.

SQL Like statement with regular expressions

mysql,regex,sql-like

You might not need regex for this Set @YourNUmber := 110; SELECT * FROM Table WHERE ';' + ids + ';' LIKE '%;'+ @yourNumber + ';%' This guarantees there are always ; surrounding all the numbers. This is formatted for SQL Server. The variable syntax and wildcards might be different...

What is the best way to find string containing a sequence of identical digits in SQL? (fake phones search)

sql,sql-server,query-optimization,sql-like

Update: To find recurring numbers, you can use a function like this. It returns 1 if any of the characters in @text are used at least @min times consecutively. create function IsRecurring(@text varchar(255), @min int) returns int as begin declare @i int = len(@text) - @min + 1 declare @result...

Trying to join Access tables with like statement with list in field

sql,ms-access,sql-like

You can use the Instr Function that tests if a string exists in other string as below: Select [Table1].CWT, [Table1].OtherColumn, [Table2].Column1Needed,[Table2].Column2Needed From [Table1], [Table2] Where Instr([Table2].TAG_NO,[Table1].CWT)>0 See http://www.techonthenet.com/access/functions/string/instr.php...

How to check exact integer value in string mysql

mysql,regex,sql-like

If the pattern is same i.e. the amount is at the end and separated with a space you can use substring_index function mysql> select substring_index('BUY ABOVE 200',' ',-1) as amount; +--------+ | amount | +--------+ | 200 | +--------+ 1 row in set (0.00 sec) So you may do the...

“NOT LIKE x” and “LIKE x” doesn't sum up?

sql,oracle,sql-like,like-operator,oracle12c

If mycolumn has some rows with NULL values, those will be excluded from both LIKE and NOT LIKE clauses. Therefore, those 2 statements should be equal: SELECT (select count(*) from mytable where mycolumn like '__________') + (select count(*) from mytable where mycolumn not like '__________') + (select count(*) from mytable...

Why is like-operator so fast

sql,database,algorithm,sql-like

I have no idea what engine you are using and what's beneath its actual hood but here is some helpful information regarding this problem: Often, SQL engines uses free text search inside the column to be able to extract queries like that extra fast. This is done by creating an...

Get records with blank field value using LIKE statement

sql,sql-like,like-operator

Try this where clause. Use OR condition to accept the empty string where (Book_Author LIKE '*" & TextBox.Text & "*' or Book_Author = '') ...

SQL Code With LIKE WildCard Doesn't Work Properly

php,mysql,sql-like

check your code that their set the value of $search_text it might be blank so it returns all rows .

MySql single column index or multi-column index in this case

mysql,indexing,sql-like

For this query: SELECT placeId FROM places WHERE placeName LIKE '$testStr%' OR placeNameEnglish LIKE '$testStr%'; MySQL could use two indexes, one on places(placeName) and one on places(placeNameEnglish). The operation is a called index merge (see here). I wouldn't count on it. This query cannot fully use a composite index. You...

How to use LIKE and wildcards in mysql statements

php,mysql,select,sql-like

You could use MySQL function FIND_IN_SET: SELECT * FROM tbl WHERE FIND_IN_SET('16', REPLACE(kategorien, ';', ','))>0 however, it is usually not a good idea to store comma separated values in a single field, please have a look at this question: Is storing a delimited list in a database column really that...

SQL Server LIKE search with [ ] not matching as expected

sql-server,tsql,sql-server-2012,sql-like

It is an issue with character escaping. Square brackets have to be handled properly because otherwise are interpreted as special chars like % is; using square brackets you match any of the char inside the brackets and that's why the first CASE always matches. The solution is to to declare...

Emulate a SQL LIKE search with ElasticSearch

elasticsearch,sql-like

I would do it like this: change the tokenizer to edge_nGram since you said you need LIKE 'CityName%' (meaning a prefix match): "tokenizer": { "autocomplete_edge": { "type": "edge_nGram", "min_gram": 1, "max_gram": 100 } } have the field specify your autocomplete_search as a search_analyzer. I think it's a good choice to...

ruby like query error

mysql,ruby-on-rails,ruby,sql-like

Looking at the log file and below trace :- Parameters: {"outlet"=>{"query"=>"life"}} I found the issue. You need to do @query = params[:outlet][:query]....

MySQL Like query not recognising anything but first word in database

php,mysql,string,sql-like

Okay, some security lessons in here. Bind the parameters $gen (with the wildcards added) and $datepicker in the prepared query. Since you can't bind column or table names, I'd run something like I did below with $tab and the allowed $tables array. This allows you to set a predefined list...

SQL using LIKE operator with variable

sql,sql-like

Use double percent signs to escape the percent signs in the string, and remove the extra apostrophes around the value: sprintf(query, "UPDATE Vote " "SET choice = '%s' " "WHERE choice LIKE '%%%s%%'", newVote, originalVoteContains); ...

Join five mysql tables for search

php,search,mysqli,sql-like,jointable

You join your table based on file_id in the following way.And you can also search by id.just putting your search id at Your_search_file_id place. select *from conference inner join journal on conference.file_id =journal.file_id inner join project_thesis on project_thesis.file_id =journal.file_id inner join research on research.file_id =journal.file_id where journal.file_id = Your_search_file_id; ...

LIKE operator in SQL for my Access Database returns no values in ASP CLASSIC while it does if the query gets copied directly in Access

sql,ms-access,asp-classic,sql-like

Figured it out myself. Seems that I only had to replace the * wildcards with the % wildcard. This did the trick..

Looking to find a number embedded in a string at some random place - MS SQL Server 2008 R2

sql,sql-server,sql-like

Try the following to select only fields with 6 consecutive digits select * from LegacyTable where Comment like '%[0-9][0-9][0-9][0-9][0-9][0-9]%' To extract those six digits from the string select Comment substring(Comment,PatIndex('%[0-9][0-9][0-9][0-9][0-9][0-9]%',Comment),6) as nbr from legacytable where Comment like '%[0-9][0-9][0-9][0-9][0-9][0-9]%' ...

how to do like query in MySQL find second word in string

mysql,regex,wildcard,like,sql-like

use regexp in your sql query .. something like select * from table where col regexp '^[A-Za-z]+\s(foo).*$' EDIT used your table .. try this .. select id,col from food where col regexp '^[A-Za-z]+ (foo)' ...

MySQL using OR with MATCH AGAINST slows query drastically

mysql,performance,full-text-search,sql-like,full-text-indexing

Though MySQL 5.0+ now supports Index Merge, there currently is this limitation: Index Merge is not applicable to full-text indexes. We plan to extend it to cover these in a future MySQL release. Typically, you can rewrite these into UNION queries: SELECT * FROM exampleTable WHERE MATCH(someColumn) AGAINST('+testing123*' IN BOOLEAN...

Case query based on two input values

sql,sql-server,select,case,sql-like

Use CASE condition in JOIN condition. select a.*, b.quantity from fruits a left join fruitSales b on CASE WHEN a.fruit IN ('Apple', 'Banana') AND a.fruitPrice = '%'+b.fruitPrice+'%' THEN 1 WHEN a.fruit NOT IN ('Apple', 'Banana') AND a.fruitPrice like '%' + b.fruitPrice + '%' THEN 1 ELSE 0 END = 1...

“Like” operator in inner join in SQL

sql,join,sql-like

A bit of an odd data model aside, you've turned the tables around in the LIKE part (table1.name should be a part of table2.name, not the other way around), and you need to add the percents to the value, not the name of the field, that means not quoting the...

Finding a Specific Character and/or String SQL Server 2008

sql,sql-server,string,sql-like

Your query is fine, you just missed one % in it. Instead of this WHERE A_EMAIL LIKE ('and %') OR A_EMAIL LIKE ('or %') OR B_EMAIL LIKE ('and %') OR B LIKE ('or %') you should use this WHERE A_EMAIL LIKE ('%and %') OR A_EMAIL LIKE ('%or %') OR B_EMAIL...

Error 1064# While using mysql Like keyword

mysql,sql,mysql-error-1064,sql-like

You can escape a single quote (') by doubling it (''): select emp_id FROM my_table where classes like '%''XII''%'; Note: These are two single quotes, not a double quote....

SQL Server 2008 AND/OR operators and parentheses

sql,sql-server,select,where,sql-like

If you absolutely must have id = 111 then this is an and relationship, not an or relationship. You can then have a series of or operators between the other conditions: WHERE ID = '111' AND (charge_desc LIKE '%garn%' OR charge_desc LIKE '%levy%' OR charge_desc LIKE '%exe%') ...

MySQL JOIN two tables using LIKE and substrings

mysql,join,sql-like

You have just switch your fields in ON clause: ON wp_prop.field_313 LIKE CONCAT('%', trim_table.trim_value, '%') http://sqlfiddle.com/#!9/616a0/1 SELECT trim_table.id as post_id, trim_table.trim_value as post_name, wp_prop.id as prop_id, wp_prop.field_313 as prop_name FROM ( SELECT id, TRIM(SUBSTRING(post_title, LOCATE('@', post_title)+1)) as trim_value FROM wp_nd333j_posts WHERE post_type="fplan" ) as trim_table INNER JOIN wp_nd333j_wpl_properties as wp_prop...

T-SQL LIKE using variable and index

sql,sql-server,sql-server-2008,tsql,sql-like

There is another choice you didn't list. You can use the OPTIMIZE FOR option to force the query optimizer to make the correct assumption about the nature of the expected variable values. This seems to match your need very well. DECLARE @starts nvarchar(100) SET @starts = 'starts%' SELECT * FROM...

USE % WILD IN WHERE CLAUSE

mysql,sql,mysqli,where-clause,sql-like

Table data doesn't match what you are searching for. What you can do is, separate each name as different conditions: SELECT * FROM DATA WHERE NAME LIKE '%john%' AND NAME LIKE '%jack%' AND NAME LIKE '%steve%' ...

MySQL LIKE pattern matching returning empty set when it should be returning values

mysql,sql,wildcard,sql-like

Use a regular expression instead of a SQL Server style LIKE pattern: SELECT * FROM tableName WHERE description REGEXP '^..[aeiou].*$'; EDIT: For those who don't read documentation thoroughly, the documentation says: The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match...

Cassandra and advanced queries: Spark, ElasticSearch, Sorl

elasticsearch,cassandra,apache-spark,sql-like,like-operator

Both elasticsearch and solr fits your needs. They use lucene library to perform reverse indexing and much more -- Datastax enterprise (commercial distribution of Cassandra) offer this solution integrating solr natively. One more solution (little different but working) is to integrate infinispan which offers both integration with Cassandra repository and...