CODEWITHSUNDEEP
mysqlsqlsqliteandroid-sqlite
AshiqSUltan
AshiqSUltan

Reputation: 81

how to make Wildcards consider spaces between

I'm working with a table with three columns( ID | NOTE |TAG)

ID- primary key

NOTE- some things

TAG- contains values separated with space

imagine that id-1 contains TAG- computer javaScript java forton cellphone

and id-2 contains TAG- computer java forton c++ android

When using a Wildcard to search for term java which is in middle of a string the SQLITE also shows the row containing javascript

My query " LIKE '%" + "java" + "%'"; this shows the rows if the word java is found anywhere inside (i.e) it shows java as well as javaScript as well as Scriptinjava

how to make it show only java ??

Upvotes: 0

Views: 39

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Like this:

where tags = 'java'
or tags like 'java %' -- starts with java
or tags like '% java %' -- java in the middle
or tags like '% java' -- ends with java

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Sometimes, we are stuck with other people's bad design decisions. Fortunately, most people know that storing lists in strings is a bad idea. So, they use the proper SQL structure, which is a table with one row per list item.

But, we we are stuck with other people's bad decisions and cannot fix the underlying data structure, it is possible to use SQL to do what you want. In MySQL, you can express this as:

WHERE concat(' ', tags, ' ') LIKE '% '" + "java" + "' %'

(I am assuming that the + is the string concatenation operator in the application language you are using.)

Upvotes: 1

Related Questions