1

Let's say an SQLite database column contains the following value:
U Walther-Schreiber-Platz

Using a query I'd like to find this record, or any similar records, if the user searches for the following strings:

  • walther schreiber
  • u walther
  • walther-schreiber platz
  • [Any other similar strings]

However I cannot figure out any query which would do that. Especially if the user does not enter the - character.

Example:
select * from myTable where value like '%walther schreiber%'; would not return the result because of the missing -.

Thanks, Robert

Thomas Shields
  • 8,874
  • 5
  • 42
  • 77
Robert Strauch
  • 12,055
  • 24
  • 120
  • 192
  • 1
    replace all of the spaces' ' with percents '%', so like: '%walther%schreiber% and lower or upper both sides of the where clause – forsvarir May 04 '11 at 19:55

1 Answers1

1

So, as I said in my comment, I think you can have a query along the lines of:

select * from myTable where LOWER(value) like <SearchValue>

I'm assuming you're collecting the <SearchValue> from the user programmatically, so would be able to do the following: <SearchValue> would need to be: The user's search string, appropriately cleansed to avoid SQL injection attacks, converted to lower case, with all of the spaces converted to '%', so that they match zero or more characters...

So you would have (for example):

select * from myTable where LOWER(value) like '%walther%schreiber%'
select * from myTable where LOWER(value) like '%walther-schreiber%platz%'

etc... however, this does assume that the word order is the same, which in your examples it is...

Community
  • 1
  • 1
forsvarir
  • 10,749
  • 6
  • 46
  • 77