SQL scalar function to add quote characters around your strings

I was working on an interface between two applications. I needed to provide to the receiving application data that would be delimited with the quote character ( " ) and of varying length. The data was coming from an older application with DDS files and fixed width fields.

In my first version I used the following to provide a person's name in the desired format:

01 SELECT '"' || RTRIM(LAST_NAME) || ', ' || RTRIM(FIRST_NAME) || '"' 02 FROM PERSON

Line 1: I am using the RTRIM scalar function to remove the trailing blanks from both fields. I always the double pipes ( || ) to denote concatenation.

The result was what I wanted:

00001 ---------------- "ALLEN, REG" "CROMPTON, JACK" "BYRNE, ROGER"

But, I am left thinking that there must be an easier way.

While investigating a solution for another issue, I came across the SQL scalar function DELIMIT_NAME. After trying this out it was better than my earlier solution.

The scalar function only requires one parameter, the string or field/column name you wish to delimit. The result is a variable length string which is delimited by the quote character.

I first tried with a character value:

VALUES DELIMIT_NAME('Simon ')

Which gave me:

00001 ------- "Simon"

It is a delimited and trimmed string.

What about if I use it with a number?

VALUES DELIMIT_NAME(01)

The result was a delimited character string:

00001 ------- "1"

Dates next:

VALUES DELIMIT_NAME(CURRENT_DATE)

It delimited that too:

00001 ------------ "2022-02-23"

The only thing I was disappointment with was the way it handles null:

VALUES DELIMIT_NAME(NULL)

I was hoping for two quote symbols with nothing between them: "". But it returns a value of null.

00001 ------ <NULL>

Note: I have change my settings in ACS's Run SQL Scripts to show null as <NULL>.

Now to examples using data from a SQL DDL table. From the table PERSON I am only concerned with the columns:

1. FIRST_NAME
2. LAST_NAME
3. PLACE_OF_BIRTH

This statement shows the first ten results from the table:

01 SELECT FIRST_NAME,LAST_NAME,PLACE_OF_BIRTH 02 FROM PERSON 03 LIMIT 10

Line 3: This is where I state I only want ten results.

The results are:

FIRST_NAME LAST_NAME PLACE_OF_BIRTH ---------- ------------ ------------------ REG ALLEN MARYLEBONE JACK CROMPTON HULME ROGER BYRNE GORTON JOHNNY CAREY DUBLIN THOMAS MCNULTY SALFORD BILLY REDMAN MANCHESTER JACKIE BLANCHFLOWER BELFAST ALLENBY CHILTON SOUTH HYLTON HENRY COCKBURN ASHTON-UNDER-LYNE DONALD GIBSON MANCHESTER

The receiving application wants the people's names formatted as: "last_name, first_name".

01 SELECT DELIMIT_NAME(RTRIM(LAST_NAME) || ', ' || FIRST_NAME) as "NAME", 02 DELIMIT_NAME(PLACE_OF_BIRTH) AS "BIRTH_PLACE" 03 FROM PERSON 04 LIMIT 10

Line 1: This part of the statement concatenates the first and last names, and then DELIMIT_NAME adds the delimiter characters.

Line 2: Adding the delimiting characters to the place of birth.

Line 4: I only want ten results returned.

The results were a bit of a surprise:

NAME BIRTH_PLACE ---------------------- -------------------- "ALLEN, REG" MARYLEBONE "CROMPTON, JACK" HULME "BYRNE, ROGER" GORTON "CAREY, JOHNNY" DUBLIN "MCNULTY, THOMAS" SALFORD "REDMAN, BILLY" MANCHESTER "BLANCHFLOWER, JACKIE" BELFAST "CHILTON, ALLENBY" "SOUTH HYLTON" "COCKBURN, HENRY" "ASHTON-UNDER-LYNE" "GIBSON, DONALD" MANCHESTER

If the value did not contain a space then the delimiting characters were not added.

Testing with the results from the DELIMIT_NAME proved successful as it was found that the receiving application only needed delimiting characters if there were spaces in the string.

 

You can learn more about the DELIMIT_NAME SQL scalar function from the IBM website here.

 

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