This page provides some handy SQL string functions that allow you to manipulate character strings more effectively.
Section 1. Basic String Manipulation Functions #
- LENGTH – Returns the string’s length.
- POSITION – Returns the position of a substring within a string.
- SUBSTRING – Extracts a substring from a string based on position and length.
Section 2. String Concatenation Functions #
- CONCAT – Returns a new string that results from concatenating from two or more strings.
Section 3. Search and Replace Functions #
- REPLACE – Returns a new string with all occurrences of a substring in a string replaced with another new substring.
Section 4. Case Conversion Functions #
- TRIM – Returns a new string with all leading, trailing, or both specified characters removed from a string.
- LTRIM – Returns a new string with all leading whitespace or specified characters removed.
- RTRIM – Returns a new string with all trailing whitespace or specified characters removed.
Section 5. Trimming Characters #
- UPPER – Returns a new string with all characters converted to uppercase.
- LOWER – Returns a new string with all characters converted to lowercase.
Section 6. Positioning & Indexing Functions #
- LEFT – Returns the leftmost n characters of a string.
- RIGHT – Returns the rightmost n characters of a string.
- INSTR, LOCATE – Returns the position of the first occurrence of a substring.
- CHARINDEX – Returns the starting position of a substring.
Section 7. String Aggregation #
- STRING_AGG – Return the aggregated string from a set of strings.
Section 8. Encoding and Decoding Characters #
- ASCII – Returns the ASCII code of the first character in a string.
- CHR – Returns the character corresponding to an ASCII code.
Section 9. Regex Operations #
- REGEXP_MATCH – Returns true if a string matches a regular expression.
- REGEXP_REPLACE – Returns a new string with portions replaced based on a regular expression.
- REGEXP_SUBSTR – Returns a substring from a string based on a regular expression.
Section 10. Padding Strings #
- LPAD – Returns a new string with specified characters padded on the left to a certain length.
- RPAD – Returns a new string with specified characters padded on the right to a certain length.
Section 11. Utility Functions #
- QUOTE – Returns a string surrounded by quotes.
- UNQUOTE – Returns a string with quotes removed from a string.
- SOUNDEX – Returns the phonetic representation of a string.
- DIFFERENCE – Returns the difference between two strings using their SOUNDEX values.
