| Function Name | Description | Example Usage | Result | |
| CONCAT | Concatenates two strings. | SELECT CONCAT(‘Oracle’, ‘PL/SQL’) FROM dual; | OraclePL/SQL | |
| ` | | ` (Concatenation) | Concatenates two strings. | |
| LENGTH | Returns the length of a string. | SELECT LENGTH(‘Oracle’); | 6 | |
| LOWER | Converts all characters in a string to lowercase. | SELECT LOWER(‘ORACLE’); | oracle | |
| UPPER | Converts all characters in a string to uppercase. | SELECT UPPER(‘oracle’); | ORACLE | |
| INITCAP | Capitalizes the first letter of each word in a string. | SELECT INITCAP(‘oracle pl/sql’); | Oracle Pl/Sql | |
| SUBSTR | Returns a substring from the specified position. | SELECT SUBSTR(‘OraclePLSQL’, 7, 5) FROM dual; | PLSQL | |
| INSTR | Returns the position of the first occurrence of a substring. | SELECT INSTR(‘OraclePLSQL’, ‘PL’) FROM dual; | 7 | |
| REPLACE | Replaces all occurrences of a substring within a string. | SELECT REPLACE(‘OraclePLSQL’, ‘PL’, ‘SQL’) FROM dual; | OracleSQLSQL | |
| LPAD | Pads the left side of a string with a specified set of characters. | SELECT LPAD(‘Oracle’, 10, ‘*’) FROM dual; | ****Oracle | |
| RPAD | Pads the right side of a string with a specified set of characters. | SELECT RPAD(‘Oracle’, 10, ‘*’) FROM dual; | Oracle**** | |
| TRIM | Removes leading and trailing spaces (or specified characters) from a string. | SELECT TRIM(‘ Oracle ‘) FROM dual; | Oracle | |
| LTRIM | Removes leading spaces (or specified characters) from a string. | SELECT LTRIM(‘ Oracle’) FROM dual; | Oracle | |
| RTRIM | Removes trailing spaces (or specified characters) from a string. | SELECT RTRIM(‘Oracle ‘) FROM dual; | Oracle | |
| SOUNDEX | Returns a phonetic representation of a string. | SELECT SOUNDEX(‘Oracle’) FROM dual; | O624 | |
| ASCII | Returns the ASCII value of the first character in a string. | SELECT ASCII(‘A’) FROM dual; | 65 | |
| CHR | Returns the character corresponding to the ASCII value. | SELECT CHR(65) FROM dual; | A | |
| TRANSLATE | Replaces characters in a string based on corresponding characters in two other strings. | SELECT TRANSLATE(‘SQL’, ‘SQ’, ‘PL’) FROM dual; | PQL | |
| REGEXP_LIKE | Determines if a string matches a regular expression pattern. | SELECT REGEXP_LIKE(‘Oracle’, ‘Ora’) FROM dual; | 1 (true) | |
| REGEXP_INSTR | Returns the position of the first occurrence of a substring matching a regular expression. | SELECT REGEXP_INSTR(‘OraclePLSQL’, ‘P.L’) FROM dual; | 7 | |
| REGEXP_SUBSTR | Returns the substring matching a regular expression. | SELECT REGEXP_SUBSTR(‘OraclePLSQL’, ‘P.L’) FROM dual; | PL | |
| REGEXP_REPLACE | Replaces substrings matching a regular expression with another substring. | SELECT REGEXP_REPLACE(‘OraclePLSQL’, ‘P.L’, ‘SQL’) FROM dual; | OracleSQLSQL | |
| INSTR | Returns the position of the first occurrence of a substring. | SELECT INSTR(‘OraclePLSQL’, ‘PL’) FROM dual; | 7 | |
| SUBSTR | Returns a substring from the specified position. | SELECT SUBSTR(‘OraclePLSQL’, 7, 5) FROM dual; | PLSQL | |
| TO_CHAR | Converts a number or date to a string. | SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD’) FROM dual; | 2024-06-04 | |
| TO_NUMBER | Converts a string to a number. | SELECT TO_NUMBER(‘12345’) FROM dual; | 12345 | |
| TO_DATE | Converts a string to a date. | SELECT TO_DATE(‘2024-06-04’, ‘YYYY-MM-DD’) FROM dual; | 04-Jun-24 | |
| REPLACE | Replaces all occurrences of a substring within a string. | SELECT REPLACE(‘OraclePLSQL’, ‘PL’, ‘SQL’) FROM dual; | OracleSQLSQL | |
| RPAD | Pads the right side of a string with a specified set of characters. | SELECT RPAD(‘Oracle’, 10, ‘*’) FROM dual; | Oracle**** | |
| LPAD | Pads the left side of a string with a specified set of characters. | SELECT LPAD(‘Oracle’, 10, ‘*’) FROM dual; | ****Oracle | |
| | | | |
| These functions provide various capabilities to manipulate and format string and character data in Oracle PL/SQL, allowing for complex transformations and queries involving text data. | | | | |
| | | | |
Leave a Reply