String functions

ASCII function (ODBC compatible)

Syntax

ASCII ( char_expression )

Description

The scalar function ASCII returns the ASCII value of the first character of the given character expression.

Example

SELECT ASCII ( zip )

FROM customer ;

Notes

  • The argument to the function must be of character type.
  • The result is of type INTEGER.
  • If the argument char_expression evaluates to null, the result is null.

CHAR function (ODBC compatible)

Syntax

CHAR ( integer_expression )

Description

The scalar function CHAR returns a character string with the first character having an ASCII value equal to the argument expression. CHAR is identical to CHR but provides ODBC-compatible syntax.

Example

SELECT *

FROM customer

WHERE SUBSTR (zip, 1, 1) = CHAR (53) ;

Notes

  • The argument to the function must be of type INTEGER, TINYINT, or SMALLINT.
  • The result is of type character.
  • If the argument integer_expression evaluates to null, the result is null.

CHR function (extension)

Syntax

CHR ( integer_expression )

Description

The scalar function CHR returns a character string with the first character having an ASCII value equal to the argument expression.

Example

SELECT *

FROM customer

WHERE SUBSTR (zip, 1, 1) = CHR (53) ;

Notes

  • The argument to the function must be of type INTEGER, TINYINT, or SMALLINT.
  • The result is of type character.
  • If the argument integer_expression evaluates to null, the result is null.

CONCAT function (ODBC compatible)

Syntax

CONCAT ( char_expression , char_expression )

Description

The scalar function CONCAT returns a concatenated character string formed by concatenating argument one with argument two.

The CONCAT scalar function is similar to the concatenation operator. However, the concatenation operator allows easy concatenation of more than two character expressions by nesting the CONCAT function.

Example

SELECT name, empno, salary

FROM customer

WHERE project = CONCAT('US',proj_nam);

Notes

  • Both the arguments must be of character type.
  • If one of the arguments is a literal and the other one a field reference, concatenation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • The result belongs to the character set of the arguments.
  • If any of the argument expressions evaluates to null, the result is null.
  • The trailing blanks for the first arguments are removed.

DIFFERENCE function (ODBC compatible)

Syntax

DIFFERENCE ( string_exp1,string_exp2 )

Description

The scalar function DIFFERENCE returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.

Example

SELECT DIFFERENCE(name,'Robets')

FROM customer

WHERE name = 'Roberts';

 

DIFFEREN

 

2

1 record selected

Notes

  • The arguments of the function are of character types.
  • The result is INTEGER.
  • If the argument expression evaluates to null, the result is null.

INITCAP function (extension)

Syntax

INITCAP ( char_expression )

Description

The scalar function INITCAP returns the result of the argument character expression after converting the first character to uppercase and the subsequent characters to lowercase.

Example

SELECT INITCAP (name)

FROM customer ;

Notes

  • The argument to the function must be and of the character types.
  • The result type is based on the argument type.
  • If the argument expression evaluates to null, the result is null.

INSTR function (extension)

Syntax

INSTR ( char_expression, char_expression [, start_position [, occurrence]])

Description

The scalar function INSTR searches for the character string corresponding to the second argument in the character string corresponding to the first argument starting at start_position. If occurrence is specified, then INSTR searches for the nth occurrence where n is the value of the fourth argument.

The position (with respect to the start of string corresponding to the first argument) is returned if a search is successful. Zero is returned if no match can be found.

Example

SELECT cust_no, name

FROM customer

WHERE INSTR (LOWER (addr), 'heritage') > 0 ;

Notes

  • The first and second arguments must be of character type.
  • The third and fourth arguments, if specified, must be of type INTEGER.
  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • The values for specifying position in a character string starts from one. That is, the very first character in a string is at position one, the second character is at position two and so on.
  • If the third argument is not specified, a default value of one is assumed.
  • If the fourth argument is not specified, a default value of one is assumed.
  • The result is of type INTEGER.
  • If any of the argument expressions evaluates to null, the result is null.

INSERT function (ODBC compatible)

Syntax

INSERT(string_exp1,start,length,string_exp2)

Description

The scalar function INSERT returns a character string where length characters have been deleted from string_exp1 beginning at start and string_exp2 has been inserted into string_exp1, beginning at start. The above operation will be performed only if both the arguments belong to the same character set (exceptions are shown below in the Notes section).

Example

