Chapter 11 - Substrings and Positioning Functions

"The afternoon knows what the morning never suspected.“

Swedish Proverb

The LOWER Function

The syntax for using LOWER:

It has been discussed earlier that there is no need for concern regarding the use of lower or UPPER case characters when coding the SQL. As a matter of fact, the different case letters can be mixed in a single statement. The NPS RDBMS is case sensitive, regarding the data. As seen earlier this means that it knows the difference between a lower case letter like 'a' and an UPPER case letter 'A'. The LOWER case function is used to convert all characters stored in a column to lower case letters for display or comparison. It is a function and therefore requires that the data be passed to it. When LOWER is used in a WHERE clause, the result is a predictable string of all lowercase characters. When compared to a lowercase value, the result is a case blind comparison. This is true regardless of how the data was originally stored.

The UPPER Function

The syntax for using UPPER:

The UPPER case function is used to convert all characters stored in a column to the same characters in UPPER case. It is a function and therefore requires that data be passed to it. It is also possible to use both the LOWER and UPPER case functions within the WHERE clause. This technique can be used to make ANSI non-case specific, by converting all the data to a known state, regardless of the starting case. Thus, it does not check the original data, but instead it checks the data after the conversion. When the data does not meet the requirements of the output format, it is time to convert the data. The UPPER and LOWER functions can be used to change the appearance or characteristics of the data to a known state.

CHARACTER_LENGTH

Query

FIRST_NAME	C_LENGTH
Mandee	6
Herbert	7
Loraine	7
Squiggy	7
John	4
Cletus	6
Billy	5
William	7
Richard	7

The Character_length command counts the number of characters in a Char or Varchar column

OCTET_LENGTH

You can also use the OCTET LENGTH command. These two queries get the same exact answer sets!

TRIM for Troubleshooting the CHARACTERS Command

Last_Name is a CHAR(20) fixed length field.

The TRIM command will trim off any spaces before and after the Last_name.

The TRIM Command trims both Leading and Trailing Spaces

Query 1

Query 2

Both queries trim both the leading and trailing spaces from Last_Name.

Trim and Trailing is Case Sensitive

'Billy' and ' Squiggy' does not TRIM the trailing 'y' because it was after a capitol 'Y'

First_Name	No_Y
Billy	Billy
Cletus	Cletus
Herbert	Herbert
John	John
Loraine	Loraine
Mandee	Mandee
Richard	Richard
Squiggy	Squiggy
William	William

For LEADING and TRAILNG, it IS case sensitive.

Trim and Trailing works if Case right

For LEADING and TRAILNG, it IS case sensitive.

Trim Combined with the CHARACTERS Command

This will allow for the character count to only be 9 because both the leading and trailing spaces have been cut.

How to TRIM only the Trailing Spaces

The TRAILING FROM Command allows you to only TRIM the spaces behind the Last_Name. Now, we will still get a character count of 11 because we are only cutting off the trailing spaces and not the beginning spaces.

How to TRIM Trailing Letters

The above example removed the trailing 'y' from the First_Name and the trailing 'g' from the Last_Name. Remember that this is case sensitive.

How to TRIM Trailing Letters and use CHARACTER_Length

Notice that the length (column No2) for Coffing is six and not seven. Notice the length for Strickling is 9 and not 10. This is because the trailing 'g' was trimmed before calculating the length. We trimmed the last name and then trimmed the trailing 'g'.

LTRIM Function

Query

LTRIM will trim the spaces from the left of a column, or you can specify which particular character you want to trim.

RTRIM Function

Query

RTRIM will trim the spaces from the right of a column, or you can specify which particular character you want to trim.

BTRIM Function

Query

BTRIM will trim the spaces from both the right and left of a column, or you can specify which particular character you want to trim at both ends.

The SUBSTRING Command

This is a SUBSTRING. The substring is passed two parameters and they are the starting position of the string and the number of positions to return (from the starting position). The above example will start in position 2 and go for 3 positions!

How SUBSTRING Works with NO ENDING POSITION

If you don't tell the Substring the end position, it will go all the way to the end.

Using SUBSTRING to move Backwards

A starting position of zero moves one space in front of the beginning. Notice that our FOR Length is 6 so 'Squiggy' turns into ' Squig'. The point being made here is that both the starting position and ending positions can move backwards, which will come in handy as you see other examples.

How SUBSTRING Works with a Starting Position of -1

A starting position of -1 moves two spaces in front of the beginning. Notice that our FOR Length is 3, so each name delivers only the first initial. The point being made here is that both the starting position and ending positions can move backwards, which will come in handy as you see other examples.

