Autocomplete Just SQL / RPG and Assembly

Question

So I'm working on implementing an auto-complete search bar and integrating it with an RPG and Assembly system using DB2. I have tried these two techniques to search for distinct IDNumbs from a file, where the input requires at least 3 numbers before the search takes place. I have tried the following:

 Regular expression example:  ^[0-9]*$ 

SQL

SELECT DISTINCT [IDNUM] FROM FILE WHERE [iNPUT] > 3;

Unfortunately neither technique works accurately. I'd love it if someone had some advice on how to make it more robust. An RPG solution is welcomed as well but an SQL one would be great.

Answer 1

You could check if your search string is a number in RPG this way:

MONITOR
    EVAL FIELD = %INT(SEARCH_STR)
ON-ERROR
    // error: not a number
ENDMON 

where SEARCH_STR is your search string and FIELD is defined as a number.

Regarding the query, if the server is DB2 this should work:

SELECT DISTINCT(IDNUM) 
FROM FILE
WHERE CHARACTER_LENGTH(SEARCH_STR) >= 3 AND 
      IDNUM LIKE '%SEARCH_STR%'

Change SEARCH_STR with your search string.

But I'd suggest to check the length of the search string via code instead of via SQL since it is trivial to do.