Searching for reserved characters in SQL

SQL like all other programming languages has a number of characters that are reserved for certain uses. What happens if you need to search for one of those characters in a table or file? In this post I am going to give you two ways you can do this.

To show how this works I have a test file with five records in it:

FIELD1 ---------- HERE'S IT 100% REAL APPLE BEAR CAT

What I want to do is…

1. Find the record with the apostrophe ( ' ) in it
2. Find the record with the percent sign ( % ) in it

Both of these characters are reserved in SQL, which is going to get interesting as the percent symbol is also SQL's wildcard symbol.

When I play with SQL I always start by using ACS's "Run SQL statements" tool.

Having programmed for so long with languages like CL and RPG I made an educated guess as how I could search for the record with the apostrophe:

01 SELECT FIELD1 FROM MYLIB.TESTFILE 02 WHERE FIELD1 LIKE '%''%'

Line 2: You see that there are two apostrophes between the wild card characters. This will search the entire length of the field for an apostrophe.

The result was a success!

FIELD1 --------- HERE'S IT

Coming up with a Select statement to find the percent symbol proved harder. Only after a deep dive into Google I found what I was looking for. I need to use an "escape character" to denote that the following character should not be consider with its special meaning, just a plain old character.

01 SELECT FIELD1 FROM MYLIB.TESTFILE 02 WHERE FIELD1 LIKE '%\%%' ESCAPE '\'

Line 2: I have "escaped" the percent symbol between the two "wild card" percent symbols with a back slash ( \ ), but I need to tell the Db2 for i engine what the "escape" character is. To do that I use the word ESCAPE followed by what I am using as the escape character. I could use any character I desired, but chose the back slash as I have not seen that used in many types of information, except internet addresses.

The results show I did only retrieve the record with the percent symbol in it:

FIELD1 --------- 100% REAL

Working in ACS's "Run SQL statements" tool is fine and dandy, but what about embedding these SQL statements into a RPG program?

01 **free 02 dcl-s RtnValue char(10) ; 05 exec sql SELECT FIELD1 INTO :RtnValue 06 FROM TESTFILE 07 WHERE FIELD1 LIKE '%''%' ; 08 dsply ('RtnValue 1 = ' + RtnValue) ; 10 exec sql SELECT FIELD1 INTO :RtnValue 11 FROM TESTFILE 12 WHERE FIELD1 LIKE '%\%%' ESCAPE '\' ; 13 dsply ('RtnValue 2 = ' + RtnValue) ; 14 *inlr = *on ;

Line 1: In 2020 this program needs to be written in totally free RPG.

Line 2: I am defining this variable so that it will contain the value of the field from the file.

Lines 5 – 7: This is basically the same as the first statement I executed in ACS. The difference is the INTO clause moves the value from the result into the program variable RtnValue.

Line 8: I am using the Display operating code, DSPLY, to show the value returned from the SQL statement.

Lines 10 – 12: This is the equivalent for the second statement I used in the ACS.

Line 13: I display the returned result.

After compiling this program, when I call it I see:

DSPLY RtnValue 1 = HERE'S IT DSPLY RtnValue 2 = 100% REAL

If you are concerned that it would take too much time to work out the number of apostrophes needed, or the code with the "escape" character may look confusing to another programmer there is another approach we can take. I can also use the REGEXP_COUNT regular expression. This expression will return the number of times a character(s) appears in a string. I am going to use it to count the number of times the apostrophe and percent sign appears in the string in the field FIELD1.

All I need to do is to replace the line in the SQL Select statement that contains the WHERE clause:

05 exec sql SELECT FIELD1 INTO :RtnValue 06 FROM TESTFILE 07 WHERE REGEXP_COUNT(FIELD1,'''',1) > 0 ; 10 exec sql SELECT FIELD1 INTO :RtnValue 11 FROM TESTFILE 12 WHERE REGEXP_COUNT(FIELD1,'%',1) > 0 ;

I am passing three parameters to the REGEXP_COUNT function:

1. Name of the field that contains the string to search
2. The character to count in the string
3. Which position in the string do I want to start

Each time the test character is found in the string the count is incremented, therefore, if I test that the number returned by the function is greater than zero I know it found the character.

These two statements returned the same results as the previous statement.

I could replace the literal in the regular expression with a variable if I so desired:

03 dcl-s TestChar char(1) ; 04 TestChar = '''' ; 05 exec sql SELECT FIELD1 INTO :RtnValue 06 FROM PGMSDH.TESTFILE 07 WHERE REGEXP_COUNT(FIELD1,:TestChar,1) > 0 ; 09 TestChar = '%' ; 10 exec sql SELECT FIELD1 INTO :RtnValue 11 FROM PGMSDH.TESTFILE 12 WHERE REGEXP_COUNT(FIELD1,:TestChar,1) > 0 ;

Line 3: This is the variable that will contain the character to count.

Line 4: I need all of those apostrophes to add just one into the variable.

Line 7: Notice that the second parameter of the REGEXP_COUNT function is the new variable.

Line 9: Move the percentage symbol into the new variable.

Line 12: As before the second parameter of the function is the new variable.

I am sure it will come as no surprise to you that the same results as before are displayed when the program is called.

There you have it two different ways to search for characters SQL gives a special purpose to, but could be found in any file or table.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.