Sams Teach Yourself SQL in 24 Hours

Sams Teach Yourself SQL in 24 Hours

By Ron Plew and Ryan Stephens

Various Common Character Functions

Character functions are used mainly to compare, join, search, and extract a segment of a string or a value in a column. There are several character functions available to the SQL programmer.

The following sections illustrate the application of ANSI concepts in some of the leading implementations of SQL, such as in Oracle, Sybase, SQLBase, Informix, and SQL Server.

Note

The ANSI concepts discussed in this book are just that—concepts. Standards provided by ANSI are simply guidelines for how the use of SQL in a relational database should be implemented. With that thought, keep in mind that the specific functions discussed in this hour are not necessarily the exact functions that you may use in your particular implementation. Yes, the concepts are the same, and the way the functions work are generally the same, but function names and actual syntax may differ.

Concatenation

Concatenation, along with most other functions, is represented slightly differently among various implementations. The following examples show the use of concatenation in Oracle and SQL Server.

In Oracle

SELECT 'JOHN' || 'SON' returns JOHNSON

In SQL Server

SELECT 'JOHN' + 'SON' returns JOHNSON

In MySQL

SELECT CONCAT('JOHN' , 'SON') returns JOHNSON

The syntax for Oracle is

   syntax_icon.gif
COLUMN_NAME || [ '' || ] COLUMN_NAME [ COLUMN_NAME ]

The syntax for SQL Server is

COLUMN_NAME + [ '' + ] COLUMN_NAME [ COLUMN_NAME ]

The syntax for MySQL is

