Asked  10 Months ago    Answers:  5   Viewed   7 times

I can't figure out what the underscore character does in an SQLite like statement. The wildcard character, %, is probably the same as in most other SQL databases.

So, what does the _ character do?

 Answers

4

The underscore is also the same as in most other SQL databases and matches any single character (i.e. it is the same as . in a regular expression). From the fine manual:

An underscore ("_") in the LIKE pattern matches any single character in the string.

For example:

-- The '_' matches the single 'c'
sqlite> select 'pancakes' like 'pan_akes';
1
-- This would need '__' to match the 'ca', only one '_' fails.
sqlite> select 'pancakes' like 'pan_kes';
0
-- '___' also fails, one too many '_'.
sqlite> select 'pancakes' like 'pan___kes';
0

And just to make sure the results make sense: SQLite uses zero and one for booleans.

Thursday, July 29, 2021
 
Zulakis
 
4

The quotes protect either ? or :name from being taken as a place-holder -- they're taken literally. You need to place the percent signs around the string you're passing, and use the plain placeholder without quotes. I.e.:

self.cursor.execute(
  "select string from stringtable where string like ? and type = ?",
  ('%'+searchstr+'%', type))

Note that neither ? is in quotes -- and that's exactly as it should be for them to be taken as placeholders.

Thursday, June 3, 2021
 
nomie
 
2

How about split it into four parts -

[MyColumn] Like '% doc %' 
OR [MyColumn] Like '% doc' 
OR [MyColumn] Like 'doc %' 
OR [MyColumn] = 'doc'

Edit: An alternate approach (only for ascii chars) could be:

'#'+[MyColumn]+'#' like '%[^a-z0-9]doc[^a-z0-9]%'

(You may want to take care of any special char as well)

It doesn't look like, but you may want to explore Full Text Search and Contains, in case that's more suitable for your situation.

See: - MSDN: [ ] (Wildcard - Character(s) to Match) (Transact-SQL)

Wednesday, June 23, 2021
 
5

You need to use the ESCAPE clause:

where field like 'xyz_abc' escape ''

See the section The LIKE and GLOB operators in the SQLite Documentation.

Friday, August 6, 2021
 
2

In the expression 'hi' LIKE '%hi there%', it is not possible to find any characters to replace the % wildcards so that the strings would match.

You need to do the comparison the other way around, i.e., 'hi there' LIKE '%hi%':

db.rawQuery("SELECT shompet FROM sentence" +
            " WHERE ? LIKE '%' || " + column + " || '%'",
            new String[] { newMessage });
Monday, December 13, 2021
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :