This article contains information about oracle string functions such as length, substr, concat, trim in Oracle database management system.<\/p>\n
Oracle has several functions for manipulating character or text data.<\/p>\n
ASCII Function returns the numerical equivalent of the character value in the ASCII table.<\/p>\n
SELECT ASCII('Y') AS Y FROM DUAL;<\/pre>\nNOTE:<\/strong> Dual table is a virtual table used in various operations.<\/p>\nCHR Function in Oracle(pl sql ascii to char)<\/h3>\n
CHR Function returns the character equivalent of the numerical value in the ASCII table.<\/p>\n
SELECT CHR(89) AS \"Y\" FROM DUAL;<\/pre>\nCONCAT Function in Oracle<\/h3>\n
The CONCAT function is used for string concatenation.<\/p>\n
SELECT CONCAT('Yusuf', ' Sefa') AS AD FROM DUAL;<\/pre>\nThe function can be nested for more than one value.<\/p>\n
SELECT CONCAT(CONCAT('Yusuf', ' Sefa'), ' SEZER') AS FullName FROM DUAL;<\/pre>\n||<\/code> operator can also be used for string concatenation operation.<\/p>\nSELECT 'Yusuf' || ' Sefa' AS Name FROM DUAL;<\/pre>\nSELECT 'Yusuf' || ' Sefa' || ' SEZER' AS FullName FROM DUAL;<\/pre>\nINITCAP Function in Oracle<\/h3>\n
This function set the first letter of each value in a String expression as uppercase and the others as lowercase letters.<\/p>\n
SELECT INITCAP('YUSUF SEFA') AS Name FROM DUAL;<\/pre>\nLENGHT Function in Oracle<\/h3>\n
This function returns the length of a string.<\/p>\n
SELECT LENGTH('YUSUF SEFA SEZER') AS CHARACTER_LENGHT FROM DUAL;<\/pre>\nThere is also LENGHT2, LENGHT4, LENGHTB and LENGHTC functions in oracle. sytanx and logic is the same.<\/p>\n
LENGHT2<\/strong> use UCS2 code points,<\/p>\nLENGHT4<\/strong> use UCS4 code points,<\/p>\nLENGHTB <\/strong>use bytes instead of characters,<\/p>\nLENGHTC<\/strong> use Unicode complete characters.<\/p>\nINSTR Function in Oracle<\/h3>\n
Returns the location of the searched value in a string expression.<\/p>\n
SELECT INSTR('YUSUF SEFA SEZER', 'U') AS Location_of_U FROM DUAL;<\/pre>\nThe parameters of the function are as follows.<\/p>\n
INSTR(string_to_search,substring,position, occurence)<\/p>\n
string -> string to search<\/p>\n
substring -> text to search in string<\/p>\n
position -> this parameter is optinal. indicates where the search begins.<\/p>\n
occurence -> this parameter is optional. The default value is 1. It means that you will search for first substring. If you want to search second substring in the string you must write 2 for this parameter.<\/p>\n
There is also INSTR2, INSTR4, INSTRB and INSTRC functions in oracle. sytanx and logic is the same.<\/p>\n
INSTR2<\/strong> use UCS2 code points,<\/p>\nINSTR4<\/strong> use UCS4 code points,<\/p>\nINSTRB <\/strong>use bytes instead of characters,<\/p>\nINSTRC<\/strong> use Unicode complete characters.<\/p>\nExamples:<\/strong><\/p>\nSELECT INSTR('dbtut', 't') FROM DUAL;\nResult: 3 (the first occurrence of 't')\n\nSELECT INSTR('dbtut', 't', 1, 1) FROM DUAL;\nResult: 2 (the first occurrence of 't')\n\nSELECT INSTR('dbtut', 't', 1, 2) FROM DUAL;\nResult: 5 (the second occurrence of 't')<\/pre>\nSUBSTR Function in Oracle<\/h3>\n
This function is used to extract a specific part from a String expression.<\/p>\n
The parameters of the function are as follows.<\/p>\n
SUBSTR(string, start_position, lenght)<\/p>\n
string -> string to search<\/p>\n
start_position -> start position for extracting<\/p>\n
position -> this parameter is optinal. indicates extract lenght.<\/p>\n
Examples:<\/strong><\/p>\nSELECT SUBSTR('dbtut.com', 2, 2) FROM DUAL;\nResult: 'bt'\n\nSELECT SUBSTR('dbtut.com', 2) FROM DUAL;\nResult: 'btut.com'<\/pre>\nTRIM Function in Oracle<\/h3>\n
This function removes spaces at the beginning and end of the string expression by default.<\/p>\n
SELECT TRIM(' YUSUF SEFA SEZER ') AS TRIM, LENGTH(TRIM(' YUSUF SEFA SEZER ')) AS Name_Lenght FROM DUAL;<\/pre>\nThe function has several uses. You can find examples below.<\/p>\n
Examples:<\/strong><\/p>\nSELECT TRIM(' dbtut ') FROM DUAL;\nResult: 'dbtut'\n\nSELECT TRIM(' ' FROM ' dbtut ') FROM DUAL;\nResult: 'dbtut'\n\nSELECT TRIM(LEADING '0' FROM '000dbtut0') FROM DUAL;\nResult: 'dbtut0'\n\nSELECT TRIM(TRAILING '1' FROM '1dbtut1') FROM DUAL;\nResult: '1dbtut'\n\nSELECT TRIM(BOTH '1' FROM '11dbtut11') FROM DUAL;\nResult: 'dbtut'<\/pre>\nRTRIM Function in Oracle<\/h3>\n
This function removes spaces at the end of the string expression by default. But if you set a second parameter, it will remove the character(s) you set right end of the string.<\/p>\n
Examples:<\/strong><\/p>\nSELECT RTRIM('dbtut ') FROM DUAL;\nResult: 'dbtut'\n\nSELECT RTRIM('dbtut ', ' ') FROM DUAL;\nResult: 'dbtut'\n\nSELECT RTRIM('dbtut.com', '.com') FROM DUAL;\nResult: 'dbtut'\n\nSELECT RTRIM('dbtut.com13254', '12345') FROM DUAL;\nResult: 'dbtut.com'<\/pre>\nLTRIM Function in Oracle<\/h3>\n
This function removes spaces from left side of the string expression by default. But if you set a second parameter, it will remove the character(s) you set from left side of the string.<\/p>\n
Examples:<\/strong><\/p>\nSELECT LTRIM(' dbtut') FROM DUAL;\nResult: 'dbtut'\n\nSELECT LTRIM(' dbtut', ' ') FROM DUAL;\nResult: 'dbtut'\n\nSELECT LTRIM('https:\/\/dbtut.com', 'https:\/\/') FROM DUAL;\nResult: 'dbtut.com'\n\nSELECT LTRIM('13254dbtut.com', '12345') FROM DUAL;\nResult: 'dbtut.com'<\/pre>\nRPAD Function in Oracle<\/h3>\n
This function completes the end of the string expression according to the value specified by the parameter.<\/p>\n
The parameters of the function are as follows.<\/p>\n
RPAD(string, lenght, character_to_complete)<\/p>\n
string -> string to search<\/p>\n
lenght -> total lenght of string after function executed<\/p>\n
character_to_complete -> this parameter is optinal. If you set this parameter, it adds this character to the string as many as the number specified in the second parameter.<\/p>\n
Examples:<\/strong><\/p>\nSELECT RPAD('dbtut', 8) FROM DUAL;\nResult: 'dbtut '\n\nSELECT RPAD('dbtut', 4) FROM DUAL;\nResult: 'dbtu'\n\nSELECT RPAD('dbtut', 6, '0') FROM DUAL;\nResult: 'dbtut0'\n\nSELECT RPAD('dbtut', 5, 'x') FROM DUAL;\nResult: 'dbtut'\n\nSELECT RPAD('dbtut', 8, 'x') FROM DUAL;\nResult: 'dbtutxxx'<\/pre>\nLPAD Function in Oracle<\/h3>\n
This function completes the start of the string expression according to the value specified by the parameter.<\/p>\n
The parameters of the function are as follows.<\/p>\n
LPAD(string, lenght, character_to_complete)<\/p>\n
string -> string to search<\/p>\n
lenght -> total lenght of string after function executed<\/p>\n
character_to_complete -> this parameter is optinal. If you set this parameter, it adds this character to the start of the string as many as the number specified in the second parameter.<\/p>\n
Examples:<\/strong><\/p>\nSELECT LPAD('dbtut', 8) FROM DUAL;\nResult: ' dbtut'\n\nSELECT LPAD('dbtut', 4) FROM DUAL;\nResult: 'dbtu'\n\nSELECT LPAD('dbtut', 6, '0') FROM DUAL;\nResult: '0dbtut'\n\nSELECT LPAD('dbtut', 5, 'x') FROM DUAL;\nResult: 'dbtut'\n\nSELECT LPAD('dbtut', 8, 'x') FROM DUAL;\nResult: 'xxxdbtut'<\/pre>\nCOMPOSE function in Oracle<\/h3>\n
This function returns unicode value of any data type. The data type must be able to converted to a string. Because function takes its parameter as string.<\/p>\n
The parameters of the function are as follows.<\/p>\n
COMPOSE(string)<\/p>\n
string -> string to converted unicode<\/p>\n
SELECT COMPOSE('o' || unistr('\\0308') ) FROM DUAL;\nResult: \u00f6\n\nSELECT COMPOSE('a' || unistr('\\0302') ) FROM DUAL;\nResult: \u00e2<\/pre>\nDecompose Function in Oracle<\/h3>\n
Decompose function decompose a string and returns unicode string.<\/p>\n
Sample Usage:<\/p>\n
SELECT DECOMPOSE ('\u00e2') FROM DUAL; \n\nDECOMPOSE\n---------\na^<\/pre>\nConvert Function in Oracle<\/h3>\n
This function convert one character set to another character set. You can query V$NLS_VALID_VALUES to get character sets.<\/p>\n
SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'CHARACTERSET'<\/pre>\nSample Usage:<\/p>\n
SELECT CONVERT('\u00c4', 'US7ASCII', 'WE8ISO8859P1') FROM DUAL;<\/pre>\nReplace Function in Oracle<\/h3>\n
This function replaces characters in a string with another characters.<\/p>\n
The parameters of the function are as follows.<\/p>\n
REPLACE(string, string_to_be_replaced,replace_string)<\/p>\n
string -> main string<\/p>\n
string_to_be_replaced -> string to be replaced<\/p>\n
replace_string -> this parameter is optinal. If you set this parameter, it replaces second parameter with the string specified in this parameter. If you not set this parameter, this function removes the character specified in the second parameter from the string.<\/p>\n
Examples:<\/strong><\/p>\nSELECT REPLACE('xxxdbtut', 'xxx') FROM DUAL;\nResult: 'dbtut'\n\nSELECT REPLACE('xxxdbtutxxx', 'xxx') FROM DUAL;\nResult:'dbtut'\n\nSELECT REPLACE('dbtut.xxx', 'xxx', 'com') FROM DUAL;\nResult: 'dbtut.com'<\/pre>\nUPPER Function in Oracle<\/h3>\n
This functions converts all lowercases to uppercases in a string.<\/p>\n
Sample Usage:<\/p>\n
SELECT UPPER('dbtut111') FROM DUAL;\nResult: 'DBTUT111'<\/pre>\nLOWER Function in Oracle<\/h3>\n
This functions converts all uppercases to lowercases in a string.<\/p>\n
Sample Usage:<\/p>\n
SELECT LOWER('DBTUT111') FROM DUAL;\nResult: 'dbtut111'<\/pre>\nVSIZE Function in Oracle<\/h3>\n
This function returns number of bytes of expression.<\/p>\n
Sample Usages:<\/p>\n
SELECT VSIZE('dbtut') FROM DUAL;\nResult: 5\n\nSELECT VSIZE(null) FROM DUAL;\nResult: <null>\n\nSELECT surname, VSIZE (surname) \"BYTES\" \n FROM staffs\n WHERE staff_id = 1;\n\nResult: \nLAST_NAME BYTES\n--------------- ----------\nCAKIR 5<\/pre>\nDifference between length and vsize function in oracle<\/h4>\n
Lenght function returns lenght of a string, but VSIZE function returns number of bytes. So, a character may be 2 byte.<\/p>\n
<\/div>\n