String functions in SQL

The alphanumeric character handling functions accept arguments of type character and return characters or numeric values. The following are some of the functions that Oracle offers to work with character strings:

- chr (x): returns a character equivalent to the code sent as argument "x". Example:

 select chr (65) from dual; - returns 'A'.

 select chr (100) from dual; - returns 'd'.

Annotations

The DUAL table is a special single column table present by default in all Oracle database installations. It is used when we want to make a statement that does not need to consult tables. The table has a single column VARCHAR2 (1) called DUMMY that has a value of 'X'

- concat (string1, string2): concatenate two character strings; is equivalent to the || operator. Example:

 select concat ('Good afternoon', 'afternoon') from dual; - returns 'Good afternoon'.

- initcap (string): returns the string sent as an argument with the first letter (capital letter) of each word in uppercase. Example:

 select initcap ('good afternoon student') from dual; - returns 'Good afternoon student'.

- lower (string): returns the string sent as an argument in lowercase. "lower" means reduce in English. 

Example:

 select lower ('Good afternoon STUDENT') from dual; - returns "good afternoon student".

- upper (string): returns the string with all the characters in uppercase. Example:

 select upper ('www.oracle.com') from dual; - 'WWW.ORACLE.COM'

- lpad (string, length, filledstring): returns the number of characters specified by the "length" argument, of the string sent as the first argument (starting from the first character); if "length" is greater than the size of the string sent, fill the remaining spaces with the string sent as the third argument (in case of omitting the third argument fill with spaces); the filling starts from the left. Examples:

 select lpad ('student', 10, 'xyz') from dual; - returns 'xyzx student'

 select lpad ('student', 4, 'xyz') from dual; - returns 'alum'

- rpad (string, length, filledstring): returns the number of characters specified by the "length" argument, of the string sent as the first argument (starting from the first character); if "length" is greater than the size of the string sent, fill the remaining spaces with the string sent as the third argument (in case of omitting the third argument fill with spaces); the padding starts from the right (last character). Examples:

 select rpad ('student', 10, 'xyz') from dual; - returns 'studentxyzx'

 select rpad ('student', 4, 'xyz') from dual; - returns 'alum'

- ltrim (string1, string2): delete all occurrences of "string2" in "string1", if they are found at the beginning; if the second argument is omitted, the spaces are removed. Example:

 select ltrim ('the house on the block', 'la') from dual; - 'house on the block'

 select ltrim ('is the house on the block', 'la') from dual; - does not remove any characters

 select ltrim ('the house') from dual; - 'the house'

- rtrim (string1, string2): deletes all occurrences of "string2" in "string1", if they are found to the right (at the end of the string); if the second argument is omitted, the spaces are removed. Example:

 select rtrim ('la casa lila', 'la') from dual; - 'la casa li'

 select rtrim ('la casa lila', 'la') from dual; - does not delete any characters

 select rtrim ('the purple house') from dual; - 'the lilac house'

- trim (string): returns the string with the left and right spaces removed. "Trim" means to trim. Example:

select trim ('oracle') from dual; - 'oracle'

- replace (string, subcade1, subcade2): returns the string with all the occurrences of the replacement substring (subcade2) by the substring to replace (subcae1). Example:

 select replace ('xxx.oracle.com', 'x', 'w') from dual;

returns "www.oracle.com '.

- substr (string, start, length): returns a part of the string specified as the first argument, starting from the position specified by the second argument and as many characters long as the third argument indicates. Example:

select substr ('www.oracle.com', 1,10) from dual; - 'www.oracle'

select substr ('www.oracle.com', 5,6) from dual; - 'oracle'

- length (string): returns the length of the string sent as an argument. "lenght" means length in English. 

Examples:

 select length ('www.oracle.com') from dual; - returns 14.

- instr (string, substring): returns the starting position (of the first occurrence) of the substring specified in the string sent as the first argument. If it does not find it, it returns 0. Examples:

 select instr ('Jorge Luis Borges', 'or') from dual; - 2

 select instr ('Jorge Luis Borges', 'ar') from dual; - 0, not found

- translate (): replaces each occurrence of a character string with another character string. The difference with "replace" is that it works with character strings and replaces one complete string with another, whereas "translate" works with simple characters and replaces several. The following example specifies that all "O" characters are replaced by the "0" character, all "S" characters by the "5" character, and all "G" characters by "6":

 select translate ('JORGE LUIS BORGES', 'OSG', '056') from dual; - 'J0R6E LUI5 B0R6E5'

These functions can be used by sending the name of a character field as an argument.