SELECT INSERT(name,2,4,'xx')

FROM customer

WHERE name = 'Goldman';

INSERT(NAME,2,4,XX)

 

Gxxan

1 record selected

Notes

  • The string_exp can be of fixed length or variable length character types.
  • The start and length can be of the type INTEGER, SMALLINT, TINYINT or BIGINT.
  • The string_exp2 has to belong to the string_exp1’s character set.
  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • The result string is of the type string_exp1 and will belong to same character set as string_exp1.
  • If any of the argument expression evaluates to a null, the result is a null.
  • If start is negative or zero, the result string evaluates to a null.
  • If length is negative, the result evaluates to a null.

LCASE function (ODBC compatible)

Syntax

LCASE ( char_expression )

Description

The scalar function LCASE returns the result of the argument character expression after converting all the characters to lowercase. LCASE is the same as LOWER but provides ODBC-compatible syntax.

Example

SELECT *

FROM customer

WHERE LCASE (name) = 'smith' ;

Notes

  • The argument to the function must be of character type.
  • The result type is based on the argument type.
  • If the argument expression evaluates to null, the result is null.

LEFT function (ODBC compatible)

Syntax

LEFT ( string_exp, count )

Description

The scalar function LEFT returns the leftmost count of characters of string_exp.

Example

SELECT LEFT(name,4)

FROM customer

WHERE name = 'Goldman';

 

LEFT(NAME,4)

 

Gold

1 record selected

Notes

  • The string_exp can be of type fixed or variable length character type.
  • The count can be of the type INTEGER, SMALLINT, BIGINT, or TINYINT.
  • If any of the arguments of the expression evaluates to a null, the result would be null.
  • If the count is negative, the result evaluates to a null.

LEN function (ODBC compatible)

Syntax

LEN ( char_expression )

Description

Synonymous with LENGTH()

LENGTH function (ODBC compatible)

Syntax

LENGTH ( char_expression )

Description

The scalar function LENGTH returns the number of characters in char_expression, excluding trailing blanks.

Example

SELECT name 'LONG NAME'

FROM customer

WHERE LENGTH (name) > 5 ;

Notes

  • The argument to the function must be of character type.
  • The result is of type INTEGER.
  • If the argument expression evaluates to null, the result is null.

LOWER function (SQL-92 compatible)

Syntax

LOWER ( char_expression )

Description

The scalar function LOWER returns the result of the argument character expression after converting all the characters to lowercase.

Example

SELECT *

FROM customer

WHERE LOWER (name) = 'smith' ;

Notes

  • The argument to the function must be of character type.
  • The result type is based on the argument type.
  • If the argument expression evaluates to null, the result is null.

LOCATE function (ODBC compatible)

Syntax

LOCATE( char-expr1 , char-expr2, [start-position] )

Description

The scalar function LOCATE returns the location of the first occurrence of char-expr1 in char-expr2. If the function includes the optional integer argument start-position, LOCATE begins searching char-expr2 at that position. If the function omits the start-position argument, LOCATE begins its search at the beginning of char-expr2.

LOCATE denotes the first character position of a character expression as 1. If the search fails, LOCATE returns 0.

  • char_expr1 and char_expr2 must evaluate to a CHAR value.
  • start, if supplied, must evaluate to an INTEGER value.
  • If either expression is null, LOCATE returns NULL.

Example

The following example uses two string literals as character expressions. LOCATE returns a value of six:

SELECT LOCATE('this', 'test this test', 1) FROM TEST;

