Wednesday, February 23, 2022

SQL scalar function to add quote characters around your strings

delimit_name sql to delimit columns and fields

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.

7 comments:

  1. Hi Simon,
    looks like delimiters are not added only if value doesn't contain spaces and is uppercase:

    values('VOJTECH') returns VOJTECH,
    but
    values('Vojtech') returns "Vojtech" for me.

    V.

    ReplyDelete
  2. shouldn't that be RTRIM(LASTNAME) in the last example?

    ReplyDelete
    Replies
    1. Thank you for pointing that out. Correction has been made.

      Delete
  3. For some reason IBM decided to let the function error out when the input value is blank(s). This seems odd and the function could have returned blanks or a null. I have name information in the database that I might want delimited but if its blank then the function will crash my statement.

    For instance, employee data has the official name and a employee preferred name. But this preferred name is only filled out if the employee asks for it to be filled out.

    Oh well, I guess I will just have to write my own.

    -Matt

    ReplyDelete
  4. I also noticed that the DELIMIT_NAME() function will put delimiters around words that are SQL reserved words.

    -Matt

    ReplyDelete
    Replies
    1. Thank you for sharing. It makes sense that it does.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.