CODEWITHSUNDEEP
sqliteselectescaping
Smith
Smith

Reputation: 5961

searching for symbols in sqlite database

i need to search for and of these ! @ # $ % ^ & * ( ) '\ symbols in within a field content sqlite database, i am using the following sql string

Select ID From Tasks Where '--' || tasks || '--' ||  note || '--'  like '%! @ # $ % ^ & * ( ) ""%'

or i tried this

Select ID From Tasks Where task LIKE '[!] [@] [#] [$] [%] [^] [&] [*] [(] [)] ''%'

but the search does not return any result. For the field task some of the content are

  1. Today's tasks or

  2. here are some wildcards ! @ # $ % ^ & * ( ) ' or

  3. Some brakest are [ ] { } ( )

so if i search item one as a whole, i should get it or if i search for ! @ # $ % ^ & * ( ) ', i should get item 2. etc

if i serah within the field passing some of the wildcards within the text or all in the same order, i should get the row contain the value

I know some of the symbols have meaning in sqlite sql, so how do i escape all or any of these characters in an sql query string

Upvotes: 0

Views: 2786

Answers (2)

CL.
CL.

Reputation: 180080

First, strings in SQL are enclosed in single quotes; if you want to write a string containing single quotes, you have to escape them by doubling them:

SELECT ID FROM Tasks WHERE task = 'Today''s tasks'

To avoid this, most languages allow you to use parameters:

db.execute("SELECT ID FROM Tasks WHERE task = ?", [ "Today's tasks" ])

Second, the LIKE operator interprets the % and _ characters specially. To use these characters without their special meaning, you can use the ESCAPE clause to specify a character that escapes %, _, and itself:

SELECT ID FROM Tasks WHERE task LIKE '%! @@ # $ @% ^ & * ( ) ''%' ESCAPE '@'

This escaping is completely independent from the SQL string quoting; you stil you have to use even if you're using parameters:

db.execute("SELECT ID FROM Tasks WHERE task LIKE ? ESCAPE '@'",
           [ "%! @@ # $ @% ^ & * ( ) '%" ])

Third, the GLOB operator does not have special escape characters; you use special characters literally, you have to use them in peculiar ways. From the documentation:

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 the enclosed list.

With the [...] and [^...] matching, a ']' character can be included in the list by making it the first character after '[' or '^'. A range of characters can be specified using '-'. Example: "[a-z]" matches any single lower-case letter. To match a '-', make it the last character in the list.

Hints: to match '*' or '?', put them in "[]". Like this:

abc[*]xyz Matches "abc*xyz" only

Upvotes: 1

Anton Kovalenko
Anton Kovalenko

Reputation: 21507

I would use this:

Select ID From Contacts Where (email || mobile || main || other) GLOB '*[!@#$%^&*()\]*'

Upvotes: 1

Related Questions