LOCATE(THIS,

------------

6

1 record selected

LPAD function (extension)

Syntax

LPAD ( char_expression, length [, pad_expression] )

Description

The scalar function LPAD pads the character string corresponding to the first argument on the left with the character string corresponding to the third argument so that after the padding, the length of the result is length.

Example

SELECT LPAD (name, 30)

FROM customer ;

SELECT LPAD (name, 30, '.')

FROM customer ;

Notes

  • The first argument to the function must be of character type.
  • The second argument to the function must be of type INTEGER.
  • The third argument, if specified, must be of character type.
  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • If the third argument is not specified, the default value is a string of length one containing one blank.
  • If L1 is the length of the first argument and L2 is the value of the second argument, then:
    • If L1 is less than L2, the number of characters padded is equal to L2 - L1.
    • If L1 is equal to L2, no characters are padded and the result string is the same as the first argument.
    • If L1 is greater than L2, the result string is equal to the first argument truncated to the first L2 characters.
  • The result is of character type whose character set is same as that of the arguments.
  • If the argument expression evaluates to null, the result is null.

LTRIM function (ODBC compatible)

Syntax

LTRIM ( char_expression [ , char_set ] )

Description

The scalar function LTRIM removes all the leading characters in char_expression, that are present in char_set and returns the resultant string. Thus, the first character in the result is guaranteed to be not in char_set. If the char_set argument is omitted, the function removes the leading and trailing blanks from char_expression.

Example

SELECT name, LTRIM (addr, ' ')

FROM customer ;

Notes

  • The first and second arguments to the function must be of character type.
  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • The result is of character type whose character set is same as that of the arguments.
  • If the argument expression evaluates to null, the result is null.

MONTH function (ODBC compatible)

Syntax

MONTH ( date_expression )

Description

The scalar function MONTH returns the month in the year specified by the argument as a short integer value in the range of 1 - 12.

Example

SELECT *

FROM orders

WHERE MONTH (order_date) = 6 ;

Notes

  • The argument to the function must be of type DATE.
  • The argument must be specified in the format MM/DD/YYYY.
  • The result is of type SHORT.
  • If the argument expression evaluates to null, the result is null.

OCTET_LENGTH SQL Function

Syntax

OCTET_LENGTH ( char_expression )

Description

The scalar function OCTET_LENGTH returns the number of bytes in the char_expression.

Example

SELECT NAME FROM customers

WHERE OCTET_LENGTH( name ) < 10 ;

NAME

----

rathan

Dany

2 records selected

Note: SQL-99 and ODBC compatible

OVERLAY SQL Function

Syntax

OVERLAY ( char_exp1 PLACING char_exp2 FROM start_position

[ FOR length ] )

The argument length is optional. If length is not specified, the scalar function returns a character string where char_exp2 has been appended to the char_exp1 beginning at start_position.

Description

The scalar function OVERLAY returns a character string where length characters have been deleted from the char_exp1 beginning at start_position and char_exp2 has been inserted into char_exp1 beginning at start_position.

Example

SELECT OVERLAY( name PLACING 'Technologies' FROM 9 FOR 12 )

From customers

WHERE name = 'FairCom Corporation' ;

OVERLAY(NAME,8,9,TECHNOLOGIES)

--------------------

FairCom Technologies

1 record selected

Details

  • The char_exp can be of fixed length or variable character types.
  • The start_position and length can be of the type INTEGER, SMALLINT, TINYINT or BIGINT.
  • The char_exp2 has to belong to char_exp1’s character set.
  • The result expression is of the type char_exp1 and will belong to the same character set as char_exp1.
  • If start_position is negative or zero, the result expression evaluates to a null.
  • If length is negative the result evaluates to a null.

Note: SQL-99 compatible

PREFIX function (extension)

Syntax

PREFIX(char_expression, start_position, char_expression)

Description

The scalar function PREFIX returns the substring of a character string starting from the position specified by start position, and ending before the specified character.

Arguments

char_expression

An expression that evaluates to a character string, typically a character-string literal or column name. If the expression evaluates to null, PREFIX returns null.

start_position

An expression that evaluates to an integer value. PREFIX searches the string specified in the first argument starting at that position. A value of one indicates the first character of the string.

char_expression

An expression that evaluates to a single character. PREFIX returns the substring that ends before that character. If PREFIX does not find the character, it returns the substring beginning with start_position, to the end of the string. If the expression evaluates to more than one character, PREFIX ignores all but the first character.

Example

SELECT C1, C2, PREFIX(C1, 1, '.') FROM T1;

C1 C2 PREFIX(C1,1,.

-- -- -------------

test.pref . test

pref.test s pref

2 records selected

SELECT C1, C2, PREFIX(C1, 1, C2) FROM T1;

C1 C2 PREFIX(C1,1,C

-- -- -------------

test.pref . test

pref.test s pref.te

2 records selected

SELECT C1, C2, PREFIX(C1, 1, 'Q') FROM T1;

C1 C2 PREFIX(C1,1,Q

-- -- -------------

test.pref . test.pref

pref.test s pref.test

2 records selected

Notes

  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.

POSITION SQL Function

Syntax

POSITION ( char_exp1 IN char_exp2 )

Description

The scalar function POSITION returns the first occurrence of char_exp1 in char_exp2.

Example

SELECT POSITION ( 'Fa' IN 'FairCom' )

FROM customers ;

POSITION

--------

1

1 record selected

Details

If any one of the expressions evaluates to be null, POSITION returns null.

Note: SQL-99 and ODBC compatible

REPEAT function (ODBC compatible)

Syntax

REPEAT ( string_exp,count )

Description

The scalar function REPEAT returns a character string composed of string_exp repeated count times.

Example

SELECT REPEAT(fld1,3)

FROM test100

WHERE fld1 = 'Afghanistan'

 

Results

 

REPEAT(FLD1,3)

 

AfghanistanAfghanistanAfghanistan

 

1 record selected

Notes

  • The string expression can be any of the fixed length or variable length character types.
  • The count can be of the type INTEGER, SMALLINT, BIGINT, or TINYINT.
  • If any of the arguments of the expression evaluates to a null, the result would be null.
  • If the count is negative or zero, the result evaluates to a null.

REPLACE function (ODBC compatible)

Syntax

REPLACE ( string_exp1,string_exp2,string_exp3 )

Description

The scalar function REPLACE replaces all occurrences of string_exp2 in string_exp1 with string_exp3. The resultant character string will have the same character set as that of the arguments.

Example

SELECT REPLACE ( name,'mi','moo' )

FROM customer

WHERE name = 'Smith';

 

REPLACE(NAME,MI,MOO)

 

Smooth

1 record selected

Notes

  • string_exp can be any of the type fixed or variable length character types.
  • If any of the arguments of the expression evaluates to null, the result is null.
  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • If the replacement string is not found in the search string, it returns the original string.

RIGHT function (ODBC compatible)

Syntax

RIGHT ( string_exp, count )

Description

The scalar function RIGHT returns the rightmost count of characters of string_exp.

Example

SELECT RIGHT(fld1,6)

FROM test100

WHERE fld1 = 'Afghanistan';

RIGHT(FLD1,6)

 

nistan

1 record selected

Notes

  • The string_exp can be any of the fixed or variable length Character types.
  • The count can be of the type INTEGER, SMALLINT, BIGINT, or TINYINT.
  • If any of the arguments of the expression evaluates to a null, the result would be null.
  • If the count is negative, the result evaluates to a null.

RPAD function (extension)

Syntax

RPAD ( char_expression, length [, pad_expression] )

Description

The scalar function RPAD pads the character string corresponding to the first argument on the right with the character string corresponding to the third argument so that after the padding, the length of the result would be equal to the value of the second argument length.

Example

SELECT RPAD (name, 30)

FROM customer ;

 

SELECT RPAD (name, 30, '.')

FROM customer ;

Notes

  • The first argument to the function must be of character type.
  • The second argument to the function must be of type INTEGER.
  • The third argument, if specified, must be of character type.
  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • If L1 is the length of the first argument and L2 is the value of the second argument, then:
    • If L1 is less than L2, the number of characters padded is equal to L2 - L1.
    • If L1 is equal to L2, no characters are padded and the result string is the same as the first argument.
    • If L1 is greater than L2, the result string is equal to the first argument truncated to first L2 characters.
  • The result is of character type whose character set is same as that of it’s arguments.
  • If the argument expression evaluates to null, the result is null.

ROWIDTOCHAR (extension)

Syntax

ROWIDTOCHAR ( expression )

Description

The scalar function ROWIDTOCHAR returns the character form of a ROWID contained in the input argument.

Example

The following example uses ROWIDTOCHAR to convert a row identifier from its internal representation to a character string.

SELECT cust_no,

SUBSTR (ROWIDTOCHAR (ROWID), 1, 8) 'PAGE NUMBER',

SUBSTR (ROWIDTOCHAR (ROWID), 10, 4) 'LINE NUMBER',

SUBSTR (ROWIDTOCHAR (ROWID), 15, 4) 'TABLE SPACE NUMBER'

FROM customer ;

Notes

  • The argument to the function must be a ROWID.
  • The result is of type NCHAR for Unicode builds and CHAR for ANSI builds.
  • If the argument expression evaluates to null, the result is null.

RTRIM function (ODBC compatible)

Syntax

RTRIM ( char_expression [ , char_set ] )

Description

The scalar function RTRIM removes all the trailing characters in char_expression, that are present in char_set and returns the resultant string. Thus, the last character in the result is guaranteed to be not in char_set. If the char_set argument is omitted, the function removes the leading and trailing blanks from char_expression.

Example

SELECT RPAD ( RTRIM (addr, ' '), 30, '.')

FROM customer ;

Notes

  • The first and second arguments to the function must be of character type.
  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • The result is of character type.
  • If the argument expression evaluates to null, the result is null.

SPACE function (ODBC compatible)

Syntax

SPACE ( count )

Description

The scalar function SPACE returns a character string consisting of count spaces.

Example

SELECT CONCAT(SPACE(3), name)

FROM customer

WHERE name = 'Roberts';

 

CONCAT ( ,NAME)

 

Roberts

1 record selected

Notes

  • The count argument can be of type INTEGER, SMALLINT, BIGINT, or TINYINT.
  • If count is null, the result is null.
  • If count is negative, the result is null.

SOUNDEX function (ODBC compatible)

Syntax

SOUNDEX ( string_exp )

Description

The scalar function SOUNDEX returns a four-character soundex code for character strings that are composed of a contiguous sequence of valid single- or double-byte roman letters.

Example

SELECT SOUNDEX('Roberts')

FROM syscalctable;

SUBSTR function (extension)

Syntax

SUBSTR ( char_expression, start_position [, length ] )

Description

The scalar function SUBSTR returns the substring of the character string corresponding to the first argument starting at start_position and length characters long. If the third argument length is not specified, substring starting at start_position up to the end of char_expression is returned.

Example

SELECT name, '(', SUBSTR (phone, 1, 3) , ')',

SUBSTR (phone, 4, 3), '-',

SUBSTR (phone, 7, 4)

FROM customer ;

Notes

  • The first argument must be of character type.
  • The second argument must be of type INTEGER.
  • The third argument, if specified, must be of type INTEGER.
  • The values for specifying position in the character string start from one. The very first character in a string is at position one, the second character is at position two and so on.
  • The result is of character type.
  • If any of the argument expressions evaluates to null, the result is null.

SUBSTRING SQL Function (SQL-99 compatible)

Syntax

SUBSTRING (char_exp FROM start_position [ FOR length ])

Description

The scalar function SUBSTRING returns the substring of a character string corresponding to the first argument starting at start_position and length characters long.

The third argument length is optional. If the length is not specified the substring starting at start_position up to the end of the char_exp is returned.

Example

SELECT SUBSTRING( 'FairCom Corporation' FROM 1 FOR 7)

From customers ;

FAIRCOM

------

FairCom

1 record selected

Details

  • The first argument, char_exp, must be of character type.
  • The second argument, start_position, must be of type INTEGER.
  • The third argument, length, if specified, must be of type INTEGER.
  • The values for specifying position in char_exp start from 1. The very first character in a string is at position 1, the second character is at position 2 and so on.
  • The result is of character type.
  • If any of the argument expressions evaluates to null, the result is null.

Note: SQL-99 compatible

SUBSTRING function (ODBC compatible)

Syntax

SUBSTRING ( char_expression, start_position [, length ] )

Description

The scalar function SUBSTRING returns the substring of the character string corresponding to the first argument starting at start_position and length characters long. If the third argument, length, is not specified, the substring starting at start_position up to the end of char_expression is returned. SUBSTRING is identical to SUBSTR and provides ODBC-compatible syntax.

Example

SELECT name, '(', SUBSTRING (phone, 1, 3) , ')',

SUBSTRING (phone, 4, 3), '-',

SUBSTRING (phone, 7, 4)

FROM customer ;

Notes

  • The first argument must be of character type.
  • The second argument must be of type INTEGER.
  • The third argument, if specified, must be of type INTEGER.
  • The values for specifying position in the character string start from one. The very first character in a string is at position one, the second character is at position two and so on.
  • The result is of character type.
  • If any of the argument expressions evaluates to null, the result is null.

SUFFIX function (extension)

Syntax

SUFFIX(char_expression1, start_position, char_expression2)

Description

The scalar function SUFFIX returns the substring of a character string starting after the position specified by start_position and the second char_expression, to the end of the string.

Arguments

char_expression1

An expression that evaluates to a character string, typically a character-string literal or column name. If the expression evaluates to null, SUFFIX returns null.

start_position

An expression that evaluates to an integer value. SUFFIX searches the string specified in the first argument starting at that position. A value of one indicates the first character of the string.

char_expression2

An expression that evaluates to a single character. SUFFIX returns the substring that begins with that character. If SUFFIX does not find the character after start_position, it returns null. If the expression evaluates to more than one character, SUFFIX ignores all but the first character.

Example

SELECT C1, C2, SUFFIX(C1, 6, '.') FROM T1;

C1 C2 SUFFIX(C1,6,.

-- -- -------------

test.pref .

pref.test s

2 records selected

SELECT C1, C2, SUFFIX(C1, 1, C2) FROM T1;

C1 C2 SUFFIX(C1,1,C

-- -- -------------

test.pref . pref

pref.test s t

2 records selected

SELECT C1, C2, SUFFIX(C1, 6, '.') FROM T1;

C1 C2 SUFFIX(C1,6,.

-- -- -------------

test.pref .

pref.test s

2 records selected

NOTES

  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.

TRANSLATE function (extension)

Syntax

TRANSLATE ( char_expression, from_set, to_set )

Description

The scalar function TRANSLATE translates each character in char_expression that is in from_set to the corresponding character in to_set. The translated character string is returned as the result. This function is similar to the Oracle TRANSLATE function.

Example

This example substitutes underscores for spaces in customer names.

SELECT TRANSLATE (customer_name, ' ', '_')

"TRANSLATE Example" from customers;

TRANSLATE EXAMPLE

-----------------

Sports_Cars_Inc.__________________________________

Mighty_Bulldozer_Inc._____________________________

Ship_Shapers_Inc._________________________________

Tower_Construction_Inc.___________________________

Chemical_Construction_Inc.________________________

Aerospace_Enterprises_Inc.________________________

Medical_Enterprises_Inc.__________________________

Rail_Builders_Inc.________________________________

Luxury_Cars_Inc.__________________________________

Office_Furniture_Inc._____________________________

10 records selected

Notes

  • char_expression, from_set, and to_set can be any character expression.
  • For each character in char_expression, TRANSLATE checks for the same character in from_set:
  • If it is in from_set, TRANSLATE translates it to the corresponding character in to_set (if the character is the nth character in from_set, the nth character in to_set).
  • If the character is not in from_set TRANSLATE does not change it.
  • If from_set is longer than to_set, TRANSLATE does not change trailing characters in from_set that do not have a corresponding character in to_set.
  • If either from_set or to_set is null, TRANSLATE does nothing.

TRIM SQL Function

Syntax

TRIM([trim_specification] [trim_characters ] FROM char_expression )

trim specification ::

LEADING

| TRAILING

| BOTH

Description

The function TRIM removes leading, trailing or both leading and trailing characters from the char_expression, that are present in trim_characters and returns the resultant string.

Example

SELECT TRIM ( LEADING 'x' FROM 'xxFairCom' )

FROM customers ;

FAIRCOM

------

FairCom

1 record selected

Details

  • If trim_specification is not specified, the function TRIM removes both the leading and trailing trim_characters from the char_expression.
  • If trim_characters are not specified, the function TRIM removes leading, trailing or both leading and trailing blank spaces from the char_expression.
  • If both trim_specification and trim_characters are not specified, the function TRIM removes both leading and trailing blank spaces from the char_expression.

Note: SQL-99 compatible

UCASE function (ODBC compatible)

Syntax

UCASE ( char_expression )

Description

The scalar function UCASE returns the result of the argument character expression after converting all the characters to uppercase. UCASE is identical to UPPER, but provides ODBC-compatible syntax.

Example

SELECT *

FROM customer

WHERE UCASE (name) = 'SMITH' ;

Notes

  • The argument to the function must be of character type.
  • The result is of character type.
  • If the argument expression evaluates to null, the result is null.

UPPER function (SQL-92 compatible)

Syntax

UPPER ( char_expression )

Description

The scalar function UPPER returns the result of the argument character expression after converting all the characters to uppercase.

Example

SELECT *

FROM customer

WHERE UPPER (name) = 'SMITH' ;

Notes

  • The argument to the function must be of character type.
  • The result is of character type.
  • If the argument expression evaluates to null, the result is null.
text manipulation string methods

Was this article helpful?

Give feedback about this article

Related Articles