Below are the string functions in Oracle with detailed explanations and examples:
1. CONCAT: Concatenates two strings.
SELECT CONCAT('Hello', ' World') AS concatenated_string FROM DUAL; -- Returns 'Hello World'
2. INSTR: Returns the position of a substring within a string.
SELECT INSTR('Hello World', 'World') AS position FROM DUAL; -- Returns 7
SELECT INSTR('Hello World', 'o', 1, 2) AS position FROM DUAL; -- Returns 8
3. SUBSTR: Extracts a substring from a string.
SELECT SUBSTR('Hello World', 1, 5) AS substring FROM DUAL; -- Returns 'Hello'
SELECT SUBSTR('Hello World', 7, 5) AS substring FROM DUAL; -- Returns 'World'
SELECT SUBSTR('Hello World', -5, 5) AS substring FROM DUAL; -- Returns 'World'
4. LENGTH: Returns the length of a string.
SELECT LENGTH('Hello World') AS length FROM DUAL; -- Returns 11
5. UPPER: Converts a string to uppercase.
SELECT UPPER('hello') AS upper_case FROM DUAL; -- Returns 'HELLO'
6. LOWER: Converts a string to lowercase.
SELECT LOWER('HELLO') AS lower_case FROM DUAL; -- Returns 'hello'
7. LPAD: Pads the left side of a string with a specified set of characters.
SELECT LPAD('Hello', 10, '*') AS left_padded FROM DUAL; -- Returns '*****Hello'
SELECT LPAD('Hello', 10) AS left_padded FROM DUAL; -- Returns ' Hello'
8. RPAD: Pads the right side of a string with a specified set of characters.
SELECT RPAD('Hello', 10, '*') AS right_padded FROM DUAL; -- Returns 'Hello*****'
SELECT RPAD('Hello', 10) AS right_padded FROM DUAL; -- Returns 'Hello '
9. LTRIM: Removes specified characters from the left side of a string.
SELECT LTRIM('***Hello', '*') AS left_trimmed FROM DUAL; -- Returns 'Hello'
SELECT LTRIM(' Hello') AS left_trimmed FROM DUAL; -- Returns 'Hello'
10. RTRIM: Removes specified characters from the right side of a string.
SELECT RTRIM('Hello***', '*') AS right_trimmed FROM DUAL; -- Returns 'Hello'
SELECT RTRIM('Hello ') AS right_trimmed FROM DUAL; -- Returns 'Hello'
11. TRIM: Removes leading and trailing characters from a string.
SELECT TRIM('*' FROM '***Hello***') AS trimmed FROM DUAL; -- Returns 'Hello'
SELECT TRIM(' ' FROM ' Hello ') AS trimmed FROM DUAL; -- Returns 'Hello'
12. REPLACE: Replaces occurrences of a substring with another substring.
SELECT REPLACE('Hello World', 'World', 'Oracle') AS replaced_string FROM DUAL; -- Returns 'Hello Oracle'
SELECT REPLACE('Hello World', 'l', 'L') AS replaced_string FROM DUAL; -- Returns 'HeLLo WorLd'
13. REGEXP_REPLACE: Replaces substrings matching a regular expression pattern with another substring.
SELECT REGEXP_REPLACE('Hello 123 World', '[0-9]', '') AS replaced_string FROM DUAL; -- Returns 'Hello World'
SELECT REGEXP_REPLACE('Hello World', '(o)', '\1\1') AS replaced_string FROM DUAL; -- Returns 'Helloo Woorld'
14. REGEXP_INSTR: Returns the position of a substring matching a regular expression pattern.
SELECT REGEXP_INSTR('Hello 123 World', '[0-9]+') AS position FROM DUAL; -- Returns 7
SELECT REGEXP_INSTR('Hello 123 World', 'W.*d') AS position FROM DUAL; -- Returns 11
15. REGEXP_SUBSTR: Returns the substring matching a regular expression pattern.
SELECT REGEXP_SUBSTR('Hello 123 World', '[0-9]+') AS substring FROM DUAL; -- Returns '123'
SELECT REGEXP_SUBSTR('Hello 123 World', 'W.*d') AS substring FROM DUAL; -- Returns 'World'
16. REGEXP_COUNT: Returns the number of times a substring matching a regular expression pattern occurs in a string.
SELECT REGEXP_COUNT('Hello 123 World', '[0-9]') AS count FROM DUAL; -- Returns 3
SELECT REGEXP_COUNT('Hello World', 'o') AS count FROM DUAL; -- Returns 2
17. TRANSLATE: Replaces characters in a string with other characters.
SELECT TRANSLATE('Hello World', 'el', 'ip') AS translated FROM DUAL; -- Returns 'Hippo World'
SELECT TRANSLATE('123-456-7890', '1234567890', 'ABCDEFGHIJ') AS translated FROM DUAL; -- Returns 'ABC-DEF-GHIJ'
18. INITCAP: Converts the first letter of each word in a string to uppercase and all other letters to lowercase.
SELECT INITCAP('hello world') AS initcap_string FROM DUAL; -- Returns 'Hello World'
SELECT INITCAP('hELLO wORLD') AS initcap_string FROM DUAL; -- Returns 'Hello World'
19. SOUNDEX: Returns a phonetic representation of a string.
SELECT SOUNDEX('Smith') AS soundex_value FROM DUAL; -- Returns 'S530'
SELECT SOUNDEX('Smyth') AS soundex_value FROM DUAL; -- Returns 'S530'
20. ASCII: Returns the ASCII code of the first character of a string.
SELECT ASCII('A') AS ascii_value FROM DUAL; -- Returns 65
SELECT ASCII('a') AS ascii_value FROM DUAL; -- Returns 97
SELECT ASCII('!') AS ascii_value FROM DUAL; -- Returns 33
21. CHR: Returns the character corresponding to the ASCII code.
SELECT CHR(65) AS character FROM DUAL; -- Returns 'A'
SELECT CHR(97) AS character FROM DUAL; -- Returns 'a'
SELECT CHR(33) AS character FROM DUAL; -- Returns '!'
These string functions in Oracle provide powerful tools for manipulating and analyzing text data, making them essential for various data processing tasks.
Tags:
Oracle