ASCII ( string_exp )
Instruction: Returns the ASCII code value of the leftmost character of string_exp as integer.
Example: SELECT {fn ASCII("Name")} AS "ASCII", "Name" FROM Customer
Returns:
| ASCII |
Name |
| 65 |
Adam's Candy Shop |
| 65 |
Andres, Cristina |
| 66 |
Balak, Mike |
| 51 |
330 Main St |
| 82 |
Residential |
| 66 |
Blackwell, Edward |
| 67 |
Chapman, Natalie |
| 67 |
Cheknis, Benjamin |
| 67 |
Corcoran, Carol |
| … |
… |
CHAR ( code )
Instruction: Returns the character that has the ASCII code value specified by code . The value of code should be between 0 and 255; otherwise, the return value is data source-dependent.
Example: SELECT {fn CHAR(65)} + {fn CHAR(66)} AS "APlusB", "Name" FROM Customer
Returns:
| APlusB |
Name |
| AB |
Adam's Candy Shop |
| AB |
Andres, Cristina |
| AB |
Balak, Mike |
| AB |
330 Main St |
| AB |
Residential |
| … |
… |
CONCAT ( string_exp1, string_exp2 )
Instruction: Returns a character string that is the result of concatenating string_exp2 to string_exp1 . If the column represented by string_exp1 or string_exp2 contains a NULL value, a NULL value will be returned.
Example: SELECT {fn CONCAT("BillAddressState", "BillAddressPostalCode")} AS "STZip", "Name" FROM Customer
Returns:
| STZip |
Name |
| CA94555 |
Adam's Candy Shop |
| CA94326 |
Andres, Cristina |
| CA94326 |
Balak, Mike |
| CA94326 |
330 Main St |
| CA94326 |
Residential |
| … |
… |
DIFFERENCE ( string_exp1, string_exp2 )
Instruction: Returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.
Example: SELECT {fn DIFFERENCE("Name", 'Abercrombie, Kristy')} AS "Difference", "Name" FROM Customer
Returns:
| Difference |
Name |
| 199949 |
Adam's Candy Shop |
| 1102999 |
Andres, Cristina |
| 9910829 |
Balak, Mike |
| 1099949 |
330 Main St |
| 169901831 |
Residential |
| … |
… |
INSERT ( string_exp1, start, length, string_exp2 )
Instruction: Returns a character string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp1 , beginning at start .
Example: SELECT {fn INSERT("Name", 3, 2, '*Inserted*')} AS "Inserted", "Name" FROM Customer
Returns:
| Inserted |
Name |
| Ad*Inserted*'s Candy Shop |
Adam's Candy Shop |
| An*Inserted*es, Cristina |
Andres, Cristina |
| Ba*Inserted*k, Mike |
Balak, Mike |
| 33*Inserted* Main St |
330 Main St |
| Re*Inserted*dential |
Residential |
| … |
… |
LCASE ( string_exp )
Instruction: Converts all upper case characters in string_exp to lower case.
Example: SELECT {fn LCASE("Name")} AS "LCase", "Name" FROM Customer
Returns:
| LCase |
Name |
| adam's candy shop |
Adam's Candy Shop |
| andres, cristina |
Andres, Cristina |
| balak, mike |
Balak, Mike |
| 330 main st |
330 Main St |
| residential |
Residential |
| … |
… |
UCASE ( string_exp )
Instruction: Converts all lower case characters in string_exp to upper case.
Example: SELECT {fn UCASE("Name")} AS "UCase", "Name" FROM Customer
Returns:
| UCase |
Name |
| ADAM'S CANDY SHOP |
Adam's Candy |
| ANDRES, CRISTINA |
Andres, Cristina |
| BALAK, MIKE |
Balak, Mike |
| 330 MAIN ST |
330 Main St |
| RESIDENTIAL |
Residential |
| … |
… |
LEFT ( string_exp, count )
Instruction: Returns the leftmost count of characters of string_exp .
Example: SELECT {fn LEFT("Name", 5)} AS "Left5", "Name" FROM Customer
Returns:
| Left5 |
Name |
| Adam' |
Adam's Candy Shop |
| Andre |
Andres, Cristina |
| Balak |
Balak, Mike |
| 330 M |
330 Main St |
| Resid |
Residential |
| … |
… |
RIGHT ( string_exp, count )
Instruction: Returns the rightmost count of characters of string_exp .
Example: SELECT {fn RIGHT(“Name”, 5)} AS "Right5", "Name" FROM Customer
Returns:
| Right5 |
Name |
| Shop |
Adam's Candy Shop |
| Stina |
Andres, Cristina |
| Mike |
Balak, Mike |
| In St |
330 Main St |
| ntial |
Residential |
| … |
… |
LENGTH ( string_exp )
Instruction: Returns the number of characters in string_exp , excluding trailing blanks and the string termination character.
Example: SELECT {fn LENGTH("Name")} AS "Length", "Name" FROM Customer
Returns:
| Length |
Name |
| 17 |
Adam's Candy Shop |
| 16 |
Andres, Cristina |
| 11 |
Balak, Mike |
| 11 |
330 Main St |
| 11 |
Residential |
| … |
… |
BIT_LENGTH ( string_exp )
Instruction: Returns the bit length of string_exp , excluding trailing blanks and the string termination character.
Example: SELECT {fn BIT_LENGTH("Name")} AS "BitLength", "Name" FROM Customer
Returns:
| BitLength |
Name |
| 136 |
Adam's Candy Shop |
| 128 |
Andres, Cristina |
| 88 |
Balak, Mike |
| 88 |
330 Main St |
| 88 |
Residential |
| … |
… |
CHAR_LENGTH ( string_exp )
Instruction: Returns the number of chars in string_exp , excluding trailing blanks and the string termination character.
Example: SELECT {fn CHAR_LENGTH("Name")} AS "CharLength", "Name" FROM Customer
Returns:
| CharLength |
Name |
| 17 |
Adam's Candy Shop |
| 16 |
Andres, Cristina |
| 11 |
Balak, Mike |
| 11 |
330 Main St |
| 11 |
Residential |
| … |
… |
CHARACTER_LENGTH ( string_exp )
Instruction: Returns the number of characters in string_exp , excluding trailing blanks and the string termination character. (Almost the same as LENGTH function)
Example: SELECT {fn CHARACTER_LENGTH("Name")} AS "CharacterLength", "Name" FROM Customer
Returns:
| CharacterLength |
Name |
| 17 |
Adam's Candy Shop |
| 16 |
Andres, Cristina |
| 11 |
Balak, Mike |
| 11 |
330 Main St |
| 11 |
Residential |
| … |
… |
OCTET_LENGTH ( string_exp )
Instruction: Returns the octet length of string_exp , excluding trailing blanks and the string termination character. (Almost the same as LENGTH function)
Example: SELECT {fn OCTET_LENGTH("Name")} AS "OctetLength", "Name" FROM Customer
Returns:
| OctetLength |
Name |
| 17 |
Adam's Candy Shop |
| 16 |
Andres, Cristina |
| 11 |
Balak, Mike |
| 11 |
330 Main St |
| 11 |
Residential |
| … |
… |
LOCATE ( string_exp1, string_exp2[, start] )
Instruction: Returns the starting position of the first occurrence of string_exp1 within string_exp2 . The search for the first occurrence of string_exp1 begins with the first position in string_exp2 unless the optional argument, start is specified. If start is specified, the search begins with the character position indicated by the value of start . The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2 , the value 0 is returned.
Example: SELECT {fn LOCATE('a', "Name", 2)} AS "LocationOfA", "Name" FROM Customer
Returns:
| LocationOfA |
Name |
| 3 |
Adam's Candy Shop |
| 16 |
Andres, Cristina |
| 2 |
Balak, Mike |
| 6 |
330 Main St |
| 10 |
Residential |
| … |
… |
LTRIM ( string_exp )
Instruction: Returns the characters of string_exp , with leading blanks removed.
Example: SELECT {fn LTRIM("Name")} AS "LTrim", "Name" FROM Customer
Returns:
| LTrim |
Name |
| Adam's Candy |
Adam's Candy Shop |
| Andres, Cristina |
Andres, Cristina |
| Balak, Mike |
Balak, Mike |
| 330 Main St |
330 Main St |
| Residential |
Residential |
| … |
… |
RTRIM ( string_exp )
Instruction: Returns the characters of string_exp , with trailinging blanks removed.
Example: SELECT {fn RTRIM("Name")} AS "RTrim", "Name" FROM Customer
Returns:
| RTrim |
Name |
| Adam's Candy Shop |
Adam's Candy Shop |
| Andres, Cristina |
Andres, Cristina |
| Balak, Mike |
Balak, Mike |
| 330 Main St |
330 Main St |
| Residential |
Residential |
| … |
… |
REPEAT ( string_exp1, repeattimes )
Instruction: Returns the characters of string_exp1, with repeating it repeattimes.
Example: SELECT {fn REPEAT("Name",2)} as "Repeat2","Name" FROM Customer
Returns:
| Repeat2 |
Name |
| Adam's Candy Shop Adam's Candy Shop |
Adam's Candy Shop |
| Andres, Cristina Andres, Cristina |
Andres, Cristina |
| Balak, Mike Balak, Mike |
Balak, Mike |
| 330 Main St330 Main St |
330 Main St |
| Residential Residential |
Residential |
| … |
… |
POSITION ( string_exp1, string_exp2 )
Instruction: Returns a integer value that shows the position where string_exp1 first begins in string_exp2 (including spaces)
Example: SELECT {fn POSITION('a' IN "Name")} As "PositionOfA", "Name" FROM Customer
Returns:
| PositionOfA |
Name |
| 1 |
Adam's Candy Shop |
| 1 |
Andres, Cristina |
| 2 |
Balak, Mike |
| 0 |
330 Main St |
| 0 |
Residential |
| … |
… |
REPLACE ( string_exp1, string_exp2,string_exp3 )
Instruction: Returns the characters of string_exp3 which takes the place of string_exp2 value in column string_exp1 .
Example: SELECT {fn REPLACE("NAME",'330 Main St','abc')} AS "Replace","Name" FROM Customer
Returns:
| Replace |
Name |
| Adam's Candy Shop |
Adam's Candy Shop |
| Andres, Cristina |
Andres, Cristina |
| Balak, Mike |
Balak, Mike |
| abc |
330 Main St |
| Residential |
Residential |
| … |
… |
SOUNDEX ( string_exp )
Instruction: Returns a character string representing the sound of the words in string_exp .
Example: SELECT {fn SOUNDEX("Name")} AS "Soundex", "Name" FROM Customer
Returns:
| Soundex |
Name |
| ADAMACACAMDACAB |
Adam's Candy Shop |
| AMDRACACRACDAMA |
Andres, Cristina |
| BALACAMACA |
Balak, Mike |
| AMAMACD |
330 Main St |
| RACADAMDAL |
Residential |
| … |
… |
SPACE ( count )
Instruction: Returns a character string consisting of count spaces.
Example: SELECT ‘[‘ + {fn SPACE(10)} + ‘]' AS "TenSpaces", "Name" FROM Customer
Returns:
| TenSpaces |
Name |
| [ ] |
Adam's Candy Shop |
| [ ] |
Andres, Cristina |
| [ ] |
Balak, Mike |
| [ ] |
330 Main St |
| [ ] |
Residential |
| … |
… |
SUBSTRING ( string_exp, start, length )
Instruction: Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters.
Example: SELECT {fn SUBSTRING("Name", 2, 5)} AS "Middle5Characters", "Name" FROM Customer
Returns:
| Middle5Characters |
Name |
| dam's |
Adam's Candy Shop |
| ndres |
Andres, Cristina |
| alak, |
Balak, Mike |
| 30 Ma |
330 Main St |
| eside |
Residential |
| … |
… |