CONCAT(COLUMN_NAME , [ '' , ] COLUMN_NAME [ COLUMN_NAME ]

Example

Meaning

SELECT CITY + STATE FROM EMPLOYEE_TBL;

This SQL Server statement concatenates the values for city and state into one value.

SELECT CITY ||', '|| STATE FROM EMPLOYEE_TBL;

This Oracle statement concatenates the values for city and state into one value, placing a comma between the values for city and state.

SELECT CITY + '' + STATE FROM EMPLOYEE_TBL;

This SQL Server statement concatenates the values for city and state into one value, placing a space between the two original values.

Example:

   mysql_icon.gif
   input_icon.gif

   SELECT LAST_NAME || ', ' || FIRST_NAME NAME

   FROM EMPLOYEE_TBL;

   output_icon.gif
NAME
-----------------
STEPHENS, TINA
PLEW, LINDA
GLASS, BRANDON
GLASS, JACOB
WALLACE, MARIAH
SPURGEON, TIFFANY

6 rows selected.

Note

Notice the use of single quotation marks and a comma in the preceding SQL statement. Most characters and symbols are allowed if enclosed by single quotations marks. Some implementations may use double quotation marks for literal string values.

TRANSLATE

The TRANSLATE function searches a string of characters and checks for a specific character, makes note of the position found, searches the replacement string at the same position, and then replaces that character with the new value. The syntax is

   syntax_icon.gif
TRANSLATE(CHARACTER SET, VALUE1, VALUE2)

Example

Meaning

SELECT TRANSLATE (CITY,'IND','ABC' FROM EMPLOYEE_TBL);B, and D with C.

This SQL statement substitutes every occurrence of I in the string with A, replaces all occurrences with C.

The following example illustrates the use of TRANSLATE with real data:

   mysql_icon.gif
   input_icon.gif

   SELECT CITY, TRANSLATE(CITY,'IND','ABC')

   FROM EMPLOYEE_TBL;

   output_icon.gif
CITY         TRANSLATE(CI
------------ ------------
GREENWOOD    GREEBWOOC
INDIANAPOLIS ABCAABAPOLAS
WHITELAND    WHATELABC
INDIANAPOLIS ABCAABAPOLAS
INDIANAPOLIS ABCAABAPOLAS
INDIANAPOLIS ABCAABAPOLAS

6 rows selected.

Notice in this example that all occurrences of I were replaced with A, N with B, and D with C. In the city INDIANAPOLIS, IND was replaced with ABC, but in GREENWOOD, D was replaced with C. Also notice how the value WHITELAND was translated.

REPLACE

The REPLACE function is used to replace every occurrence of a character(s) with a specified character(s). The use of this function is similar to the TRANSLATE function; only one specific character or string is replaced within another string. The syntax is

   syntax_icon.gif

REPLACE('VALUE', 'VALUE', [ NULL ] 'VALUE')

Example

Meaning

SELECT REPLACE(FIRST__'T', 'B') FROM EMPLOYEE_TBL

This statement returns all NAME, the first names and changes any occurrence of T to a B. 

   input_icon.gif

   SELECT CITY, REPLACE(CITY,'I','Z')

   FROM EMPLOYEE_TBL;

   output_icon.gif
CITY         REPLACE(CITY)
------------ -------------
GREENWOOD    GREENWOOD
INDIANAPOLIS ZNDZANAPOLZS
WHITELAND    WHZTELAND
INDIANAPOLIS ZNDZANAPOLZS
INDIANAPOLIS ZNDZANAPOLZS
INDIANAPOLIS ZNDZANAPOLZS

6 rows selected.

UPPER

Most implementations have a way to control the case of data by using functions. The UPPER function is used to convert lowercase letters to uppercase letters for a specific string.

The syntax is as follows:

   syntax_icon.gif
UPPER(character string)

Example

Meaning

SELECT UPPER(LAST_NAME)FROM EMPLOYEE_TBL;LAST_NAME

This SQL statement converts all characters in the column to uppercase. 

   input_icon.gif

   SELECT UPPER(CITY)

   FROM EMPLOYEE_TBL;

   output_icon.gif
UPPER(CITY)
-------------
GREENWOOD
INDIANAPOLIS
WHITELAND
INDIANAPOLIS
INDIANAPOLIS
INDIANAPOLIS

6 rows selected.

LOWER

The converse of the UPPER function, the LOWER function is used to convert uppercase letters to lowercase letters for a specific string.

The syntax is as follows:

   syntax_icon.gif
LOWER(character string)

Example

Meaning

SELECT LOWER(LAST_NAME)FROM EMPLOYEE_TBL;LAST_NAME

This SQL statement converts all characters in the column to lowercase. 

   input_icon.gif

   SELECT LOWER(CITY)

   FROM EMPLOYEE_TBL;

   output_icon.gif
LOWER(CITY)
-------------
greenwood
indianapolis
whiteland
indianapolis
indianapolis
indianapolis

6 rows selected.

SUBSTR

Taking an expression's substring is common in most implementations of SQL, but the function name may differ, as shown in the following Oracle and SQL Server examples.

The syntax for Oracle is

   syntax_icon.gif
SUBSTR(COLUMN NAME, STARTING POSITION, LENGTH)

The syntax for SQL Server is

SUBSTRING(COLUMN NAME, STARTING POSITION, LENGTH)

The only difference between the two implementations is the spelling of the function name.

Example

Meaning

SELECT SUBSTRING(EMP_ID,1,3) FROM EMPLOYEE_TBL

This SQL statement returns the first three characters of EMP_ID.

SELECT SUBSTRING(EMP_ID,4,2) FROM EMPLOYEE_TBL

This SQL statement returns the fourth and fifth characters of EMP_ID.

SELECT SUBSTRING(EMP_ID,6,4) FROM EMPLOYEE_TBL

This SQL statement returns the sixth through the ninth characters of EMP_ID.

The following is an example that is compatible with Microsoft SQL Server and MySQL:

   input_icon.gif

   SELECT EMP_ID, SUBSTRING(EMP_ID,1,3)

   FROM EMPLOYEE_TBL;

   output_icon.gif
EMP_ID    SUB
--------- ---
311549902 311
442346889 442
213764555 213
313782439 313
220984332 220
443679012 443

6 rows affected.
mysql_icon.gif
   input_icon.gif

   SELECT EMP_ID, SUBSTR(EMP_ID,1,3)

   FROM EMPLOYEE_TBL;

   output_icon.gif
EMP_ID    SUB
--------- ---
311549902 311
442346889 442
213764555 213
313782439 313
220984332 220
443679012 443

6 rows selected.

Note

Notice the difference between the feedback of the two queries. The first example returns the feedback 6 rows affected and the second returns 6 rows selected. You will see differences such as this between the various implementations.

INSTR

The INSTR function is a variation of the POSITION function; it is used to search a string of characters for a specific set of characters and report the position of those characters. The syntax is as follows:

   syntax_icon.gif
INSTR(COLUMN NAME, 'SET',
[ START POSITION [ , OCCURRENCE ] ]);

Example

Meaning

SELECT INSTR(STATE,'I',1,1) FROM EMPLOYEE_TBL;

This SQL statement returns the position of the first occurrence of the letter I for each state in EMPLOYEE_TBL. 

   mysql_icon.gif
   input_icon.gif

   SELECT PROD_DESC,
       
   INSTR(PROD_DESC,'A',1,1)

   FROM PRODUCTS_TBL;

   output_icon.gif
PROD_DESC                 INSTR(PROD_DESC,'A',1,1)
------------------------- --------------------------
WITCHES COSTUME                                  0
PLASTIC PUMPKIN 18 INCH                          3
FALSE PARAFFIN TEETH                             2
LIGHTED LANTERNS                                10
ASSORTED COSTUMES                                1
CANDY CORN                                       2
PUMPKIN CANDY                                   10
PLASTIC SPIDERS                                  3
ASSORTED MASKS                                   1
KEY CHAIN                                        7
OAK BOOKSHELF                                    2

11 rows selected.

Notice that if the searched character A was not found in a string, the value 0 was returned for the position.

LTRIM

The LTRIM function is another way of clipping part of a string. This function and SUBSTRING are in the same family. LTRIM is used to trim characters from the left of a string. The syntax is

   syntax_icon.gif

LTRIM(CHARACTER STRING [ ,'set' ])

Example

Meaning

SELECT LTRIM(FIRST_NAME,'LES') FROM CUSTOMER_TBL WHERE FIRST_NAME ='LESLIE';

This SQL statement trims the characters LES from the left of all names that are LESLIE. 

   mysql_icon.gif
   input_icon.gif

   SELECT POSITION, LTRIM(POSITION,'SALES')

   FROM EMPLOYEE_PAY_TBL;

   output_icon.gif
POSITION        LTRIM(POSITION,
--------------- ---------------
MARKETING       MARKETING
TEAM LEADER     TEAM LEADER
SALES MANAGER   MANAGER
SALESMAN        MAN
SHIPPER         HIPPER
SHIPPER         HIPPER

6 rows selected.

The S in SHIPPER was trimmed off, even though SHIPPER does not contain the string SALES. The first four characters of SALES were ignored. The searched characters must appear in the same order of the search string and must be on the far left of the string. In other words, LTRIM will trim off all characters to the left of the last occurrence in the search string.

RTRIM

Like LTRIM, the RTRIM function is used to trim characters from the right of a string. The syntax is

   syntax_icon.gif
RTRIM(CHARACTER STRING [ ,'set' ])

Example

Meaning

SELECT RTRIM(FIRST_NAME, 'ON') FROM EMPLOYEE_TBL WHERE FIRST_NAME = 'BRANDON';

This SQL statement returns the first name BRANDON and trims the ON, leaving BRAND as a result. 

   mysql_icon.gif
   input_icon.gif

   SELECT POSITION, RTRIM(POSITION,'ER')

   FROM EMPLOYEE_PAY_TBL;

   output_icon.gif
POSITION        RTRIM(POSITION,
--------------- ---------------
MARKETING       MARKETING
TEAM LEADER     TEAM LEAD
SALES MANAGER   SALES MANAG
SALESMAN        SALESMAN
SHIPPER         SHIPP
SHIPPER         SHIPP

6 rows selected.

The string ER was trimmed from the right of all applicable strings.

DECODE

The DECODE function is not ANSI—at least not at the time of this writing—but its use is shown here because of its great power. This function is used in SQLBase, Oracle, and possibly other implementations. DECODE is used to search a string for a value or string, and if the string is found, an alternative string is displayed as part of the query results.

The syntax is

   syntax_icon.gif
DECODE(COLUMN NAME, 'SEARCH1', 'RETURN1',[ 'SEARCH2', 'RETURN2' ,'DEFAULT VALUE'])

Example

Meaning

SELECT DECODE(LAST_NAME,'SMITH', 'JONES','OTHER') FROM EMPLOYEE_TBL;

This query searches the value of all last names in EMPLOYEE_TBL; if the value SMITH is found, JONES is displayed in its place.Any other names are displayed as OTHER, which is called the default value.

In the following example, DECODE is used on the values for CITY in EMPLOYEE_TBL:

   mysql_icon.gif
   input_icon.gif

   SELECT CITY,
       
   DECODE(CITY,'INDIANAPOLIS','INDY',
                   
   'GREENWOOD','GREEN', 'OTHER')

   FROM EMPLOYEE_TBL;

   output_icon.gif
CITY         DECOD
------------ -----
GREENWOOD    GREEN
INDIANAPOLIS INDY
WHITELAND    OTHER
INDIANAPOLIS INDY
INDIANAPOLIS INDY
INDIANAPOLIS INDY

6 rows selected.

The output shows the value INDIANAPOLIS displayed as INDY, GREENWOOD displayed as GREEN, and all other cities displayed as OTHER.

Share ThisShare This

Informit Network