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),...
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 `\`; ...
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...
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...
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...
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...
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...
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...
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...
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...
sql-like,case-statement,proc-sql
It looks like you're using the LIKE keyword when you really meant to say CONTAINS.
Use bson.Regex to specify a regular expression value: sess.DB(db).C(cApp). Find(bson.M{"permalink": bson.RegEx{"org.*", ""}}). All(&m) ...
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...
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...
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...
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...
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"...
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:...
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 |...
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...
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...
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...
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...
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, "%") ...
Functions in the where clause tend to be slow. Something like this might be quicker: where id like '123%' or id like '% 123%' ...
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}"; ...
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...
> 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 ...
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'); ...
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...
replace "%received_input%" with '%{}%'.format(received_input)
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,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 ...
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...
I have used regexp_like instead of only like and it worked for me.
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...
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...
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...
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...
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...
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...
Try this where clause. Use OR condition to accept the empty string where (Book_Author LIKE '*" & TextBox.Text & "*' or Book_Author = '') ...
check your code that their set the value of $search_text it might be blank so it returns all rows .
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...
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,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...
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...
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]....
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...
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); ...
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; ...
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..
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]%' ...
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...
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...
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...
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...
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,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%') ...
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...
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...
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%' ...
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...
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...