Character Functions
Character Functions#
Character functions return either character or numeric values. They can be classified into two types depending on which kind of data they return
Character functions that return character data#
CHR, CONCAT, DIGITS, INITCAP, LOWER, LPAD, LTRIM, NCHR, PKCS7PAD16, PKCS7UNPAD16,RANDOM_STRING, REPLICATE, REPLACE2, REVERSE_STR, RPAD, RTRIM, STUFF, SUBSTRB(SUBSTRING), TRANSLATE, TRIM, UPPER
Character functions that return numeric data#
ASCII, INSTR(POSITION), CHAR_LENGTH(CHARACTER_LENGTH, LENGTH), INSTRB, OCTET_LENGTH(LENGTHB), SIZEOF
ASCII#
Syntax#
ASCII (expr)
Description#
ASCII returns the ASCII code value for the first (i.e. leftmost) character in expr.
Example#
<Query> Output the ASCII code for the letter 'A'.
SELECT ASCII('A') FROM DUAL;
ASCII('A')
--------------
65
1 ROW SELECTED.
CHAR_LENGTH, CHARACTER_LENGTH, LENGTH#
Syntax#
CHAR_LENGTH (expr)
CHARACTER_LENGTH (expr)
LENGTH (expr)
Description#
These return the length of the input character string.
Example#
<Query> Output the length of the street addresses in the managers table. Note that the character set is KO16KSC5601.
CREATE TABLE managers
( mgr_no INTEGER PRIMARY KEY,
m_lastname VARCHAR(20),
m_firstname VARCHAR(20),
address VARCHAR(60)
);
INSERT INTO managers
VALUES(1, 'Jones', 'Davey', '3101 N. Wabash Ave. Brooklyn, NY');
INSERT INTO managers
VALUES(15, 'Min', 'Sujin', '서울 마포구 아현 1');
SELECT CHAR_LENGTH(address) FROM managers;
CHAR_LENGTH (ADDRESS)
------------------------
32
11
2 rows selected.
CHOSUNG#
Syntax#
CHOSUNG (expr1)
Description#
This extracts only the first consonant of each character from the input Hangul string and returns it. expr1 can be a CHAR or VARCHAR type string. If a string in a language other than Korean is used as the input string, the input string is returned as it is.
Note:
The initial consonant is correctly returned only when using the CHAR, VARCHAR type columns of a database whose character set is KSC5601 as an input string. Note that using NCHAR or NVARCHAR type column that uses unicode as the input string does not correctly return the initials.
Example#
<Query> Extract the constellation from Altibase.
SELECT CHOSUNG('알티베이스') chosung FROM DUAL;
CHOSUNG
-----------
ㅇㅌㅂㅇㅅ
1 row selected.
CHR#
Syntax#
CHR (n)
Description#
This function converts an input integer ASCII code value into the corresponding character. Multiple characters can be concatenated using double vertical bars.
Example#
<Query> Output the word "ALTIBASE" using ASCII code values
SELECT CHR(65) || CHR(76) || CHR(84) || CHR(73) || CHR(66) || CHR(65) || CHR(83) || CHR(69) mmdbms
FROM DUAL;
MMDBMS
------------------------------------
ALTIBASE
1 row selected.
<Query> Use the line feed character, which has an ASCII value of 10, to format SELECT query results to make them suitable for printing.
SELECT RTRIM(c_firstname) || ' ' || c_lastname || CHR(10) || sex || ' ' || cus_job || CHR(10) || address cus_info
FROM customers
WHERE cno = 10;
CUS_INFO
------------------------------------------------
Anh Dung Nguyen
M
8A Ton Duc Thang Street District 1 HCMC Vietnam
1 row selected.
Note:#
| Control Character | ASCII VALUE |
|---|---|
| Tab | 9 |
| New line | 10 |
| Carriage Return | 13 |
CONCAT#
Syntax#
CONCAT (expr1, expr2)
Description#
CONCAT returns expr1 concatenated with expr2. This function is the same as using the double-vertical-bars concatenation operator ("||").
Example#
<Query> Concatenate the results of a SELECT query into a English sentense.
SELECT CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(RTRIM(e_firstname), ' '), RTRIM(e_lastname)), ' is a ' ), emp_job ), '.') job
FROM employees
WHERE eno = 10;
JOB
-------------------------------------------------------------------
Elizabeth Bae is a programmer.
1 row selected.
DIGITS#
Syntax#
DIGITS (n)
Description#
This returns an integer as a character string.
The length of the character string depends on the data type (or size) of n. A string comprising 5 digits is returned for a SMALLINT, 10 digits for an INTEGER, and 19 digits for a BIGINT. If the number of digits in the input value is less than the number of digits that can be stored in the numeric data type, the leading spaces are populated with 0s (zeros).
Example#
<Query> Output a character string for each of three input numbers having different numeric data types. The length of the string is set differently for each data type.
CREATE TABLE t1 (i1 SMALLINT, i2 INTEGER, i3 BIGINT);
INSERT INTO t1 VALUES (357, 12, 5000);
SELECT DIGITS(i1)
, DIGITS(i2)
, DIGITS(i3)
FROM t1;
DIGITS(I1) DIGITS(I2) DIGITS(I3)
------------------------------------------------
00357 0000000012 0000000000000005000
1 row selected.
INITCAP#
Syntax#
INITCAP (expr)
Description#
INITCAP converts the first character in each word in the input character expression to uppercase and returns the result. Words are delimited by spaces or by characters that are not letters or numbers.
Example#
<Query> Output the character string "the soap" after converting the first letter in each word to uppercase.
SELECT INITCAP ('the soap') capital FROM DUAL;
CAPITAL
------------
The Soap
1 row selected.
INSTR, INSTRB, POSITION#
Syntax#
INSTR (expr, substring [, start [, occurrence]])
INSTRB (expr, substring [,start [, occurrence]])
POSITION (expr, substring [,start [, occurrence]])
Description#
The INSTR function looks for substring in expr and returns the location of the first character in substring if substring is found. It returns 0 (zero) if substring is not found. The INSTRB function returns the position of the specified string in bytes rather than in characters.
start specifies the position in expr at which the search begins. The default start value is 1, i.e. the first character. If a negative start value is specified, the search begins the specified number of characters from the end of expr. If a start value of 0 (zero) is specified, the function simply returns 0 (zero). If the value of start is greater than the length of expr, an error will occur.
occurrence indicates the instance of substring in expr for which to return the position. The default value is 1. If occurrence is set to 1, the position of the first instance of substring that is found will be returned; if it is set to 2, the position of the second instance of substring will be returned; etc. If it is set to 0 (zero) or to a value that is greater than the number of times that substring is found in expr, 0 (zero) will be returned. If it is set to a negative value, an error will occur. POSITION has the same function as INSTR. This function is case-sensitive..
Examples#
<Query> Return the position of the second occurrence of "OR" in the string "CORPORATE FLOOR", beginning the search at the third character.
SELECT INSTR ('CORPORATE FLOOR','OR', 3, 2) instring FROM DUAL;
INSTRING
--------------
14
1 row selected.
<Query> Return the position of the second occurrence of "베이" in the string "알티베이스5 데이터베이스", beginning the search at the third character. (Note that the character set is KO16KSC5601.)
SELECT INSTR ('알티베이스5 데이터베이스','베이', 3, 2) instring FROM DUAL;
INSTRING
--------------
11
1 row selected.
LOWER#
Syntax#
LOWER (expr)
Description#
LOWER returns the input string with all letters converted to lowercase.
Example#
<Query> Convert the input string into lowercase letters and output it.
SELECT LOWER('ONE PAGE PROPOSAL') lowercase FROM DUAL;
LOWERCASE
---------------------
one page proposal
1 row selected.
LPAD#
Syntax#
LPAD (expr1, n [,expr2])
Description#
LPAD pads the left of expr1 with the sequence of characters in expr2, repeatedly if necessary, until the resultant string is n characters long. If expr2 is not specified, then expr1 is padded with blank spaces. If expr1 is longer than n, this function simply returns the leftmost n characters of expr1.
Note that n is the number of characters, not bytes, and thus the number of bytes in the string may vary depending on the respective characters set that are in use on the server and the client (NLS_USE).
Example#
<Query> Pad the left of the character string "abc" with "xyz" and return a total of 10 characters.
SELECT LPAD('abc', 10, 'xyz') lpad_ex FROM DUAL;
LPAD_EX
------------------------------------------------
xyzxyzxabc
1 row selected.
LTRIM#
Syntax#
LTRIM (expr1 [,expr2])
Description#
LTRIM compares each of the characters in expr1 with each of the characters in expr2, starting from the leftmost character in expr1. If any of the characters in expr2 is the same as the current character in expr1, that character is deleted from expr1. This process occurs repeatedly until none of the characters in expr2 match the current character in expr1, at which point the current character in expr1 (i.e the first character without a match in expr2) is output along with all subsequent characters.
The default value for expr2 is a single blank. Therefore, if expr2 is omitted, blank spaces are trimmed from the left of expr1.
Example#
<Query> Trim all occurrences of the letters "a" and "b" from the beginning of the string "abaAabLEFT TRIM" and output the result.
SELECT LTRIM ('abaAabLEFT TRIM', 'ab') ltrim_ex FROM DUAL;
LTRIM_EX
-------------------
AabLEFT TRIM
1 row selected.
<Query> Retrieve the month that each employee joined the company by removing the leading day information and the hyphen between the day and month.
SELECT e_lastname
, LTRIM(LTRIM(join_date, '1234567890'), '-') join_month
FROM employees;
E_LASTNAME JOIN_MONTH
-----------------------------------------------------------
.
.
.
Ghorbani DEC-2009
Momoi SEP-2010
Fleischer JAN-2004
Wang NOV-2009
.
.
.
20 rows selected.
NCHR#
Syntax#
NCHR (n)
Description#
NCHR returns the character corresponding to the value of n in the national character set. The return value is of type NVARCHAR.
Example#
<Query> Output the 187th (U+00BB) character in the national character set.
SELECT NCHR(187) FROM DUAL;
NC
--
>>
1 row selected.
OCTET_LENGTH, LENGTHB#
Syntax#
OCTET_LENGTH (expr)
Description#
OCTET_LENGTH returns the size, in bytes, of the input character string.
The number of bytes in the input character string can vary depending on the database character set and the national character set of the database.
LENGTHB has the same function as OCTET_LENGTH
Examples#
<Query> Output the length, in bytes, assigned to the character string '우리나라'. (Note that the database character set in this case has been set to K016KSC5601.)
SELECT OCTET_LENGTH('우리나라') FROM DUAL;
OCTET_LENGTH('우리나라')
---------------------------
8
1 row selected.
<Query> Return the lengths, in bytes, of the addresses in the managers table.
SELECT OCTET_LENGTH(address)
FROM managers;
OCTET_LENGTH(ADDRESS)
------------------------
32
18
2 rows selected.
PKCS7PAD16#
Syntax#
PKCS7PAD16 (expr)
Description#
PKCS7PAD16 adjusts the total byte length of expr to a multiple of 16. This function is helpful when using encryption or decryption functions which only accept strings that are a multiple of 8 or 16 in length.
The padding rule for adjusting the input data length to a multiple of 16 is as follows:
-
For strings whose length is shorter than a multiple of 16 :
the end of the string is padded with characters so that the total string length is a multiple of 16. For example, four "0x04" characters are padded to a string which requires four more bytes. - For strings whose length is a multiple of 16:
the end of the string is padded with the "0x10(16)" character.
Example#
Please refer to the examples for AESDECRYPT.
PKCS7UNPAD16#
Syntax#
PKCS7UNPAD16 (expr)
Description#
PKCS7UNPAD16 removes the trailing characters from a byte string whose length was padded to a multiple of 16 with the PKCS7PAD16() function, and returns it as it was. However, if expr does not comply with the padding rule of the PKCS7PAD16 function, an error occurs.
This function is helpful when using encryption or decryption functions which only accept strings that are a multiple of 8 or 16 in length.
Example#
Please refer to the examples for AESDECRYPT.
RANDOM_STRING#
Syntax#
RANDOM_STRING (option, length)
Description#
This function generates a random string in the format specified in option and of as many characters as specified in length. The return type is VARCHAR.
length specifies the length of the random string to be created, and can take a value from 0 ~ 4000. option can take one of the following parameters:
-
'u', 'U'
generates a random string in upper case alphabetic characters
-
'l', 'L'
generates a random string in lower case alphabetic characters
-
'a', 'A'
generates a random string in case-insensitive alphabetic characters
-
'x', 'X'
generates a random string in upper case alphanumeric characters
-
'p', 'P'
generates a random string in any printable characters
Example#
<Query>
SELECT RANDOM_STRING( 'U', 10 ) FROM DUAL;
RANDOM_STRING( 'U', 10 )
---------------------------
BCJVFUMXPK
1 row selected.
SELECT RANDOM_STRING( 'l', 10 ) FROM DUAL;
RANDOM_STRING( 'l', 10 )
----------------------------
fgddcmpydo
1 row selected.
SELECT RANDOM_STRING( 'p', 10 ) FROM DUAL;
RANDOM_STRING( 'p', 10 )
----------------------------
fEn$bLq6jZ
1 row selected.
REGEXP_COUNT#
Syntax#
REGEXP_COUNT (expr, pattern_expr[, start])
Description#
REGEXP_COUNT returns the number of occurrences of pattern_expr in the expr string, from the start position.
pattern_expr is the regular expression patten to be searched for, and can contain up to 1024 bytes. If expr and pattern_expr are of different data types, pattern_expr is converted to the datatype of expr.
start is the position at which the search is to begin, and takes the value of a positive integer other than 0. If this value is omitted, the default value is 1, and the search begins at the first character of the input expr string.
pattern_expr can be a value that expresses the search patten as a regular expression. For further information on regular expressions, please refer to "Appendix A. Regular Expressions".
Example#
<Query> Search the expression 'Guro' from the beginning of the input string, and output the number of times the expression occurs.
SELECT REGEXP_COUNT('Daerungpost-Tower II Guro-3 Dong, Guro-gu Seoul', 'Guro', 1) "REGEXP_COUNT"
FROM DUAL;
REGEXP_COUNT
---------------
2
1 row selected.
<Query> Search the input string from the beginning for substrings without blank characters, and output the number of times it occurs.
SELECT REGEXP_COUNT('Daerungpost-Tower II Guro-3 Dong, Guro-gu Seoul', '[^ ]+', 1) "REGEXP_COUNT"
FROM DUAL;
REGEXP_COUNT
---------------
6
1 row selected.
REGEXP_INSTR#
Syntax#
REGEXP_INSTR (expr, pattern_expr [, start [, occurrence]])
Description#
REGEXP_INSTR is a function which extends the functionality of the INSTR function.
REGEXP_INSTR differs from INSTR in that it takes a regular expression pattern (instead of the search string) as the second argument, and that start only accepts a positive integer. pattern_expr can contain up to 1024 bytes. Other arguments are equivalent for both functions; please refer to the descriptions for the INSTR.
pattern_expr can be a value that expresses the search patten as a regular expression. For further information on regular expressions, please refer to "Appendix A. Regular Expressions".
Example#
<Query> The following example searches the string for occurrences of substrings without blank characters. The beginning position of the fifth occurrence of a substring without a blank character is output.
SELECT REGEXP_INSTR('Daerungpost-Tower II Guro-3 Dong, Guro-gu Seoul', '[^ ]+', 1, 5) "REGEXP_INSTR"
FROM DUAL;
REGEXP_INSTR
---------------
35
1 row selected.
REGEXP_REPLACE#
Syntax#
REGEXP_REPLACE (expr, pattern_expr [, replace_string [, start [,occurrence]]])
Description#
This finds a string in expr that matches pattern_expr and replaces or removes it with another character.
For pattern_expr, use this function to enter the regular expression pattern (up to 1024 bytes) the user wants to find. If pattern_expr is an empty string or NULL , expr is returned as is.
Entering replace_string returns the result of replacing replace_string with the matched string in expr. If replace_string is omitted or NULL, the string that matches the regular expression pattern in expr is returned.
start is the starting position of the search, and it enters a nonzero positive integer. If this value is omitted, the default value is 1, and the search starts from the first character of the expr string entered,
occurrence allows to set the number of expr occurrences of pattern_expr. The default value is 0, meaning that all occurrences of pattern_expr will be replaced.
pattern_expr can be a value that expresses the search pattern as a regular expression. For more information about regular expression, please refer to "Appendix A. Regular Expressions".
Example#
<Query> Replace the second of the strings that match 'Guro' with 'Mapo'.
SELECT REGEXP_REPLACE('Daerungpost-Tower II Guro-3 Dong, Guro-gu Seoul', 'Guro', 'Mapo', 1, 2) "REGEXP_REPLACE"
FROM DUAL;
REGEXP_REPLACE
----------------------------------------------------------------------
Daerungpost-Tower II Guro-3 Dong, Mapo-gu Seoul
1 row selected.
REGEXP_SUBSTR#
Syntax#
REGEXP_SUBSTR (expr, pattern_expr [, start [, occurrence]])
Description#
REGEXP_SUBSTR returns the matching string for pattern_expr from expr.
pattern_expr is the regular expression patten to be searched for, and can contain up to 1024 bytes.
start is the position at which the search is to begin, and takes the value of a positive integer other than 0. If this value is omitted, the default value is 1, and the search begins at the first character of the input expr string.
occurrence is the nth occurrence of pattern_expr in expr that is to be returned. The default value is 1 and this indicates the first occurrence of pattern_expr.
pattern_expr can be a value that expresses the search patten as a regular expression. For further information on regular expressions, please refer to "Appendix A. Regular Expressions".
Example#
<Query> Search the input string for a substring that matches the second occurrence of 'Guro', and output it.
SELECT REGEXP_SUBSTR('Daerungpost-Tower II Guro-3 Dong, Guro-gu Seoul', 'Guro', 1, 2) "REGEXP_SUBSTR"
FROM DUAL;
REGEXP_SUBSTR
---------------
Guro
1 row selected.
<Query> Search the input string for a substring with the third occurrence of non-blank characters, and output it.
SELECT REGEXP_SUBSTR('Daerungpost-Tower II Guro-3 Dong, Guro-gu Seoul', '[^ ]+', 1, 3) "REGEXP_SUBSTR"
FROM DUAL;
REGEXP_SUBSTR
---------------------------------------------------
Guro-3
1 row selected.
REPLACE2#
Syntax#
REPLACE2 (expr1 , expr2, [expr3])
Description#
REPLACE2 replaces every occurrence of expr2 in expr1 with expr3 and returns the result. If expr3 is omitted or NULL, then all occurrences of expr2 are removed. If expr2 is NULL, then expr1 is returned unchanged.
Unlike the TRANSLATE function, which replaces individual characters, the REPLACE2 function replaces an entire character string with another character string.
Example#
<Query> Query the departments table and replace all instances of the word "team" in the dname column with the word "division".
SELECT REPLACE2(dname, 'team', 'division')
FROM departments;
REPLACE2(DNAME, 'team', 'division')
------------------------------------------------
Engine Development Division
Marketing Division
Planning and Management Division
Sales Division
5 rows selected.
<Query> In the character string "abcdefghicde", replace all instances of "cde" with "xx".
SELECT REPLACE2('abcdefghicde', 'cde', 'xx') FROM DUAL;
REPLACE2('abcdefghicde', 'cde', 'xx')
-----------------------------------------
abxxfghixx
1 row selected.
REPLICATE#
Syntax#
REPLICATE (expr, n)
Description#
REPLICATE returns a character string in which expr is repeated n times. expr must be a string and n must be a positive number. If 0 (zero) or a negative number is entered, the REPLICATE function will return NULL.
Example#
<Query> Output a string comprising three repetitions of "KSKIM".
SELECT REPLICATE ('KSKIM', 3) FROM DUAL;
REPLICATE ('KSKIM', 3)
-----------------------------------
KSKIMKSKIMKSKIM
1 row selected.
RPAD#
Syntax#
RPAD (expr1, n [,expr2])
Description#
RPAD pads the right end of expr1 with the sequence of characters in expr2, repeatedly if necessary, until the resultant string is n characters long. If expr2 is not specified, then expr1 is padded with blank spaces. If expr1 is longer than n, this function simply returns the leftmost n characters of expr1.
Note that n is the number of characters, not bytes, and thus the number of bytes in the string may vary depending on the respective character sets that are in use on the server and the client (NLS_USE).
Example#
<Query> In the following example, the right side of the character string "123" is padded with "0" (zeros) and then type-converted to return a 10-digit number.
SELECT TO_NUMBER(RPAD('123', 10, '0')) rpad_ex FROM DUAL;
RPAD_EX
--------------
1230000000
1 row selected.
RTRIM#
Syntax#
RTRIM (expr1 [, expr2])
Description#
RTRIM compares each of the characters in expr1 with each of the characters in expr2, starting from the rightmost character in expr1 and working toward the left. If any of the characters in expr2 is the same as the current character in expr1, that character is deleted from expr1. This process occurs repeatedly until none of the characters in expr2 match the current character in expr1, at which point the current character in expr1 (i.e the rightmost character without a match in expr2) is output along with all preceding characters.
The default value for expr2 is a single blank. Therefore, if expr2 is omitted, blank spaces are trimmed from the right of expr1.
Example#
<Query> Remove all lowercase "a" and "b" characters from the end of the character string "RightTrimbaAbab" and output the result.
SELECT RTRIM ('RIGHTTRIMbaAbab', 'ab') rtrim_ex FROM DUAL;
RTRIM_EX
-------------------
RIGHTTRIMbaA
1 row selected.
<Query> Retrieve the day and month that each employee joined the company by removing the trailing year information and the hyphen between the month and year.
SELECT e_lastname
, RTRIM(RTRIM(join_date, '1234567890'), '-') join_date
FROM employees;
E_LASTNAME JOIN_DATE
---------------------------------------------------------------------------
.
.
.
Ghorbani 20-DEC
Momoi 09-SEP
Fleischer 24-JAN
Wang 29-NOV
.
.
.
20 rows selected.
SIZEOF#
Syntax#
SIZEOF (expr)
Description#
SIZEOF returns the size of a character string or the size allocated thereto. The input character string can be CHAR, VARCHAR or any numeric data type. If the input value is of a numeric data type, it is converted to VARCHAR and the size allocated thereto is returned.
Unlike OCTET_LENGTH, which returns the actual size of the input character string, SIZEOF returns the length of the space allocated to the input character string, or the length of the column, which was specified when the table was created.
Note therefore that SIZEOF returns 20 for the INTEGER, BIGINT, and SMALLINT data types, 47 for the DECIMAL, FLOAT, NUMBER, and NUMERIC types, and 22 for the DOUBLE and REAL data types.
Example#
<Query> Retrieve the length of the column dummy in the table dual.
SELECT SIZEOF(dummy) FROM DUAL;
SIZEOF(DUMMY)
--------------
1
1 row selected.
SUBSTR, SUBSTRB, SUBSTRING#
Syntax#
SUBSTR (expr, start [, length])
Description#
SUBSTR returns a portion of expr that is length characters long, beginning at character start.
If start is positive, which is typical, Altibase counts from the beginning of the input string to find the first character. start can also be set to a negative value, in which case Altibase counts backwards from the end of string to find the first character. If start is set to 0, then it is handled as though it were set to 1.
If length is omitted, then Altibase returns all characters to the end of the string.
The input character string can be CHAR, VARCHAR, or any numeric data type. If the input value is of a numeric data type, it is converted to VARCHAR. The type of the return value is VARCHAR.
Unlike SUBSTR, which determines the position and length in units of the characters of the input character set, SUBSTRB determines the position and length in units of bytes rather than characters.
SUBSTRING has the same function as SUBSTR.
Example#
<Query> Return a substring of the character string "SALESMAN", 5 characters long and starting from the first character.
SELECT SUBSTR('SALESMAN', 1 ,5) substring FROM DUAL;
SUBSTRING
-------------
SALES
1 row selected.
<Query> Return a substring of the input string "ABCDEFG".
SELECT SUBSTR('ABCDEFG', -5 ,4) substring FROM DUAL;
SUBSTRING
-------------
CDEF
1 row selected.
<Query> Return a character string 2 bytes in length, starting from the 5th byte in the character string "ABCDEFG"
SELECT SUBSTRB('ABCDEFG', 5, 2) substring_with_bytes FROM DUAL;
SUBSTRING_WITH_BYTES
------------------------
EF
1 row selected.
TRANSLATE#
Syntax#
TRANSLATE (expr1 , expr2, expr3)
Description#
TRANSLATE checks each character in expr1 to determine whether it is found in expr. If the character is not found in expr, it is left as it is. If, however, the character is found in expr2, it is left as it is. If, however, the character is found in expr2, it is replaced by the character at the corresponding p
It is possible to specify expr2 so that it has more characters than expr3. In this case, some characters at the end of expr2 will have no corresponding characters in expr3. If any of these characters are found in expr1, they are simply removed from the character string to be returned. Setting expr3 to an empty string causes this function to remove all characters in expr2 from expr1.
If expr3 is longer than expr2, the remaining characters in expr3 are ignored. If the same character is specified multiple times in expr2, the first match in expr3 is used.
Example#
<Query> For all items for which the quantity in inventory is more than 50000, replace the letter "M" in the product name with "L".
SELECT TRANSLATE(gname, 'M', 'L')
FROM goods
WHERE stock > 50000;
TRANSLATE(GNAME, 'M', 'L')
--------------------------------------------
TL-U200
L-190G
2 rows selected.
<Query> Convert all alphabetic characters in a string to lowercase.
SELECT TRANSLATE('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'abcdefghijlkmnopqrstuvwxyz')
FROM DUAL;
TRANSLATE('0123456789ABCDEFGHIJKLMNOPQRS
------------------------------------------------
0123456789abcdefghijlkmnopqrstuvwxyz0123456789
1 row selected.
<Query> Remove all alphabetic characters from a license number and return only the numbers.
SELECT TRANSLATE('3PQR334', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') License
FROM DUAL;
LICENSE
------------------
3334
1 row selected.
TRIM#
Syntax#
TRIM (expr1 [, expr2])
Description#
TRIM returns a substring of expr1 that starts with the first character that is not found in expr2 and ends with the last character that is not found in expr2. The portion of expr1 between these two characters is returned without change.
In other words, TRIM removes all characters found in expr2 from the beginning and end of expr1, and returns the result.
TRIM is, therefore, functionally the same as executing both LTRIM and RTRIM with the same value of expr2.
The default value for expr2 is a single space. This means that if expr2 is not specified, all blank spaces are trimmed from both ends of expr1.
Example#
<Query> Remove all instances of lowercase "a" and "b" from both ends of the character string "abbAaBbAbba" and output the result.
SELECT TRIM ('abbAaBbAbba', 'ab') trim_ex FROM DUAL;
TRIM_EX
---------------
AaBbA
1 row selected.
UPPER#
Syntax#
UPPER (char)
Description#
UPPER returns the input string with all letters converted to uppercase.
Example#
<Query> Convert the input string into uppercase letters and output it.
SELECT UPPER('Capital') uppercase FROM DUAL;
UPPERCASE
-------------
CAPITAL
1 row selected.
REVERSE_STR#
Syntax#
REVERSE_STR (expr)
Description#
REVERSE_STR reverses the order of the characters in expr and returns the result.
Examples#
<Query> Output the string "KSKIM" in reverse.
SELECT REVERSE_STR ('KSKIM') FROM DUAL;
REVERSE_STR ('KSKIM')
-------------------------
MIKSK
1 row selected.
<Query> Reverse the characters in the string '알티베이스7' and output the result. (Note that the database character set has been set to KO16KSC5601)
SELECT REVERSE_STR ('알티베이스7') FROM DUAL;
REVERSE_STR ('알티베이스7')
-------------------------
7스이베티알
1 row selected.
STUFF#
Syntax#
STUFF (expr1, start, length, expr2)
Description#
STUFF removes a substring, beginning at the position specified by start and having the length specified by length, from expr1, and inserts expr2 in its place.
If length is specified as 0 (zero), expr2 is simply inserted without first deleting anything from expr1. In this case, expr2 is inserted to the left of the position specified using start. If length is greater than the number of characters to the right of the start position in expr1, all characters in expr1 to the right of start are removed, and expr2 is inserted.
To simply concatenate expr2 to the end of expr1, set start to (1 + the length of expr1) and length to 0 (zero) or a positive number.
If the value of either start or length is negative, or if the value of start is greater than (1 + the length of expr1), an error will occur.
Example#
<Query> Use the STUFF function to convert "KDHONG" to "KILDONG HONG".
SELECT STUFF ('KDHONG', 2, 1, 'ILDONG ') FROM DUAL;
STUFF ('KDHONG', 2, 1, 'ILDONG ')
--------------------------------------------------
KILDONG HONG
1 row selected.
<Query> Insert expr2 before expr1.
SELECT STUFF ('KDHONG', 1, 0, 'ILDONG ') FROM DUAL;
STUFF ('KDHONG', 1, 0, 'ILDONG ')
--------------------------------------------------
ILDONG KDHONG
1 row selected.
<Query> Insert expr2 after expr1.
SELECT STUFF ('KDHONG', 7, 0, 'ILDONG ') FROM DUAL;
STUFF ('KDHONG', 7, 0, 'ILDONG ')
--------------------------------------------------
KDHONGILDONG
1 row selected.
<Query> Set length to 0 to insert expr2 to the left of the start position without deleting anything.
SELECT STUFF ('KDHONG', 2, 0, 'ILDONG ') FROM DUAL;
STUFF ('KDHONG', 2, 0, 'ILDONG ')
--------------------------------------------------
KILDONG DHONG
1 row selected.
<Query> Use the STUFF function to change the contents of the input string when KO16KSC5601 is set as the database character set.
SELECT STUFF ('알티베이스0', 5, 1, '데이터베이스') FROM DUAL;
STUFF ('알티베이스0', 5, 1, '데이터베이스')
------------------------------------------------
알티베이데이터베이스0
1 row selected.