SQL query issue

Classic Servoy
1

I have a number of records where the “catkey” field contains data such as “L1”, “L1L2”, “L1L2L3”, “L2”, L2L2", etc. I’m attempting to construct a SQL query where I’ll select all records starting with “L1”. I DON’T know how many variations there may be. Therefore I believe I should use a wildcard.

I can manually enter Find mode and successfully search for “L1%”. That works. But the following does not:

var key = 'L1';
key = key + '%';
var maxReturedRows = 1000; 
var query = "SELECT catitemid FROM catitem WHERE catkey = '" + key + "' ";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);
controller.loadRecords(dataset);

I’m misunderstanding the required syntax.

2

Morley,

have a look at one of ten thousand SQL guides out there.:wink: You query should look like this:

var query = “SELECT catitemid FROM catitem WHERE catkey LIKE '” + key + "’ ";

In this example, “key” can be either

  1. “L1%”: everything starting with L1 will be found
  2. “%L1”: everything ending on L1 will be found
  3. “%L1%”: everything containing L1 will be found

Patrick

3

Have read about “LIKE” but until now have never recognized a place to put it into service. Works. Thanks Patrick.