Using Single-Row Functions to Customize Output
These functions are used to change the case of characters in a string.
LOWER(string): Converts all characters in the string to lowercase.
Syntax: LOWER(expression)
Example:
SQL
SELECT LOWER('This is a STRING') AS lower_case_string;
This will output: lower_case_string = this is a string
UPPER(string): Converts all characters in the string to uppercase.
Syntax: UPPER(expression)
Example:
SQL
SELECT UPPER('This is a STRING') AS upper_case_string;
This will output: upper_case_string = THIS IS A STRING
INITCAP(string): Converts the first letter of each word in the string to uppercase,
and the rest to lowercase.
Syntax: INITCAP(expression)
Example:
SQL
SELECT INITCAP('This is a STRING') AS initcap_string;
This will output: initcap_string = This Is A String
Character manipulation functions
These functions are used to manipulate the content of a string.
CONCAT(string1, string2): Concatenates (joins) two strings string1 and string2.
Syntax: CONCAT(string1, string2)
Example:
SQL
SELECT CONCAT('Hello', 'World') AS concatenated_string;
This will output: concatenated_string = Hello World
SUBSTR(string, start, length): Extracts a substring from the string starting at the
start position (1-based index) and retrieves a specified number of characters
(length).
Syntax: SUBSTR(string, start, length)
Example:
SQL
SELECT SUBSTR('This is a STRING', 10, 6) AS substring_result;
This will output: substring_result = String (extracts characters from index 10 to
15)
LENGTH(string): Returns the length (number of characters) of the string.
Syntax: LENGTH(expression)
Example:
SQL
SELECT LENGTH('This is a STRING') AS string_length;
This will output: string_length = 16
INSTR(string, substring): Searches for the first occurrence of the substring within
the string and returns the position (1-based index) of the first character of the
substring. If the substring is not found, it returns 0.
Syntax: INSTR(string, substring)
Example:
SQL
SELECT INSTR('This is a STRING', 'is') AS substring_position;
This will output: substring_position = 2 (finds the index of "is")
LPAD(string, length, pad_string): Pads the string with the pad_string on the left
to a total length of length. If the pad_string is not specified, it defaults to
spaces.
Syntax: LPAD(string, length, pad_string)
Example:
SQL
SELECT LPAD('This is a STRING', 30, '*') AS left_padded_string;
This will output: left_padded_string = *****************This is a STRING
RPAD(string, length, pad_string): Pads the string with the pad_string on the right
to a total length of length. If the pad_string is not specified, it defaults to
spaces.
Syntax: RPAD(string, length, pad_string)
Example:
SQL
SELECT RPAD('This is a STRING', 30, '*') AS right_padded_string;
This will output: right_padded_string = This is a STRING*****************
SELECT RPAD(' ',10,'*') FROM DUAL; as ********* 9 stars because there is a space
TRIM(string): Removes leading and trailing spaces from the string. You can also
optionally specify trailing or leading characters to remove using