How SUBSTRING Works with an Ending Position of 0

In our example above, we start in position 3, but we go for zero positions so nothing is delivered in the column. That is what's up!

An Example using SUBSTRING, TRIM, and CHAR Together

The SQL above brings back the last two letters of each Last_Name even though the last names are of different length. We first trimmed the spaces off of Last_Name. Then, we counted the characters in the Last_Name. Then, we subtracted two from the Last_Name character length and then passed it to our substring as the starting position. Since we didn't give an ending position in our substring, it defaulted to the end.

SUBSTRING and SUBSTR are equal, but use different syntax

Query 1

Query 2

Both queries above are going to yield the same results! SUBSTR is just a different way of doing a substring. Both have two parameters in starting position and number of character length.

The POSITION Command finds a Letters Position

This is the position counter. What it will do is tell you what position a letter is on. Why did Jones have a 4 in the result set? The 'e' was in the 4^th position. Why did Smith get a zero for both columns? There is no 'e' in Smith and no 'f' in Smith. If there are two 'f's, only the first occurrence is reported.

STRPOS Function

The STRPOS function is used to return a positive number that indicates the location of a character string within another character string. If STRPOS does not find the character requested, it returns a zero. Here is the syntax for STRPOS:

SELECT STRPOS(<character-data-column>,<character-string>)

Query

Result

The example query above uses STRPOS to show the starting position of the first 'a' in the column Last_Name. Those who don't have an 'a' in their last name return a 0.

The POSITION And STRPOS Do The Same Thing

The POSITION and the STRPOS functions are equivalent. They just have a little different syntax

SUBSTRING and POSITION Used Together In An UPDATE

When two of these functions are used in combination, they deliver a lot of power to the SQLstatement. The utilization below illustrates the ability to change one character to another character based on the stored data. Assume that the data should have been stored as an 'M' for male and an 'F' for female. However, due to a misunderstanding, the values have been reversed in every row stored in the table. How would you fix this?

This change can be accomplished using SQL. The following UPDATE statement manages it very well:

If this looks too simple to work, you need to look at it again. There is not always a need to be long and involved in order to accomplish a big task. What does the SQL do? First, it examines the data stored in every row of Mytable. When the Gender_column contains an 'F', the POSITION function returns a 1 as the starting location of the only character stored there. Then, it adds 1 to the 1 (POSITION value) to calculate the value 2. It uses the 2 in the SUBSTRING function to return the character starting in position 2 of the literal string 'FM' or the 'M'. Therefore, this row that was an 'F' becomes an 'M‘, and this now reflects a female instead of a male.

That is great for the females, but what about the 'M' values being converted to an 'F'? It works the same way but with different values being returned from the POSITION functions. Let's walk through this scenario. When the Gender_column contains an 'M', the POSITION function returns a 0 because the 'F' is not found. Then, it adds 1 to the 0 to calculate the value 1. It uses the 1 in the SUBSTRING function to return the character starting in position 1 of the literal string 'FM' which is the 'F' and converts this row from a male into a female.

Similar processing can be accomplished for more than a single character or multiple concurrent characters. Make sure that all multiple character values are the same length, even if literal spaces must be added at the end of the string.

The POSITION Command is brilliant with SUBSTRING

What was the starting position of the Substring in the above query? It was one. The ending position (FOR length) was calculated to look for the first space and then subtract 1. So, for "Research and Develop“, the starting position was one and For 9-1 = 8.

Quiz – Name that SUBSTRING Starting and For Length

Marketing (FROM __   FOR __ )

Research and Develop (FROM __   FOR __ )

Sales (FROM __ FOR __ )

Customer Support (FROM __ FOR __ )

Human Resources (FROM __ FOR __ )

Fill in the number for the FROM and the FOR numbers above for each row. Next page!

Answer to Quiz – Name that Starting and For Length

The FOR Length is calculated by finding the length up to the first SPACE and then subtracting 1.

Marketing (FROM 1   FOR 9 )

Research and Develop (FROM 1   FOR 8 )

Sales (FROM 1 FOR 5 )

Customer Support (FROM 1 FOR 8 )

Human Resources (FROM 1 FOR 5 )

The FOR was calculated in the POSITION Subquery

Using the SUBSTRING to Find the Second Word On

Dept_Name	Word2
Customer Support	Support
Human Resources	Resources
Research and Develop	and Develop

Notice we only had three rows come back. That is because our WHERE looks for only Department_Name that has multiple words. Then, notice that our starting position of the Substring is a subquery that looks for the first space. Then, it adds 1 to the starting position, and we have a staring position for the 2^nd word. We don't give a FOR length parameter, so it goes to the end.

