Determine the length of a string when using SQL

The title is not as descriptive as I would have liked, my original title was too long to appear reasonably at the top of this page. I am going to describe how you can determine the length of the string of characters within a column from a DDS file or DDL table using SQL. I have found this most useful when using it within a Select statement.

This SQL function can be called by one of two names: CHARACTER_LENGTH or CHAR_LENGTH.

There is also a related function, LENGTH, that I will describe later in this post.

As its name suggests, CHARACTER_LENGTH will return the length of a string expression. In the following example I am using the VALUES SQL statement with CHARACTER_LENGTH:

01 VALUES CHARACTER_LENGTH('A ')

Line 1: The string is the letter "A" followed by thirteen blanks.

The result is:

00001 ------ 14

The result of 14 includes the blanks, which are valid characters. If I want to exclude those from the count I would change my SQL statement to be:

01 VALUES CHARACTER_LENGTH(TRIM('A '))

Line 1: I have used the TRIM function to "remove" any leading or trailing blanks. Why did I TRIM instead of just a right trim, RTRIM? In this example the result of the two is the same. I use the TRIM in case any of the data I am processing has leading blanks as well as trailing ones, I want to ignore both.

The result is 1 as there is only one non-blank character in the string I used.

00001 ------ 1

I can show this function in a way I use it with the following statement:

01 SELECT LAST_NAME, 02 CHARACTER_LENGTH(LAST_NAME) AS "Length" 03 FROM PERSON 04 LIMIT 5

Lines 1 and 2: There are just two column of results, the first, line 1, is the last name of the Person and the second, line 2, is the length of the string. I do not have to trim the last name column as it is VARCHAR, variable length character.

Line 5: I only want to return five results.

The results retrieved are:

LAST_NAME Length --------- ------ ALLEN 5 CROMPTON 8 BYRNE 5 CAREY 5 MCNULTY 7

The results show that as the column is VARCHAR, CHARACTER_LENGTH returns the length of the string within it.

I can use the following statement to find the longest last name:

01 SELECT LAST_NAME, 02 CHARACTER_LENGTH(LAST_NAME) AS "Length" 03 FROM PERSON 04 ORDER BY "Length" DESC 05 LIMIT 1

Line 4: I can use the name I gave the column for sorting the file, rather than repeat the CHARACTER_LENGTH. The results are ordered in descending sequence.

Line 5: I only need one result, which will be the one with the most characters, i.e. the longest.

LAST_NAME Length ------------ ------ BLANCHFLOWER 12

With a file where the field is fixed length character things are not as easy as I have just shown. I have a file with a 50 long character field, and my statement to return the total length of the characters is:

01 SELECT DATA, 02 CHARACTER_LENGTH(DATA) AS "Length" 03 FROM TESTFILE

As the field is of a fixed width both of the results have the same length.

DATA Length ---------------------- ------ Simon is here! 50 And he is here too!!! 50

I need to use a TRIM, on line 2, to get the actual length:

01 SELECT DATA, 02 CHARACTER_LENGTH(TRIM(DATA)) AS "Length" 03 FROM TESTFILE

The above shows the actual lengths of the strings.

DATA Length ---------------------- ------ Simon is here! 14 And he is here too!!! 21

When I use Unicode columns and want to know the length of the string, versus the number of characters within, I would use the LENGTH function. What I call "special" characters, characters with accents etc., occupy two bytes, while those without occupy only one. The statement below shows the difference of the results returned from CHARACTER_LENGTH and LENGTH with data from a Unicode column:

01 SELECT UNICODE, 02 CHARACTER_LENGTH(TRIM(UNICODE)) AS "CharLen", 03 LENGTH(TRIM(UNICODE)) AS "Length", 04 LENGTH(TRIM(UNICODE)) - CHARACTER_LENGTH(TRIM(UNICODE)) AS "Diff" 05 FROM TESTFILE1

Line 1: The Unicode column is called UNICODE.

Line 2: I am trimming the contents of UNICODE, I am trimming to remove any blanks, and getting the number of characters using CHARACTER_LENGTH.

Line 3: The same as line 2, but using the LENGTH function.

Line 4: Calculate the difference between the results returned from the two functions.

The results are:

UNICODE CharLen Length Diff ------------------------- ------- ------ ---- Simon 5 5 0 Sìmôñ 5 8 3 áâãåæçèéêëìíîïñòóôõøùúûý 24 48 24

The first result "Simon" does not contain any "special" characters, therefore the results from the two functions is the same, in other words the difference is zero.

The second result "Sìmôñ" contains three "special" characters, the "ì", "ô", and "ñ". The string contains five characters, and the length is eight byes as each "special" character occupies two bytes. Which results in a difference of three between the two functions.

The third result, which is 24 "special" characters, CHARACTER_LENGTH returns 24, as there are 24 characters, and LENGTH returns 48, as each of those "special" characters occupies two bytes.

This shows how I can use the two function for different things:

• CHARACTER_LENGTH for a count of the characters
• LENGTH the space that the characters occupy

I have noticed that I have used CHARACTER_LENGTH here, rather than CHAR_LENGTH. In my programming I do the opposite and use CHAR_LENGTH in preference to the longer named CHARACTER_LENGTH.

 

You can learn more about this from the IBM website:

• CHARACTER_LENGTH and CHAR_LENGTH SQL functions
• LENGTH SQL function

 

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