How to fetch SQLite data starting with "%" (percent sign)?

Question

I have a database in which I have few tables. I have a table which contains a column containing tag names and tag IDs. There is a situation where I have tag name and I want the tag IDs for all those tags whose tag names start with the input text.

The problem is that when I am hitting database for tag name "%", it is returning all the rows of that table. But I need only those rows which contains tag names starting with the the input text (which in this case is starting with the "%" symbol).

Example:

TagName
-------
abc
%test_tag
def

the result should be "%test_tag" if I search for "%".

Currently, the query which I am using is

SELECT * FROM Tags WHERE TagName like '"+tagName+" %'

How to fix it?

possible duplicate of [Android SQLite LIKE escape wildcard](http://stackoverflow.com/questions/13830034/android-sqlite-like-escape-wildcard) — Andrew T., Sep 18 '14 at 10:34

score 4 · Answer 1 · edited May 23 '17 at 12:30

As suggested here, you need to use the ESCAPE keyword. If it didn't work for you, please post your attempt. I have successfully used the following query, which matches https://de.wikipedia.org/wiki/%s:

SELECT * FROM moz_places WHERE url LIKE '%de.wikipedia.org%\%s' ESCAPE '\'

Basically, pick an escape character (e.g. :), and replace any instance of % with :% (or whatever character you picked). If the escape character itself can appear in searches, replace each occurrence with the escape character with two escape characters. Pseudocode:

search = '%test'
search = search.replace(':', '::')
search = search.replace('%', ':%')

query = "SELECT * FROM Tags WHERE TagName like '"+search+"%'"

Note that I removed a space before your % sign, that might have been the issue. Also, don't build SQL queries by concatenating strings unless you want SQL injection. Use prepared statements.

(I know the question is old, but others will Google it and deserve an answer.)

For future reference: For multiple statements, ESCAPE needs to be added to each one. "Where column_a LIKE '%\%%' ESCAPE '\' or column_b LIKE '%\%%' ESCAPE '\'" For example. — Max Izrin, Aug 09 '17 at 17:48

score 0 · Answer 2 · answered Sep 18 '14 at 10:47

0

% is a reserved operator for sql its usage : LIKE %ong% will get the rows including "ong" string in it for example : "Long".

You have to use an escape case for "%" character. I don't know how to escape % character but im sure you can find it in a short googling.

answered Sep 18 '14 at 10:47

Ercan

3,705
1
22
37

there should be another way to escape like single quote. you can escape single quote by using it twice like : ' Ercan''s'. isn't there another way to escape % like this? – Ercan Sep 18 '14 at 11:25
I'm afraid there is no way to escape % like this – Shadab Ansari Sep 18 '14 at 11:27
Can you replace % with another special character instead? even a word can do the job: "SELECT * FROM Tags where TagName LIKE 'POTATO%'" – Ercan Sep 18 '14 at 11:31

score 0 · Answer 3 · edited May 23 '17 at 12:06

The idea from the accepted answer on other question is to use BETWEEN keyword instead, for query starting/ending with LIKE's wildcard keywords (e.g. % and _).

Try something like this:

String query = "SELECT * FROM Tags";
if(tagName.startsWith("%")) {
    query += " WHERE TagName BETWEEN " + tagName + " AND " + tagName + "z";
} else {
    query += " WHERE TagName LIKE '" + tagName + "%'";
}

score 0 · Answer 4 · answered Apr 19 '19 at 20:35

0

you can use replace function in this case:

select *
from Tags where replace(TagName,'%','@')like '@%'

answered Apr 19 '19 at 20:35

UMUT

134
5

score -1 · Answer 5 · answered Sep 18 '14 at 10:33

-1

select * from Tags where Tagname like '%"+tagname+"'

This will do the job

answered Sep 18 '14 at 10:33

Ameya Deshpande

3,580
4
30
46

This will return all the rows if tagname is "%" – Shadab Ansari Sep 18 '14 at 10:36
yes It will. But the best practice should be not to add % in tagname. try something else like # or @. Why you want % in name. Anything related to it – Ameya Deshpande Sep 18 '14 at 10:41
I cannot prevent "% from getting used as it is customer's requirement. – Shadab Ansari Sep 18 '14 at 10:43

How to fetch SQLite data starting with "%" (percent sign)?

5 Answers5