Quiz – Why Did only one Row Return

Dept_Name	Third_Word
Research and Develop	Develop

Why did only one row come back?

Answer to Quiz – Why Did only one Row Return

Dept_Name	Third_Word
Research and Develop	Develop

It has 3 words

Why did only one row come back? It's the Only Department Name with three words. The SUBSTRING and the WHERE clause both look for the first space, and if they find it, then look for the second space. If they find that, add 1 to it and their Starting Position is the third word. There is no FOR position, so it defaults to "go to the end".

Concatenation

See those || ? Those represent concatenation. That allows you to combine multiple columns into one column. The || (Pipe Symbol) on your keyboard is just above the ENTER key. Don't put a space in between, but just put two Pipe Symbols together.

In this example, we have combined the first name, then a single space, and then the last name to get a new column called 'Full name' like Squiggy Jones.

Concatenation and SUBSTRING

Of the three items being concatenated together, what is the first item of concatenation in the example above? The first initial of the First_Name. Then, we concatenated a literal space and a period. Then, we concatenated the Last_Name.

Four Concatenations Together

Why did we TRIM the Last_Name? To get rid of the spaces or the output would have looked odd. How many items are being concatenated in the example above? There are 4 items concatenated. We start with the Last_Name (after we trim it), then we have a single space, then we have the First Initial of the First Name and then we have a Period.

Troubleshooting Concatenation

ERROR

What happened above to cause the error. Can you see it? The Pipe Symbols || have a space between them like | |, when it should be ||. It is a tough one to spot so be careful.

Miscellaneous Character Functions - ASCII

Compatibility: NPS Extensions

The ASCII function returns the numeric ASCII value of the first character in the data string.

Syntax: ASCII(<data-string>)

The ASCII function returns the numeric ASCII value of the first character in the data string. These functions all provide interesting operations that allow the transformation of data in one way and another. They are good for a variety of special situations.

Miscellaneous Character Functions - CHR

CHR Function

The CHR function returns the character with ASCII value from the data string.

Syntax: CHR(<data-string>)

Query

The CHR function returns the character with ASCII value from the data string. Notice that we put the number 67 inside our CHR(67). It returned the character C because 67 is a C in ASCII.

Miscellaneous Character Functions - INITCAP

The INITCAP function returns the first character from the data string as a capitalized letter.

Syntax: INITCAP(<data-string>)

The INITCAP function returns the first character from the data string as a capitalized letter. In our query above, we used the lower function to bring back the First_Name column in all lower case letters. We did the same thing with the second column in the query except we used the INITCAP command to capitalize the first letter.

.

Miscellaneous Character Functions - REPEAT

The REPEAT function returns the data string repeated the number of times specified within the function.

Syntax: REPEAT(<data-string>,<number-times>)

Query

The REPEAT function returns the data string repeated the number of times specified within the function. In our example, we have asked to REPEAT the First_Name three times.

Miscellaneous Character Functions - TRANSLATE

The TRANSLATE function any character in the data string that matches a character in the <from> set with the corresponding character in the <to> set. For example, translate('12345','14','ax') returns 'a23x5'.

Syntax: TRANSLATE(<data-string>,<from>,<to>)

Query

The TRANSLATE function any character in the data string that matches a character in the <from> set with the corresponding character in the <to> set. In our example, we did the TRANSLATE function to exchange a capital 'C' for a Capital 'Z‘, and then we did the TRANSLATE function to convert a Capital 'W' and a small 'a' to a Capital 'X' and a small 'y'.

Character Padding Functions - LPAD Function

LPAD puts one or more space characters on the Left or at the beginning of the string. It adds enough spaces to make the column the desired length as specified in the function.

Syntax for LPAD:

SELECT LPAD(<character-data-column>,<desired-length>)

Query

Result

These next set of functions all provide the opposite capability from the SUBSTRING. Instead of removing characters from a string, they put characters into a string. Normally we think of one or spaces as "padding" characters at the end of a string, on the right. These functions certainly do this operation, however they also can pad with characters other than a space and also at the beginning of the string, on the left. Our example query above pads spaces to the left of the column First_Name.

Character Padding Functions - RPAD Function

RPAD puts one or more space characters on the Right or at the end of the string. It adds enough spaces to make the column the desired length.

Syntax for RPAD:

SELECT RPAD(<character-data-column>,<desired-length>)

Query

Result

The example above uses RPAD to add make the VARCHAR first name 10 characters long with space at the end (right) for all names containing an 'i' as the second letter in the column First_Name.