• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to search sqlite for word beginning with user inputted string

 
Roger Shaw
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
in Java how do I search a sqlite db for words that start with a string of characters input by the user? I want to be able to input two or three letters and provide a choice of possible matches. It seems I can't use the name of the input string in the sqlite query and have to use a placeholder to avoid problems? I would like to create a cursor to move through possible matches or a result set to work through.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roger Shaw wrote:in Java how do I search a sqlite db for words that start with a string of characters input by the user? I want to be able to input two or three letters and provide a choice of possible matches. It seems I can't use the name of the input string in the sqlite query and have to use a placeholder to avoid problems? I would like to create a cursor to move through possible matches or a result set to work through.

Use SQL LIKE operator with a wildcard e.g. SELECT ... FROM mytable WHERE somecolumn LIKE 'fub%'.
 
Roger Shaw
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply chris.
I didn't explain very well.

I had managed to get my code to search a database for some text I included in the code with an added wildcard like the example you provided.
What is stumping me is the next step.
How to replace that text with a string variable that will change every time the program is run.

As i understand it in your example the code will return words beginning fub each time its run.

Instead I want it search for words beginning with letters that a user provides and change each time.
To keep things simple I used this for my input

String nuplant;
nuplant = user_input.next();

Next I want to include the contents of nuplant in the search to return LIKE (nuplant+%)

So a user can search for words beginning fub, or ram or deb.... or anything else.

I managed to get my code to work by returning the whole contents of the database then searching the result set for matches.
Which worked but there has to be a better, easier way.


 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, you might want to look at PreparedStatement which is the usual (and safest) way to bind a Java variable into a SQL statement. Here's an example using LIKE.

I'm not a Java programmer, but if you post the relevant pieces of your code then there are plenty of Java developers around here who might be able to help you further if necessary. Just remember to put code tags around it (use the "Code" button in the post editor).

PS: Welcome to JavaRanch!
 
Roger Shaw
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks again chris

you pointed me in the right direction and i cobbled together different examples and it now works.

Was scratching my head for awhile when the resultset was coming back with nothing.

Then realised was so focused on the prepared statement had forgotten the wildcard.

Then had lots of problems trying to get % to work, tried it everywhere (i thought) and nothing worked.

so typed problem into search engine and this sorted that out

stackoverflow - using wildcard in prepared statements

This seems to work fine

 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Glad you got there in the end.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic