SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL | Character Functions with Examples

Character functions in SQL operate on character data types (CHAR, VARCHAR, etc.), allowing you to manipulate and analyze string values. Below are some common character functions available in most relational database systems, accompanied by examples. Note that the exact syntax or function names may vary slightly among different RDBMS.

1. UPPER and LOWER:

  • UPPER: Converts all characters in a string to uppercase.
  • LOWER: Converts all characters in a string to lowercase.

Example:

SELECT UPPER('Hello World!');  -- Returns 'HELLO WORLD!'
SELECT LOWER('Hello World!');  -- Returns 'hello world!'

2. LENGTH:

Returns the number of characters in a string.

Example:

SELECT LENGTH('Hello');  -- Returns 5

3. TRIM:

Removes specified prefixes or suffixes from a string.

Example:

SELECT TRIM('   leading spaces');   -- Returns 'leading spaces'
SELECT TRIM(TRAILING 's' FROM 'excess');  -- Returns 'exce'

4. SUBSTRING or SUBSTR:

Extracts a portion of a string.

Example:

SELECT SUBSTRING('Hello World!', 1, 5);  -- Returns 'Hello'
-- or, depending on the RDBMS
SELECT SUBSTR('Hello World!', 1, 5);     -- Returns 'Hello'

5. CHARINDEX or INSTR:

Returns the position of the first occurrence of a substring.

Example:

-- In SQL Server:
SELECT CHARINDEX('World', 'Hello World!');  -- Returns 7
-- In Oracle:
SELECT INSTR('Hello World!', 'World');      -- Returns 7

6. CONCAT:

Concatenates two or more strings.

Example:

SELECT CONCAT('Hello', ' ', 'World!');  -- Returns 'Hello World!'

7. REPLACE:

Replaces occurrences of a substring within a string.

Example:

SELECT REPLACE('Hello World!', 'World', 'Universe');  -- Returns 'Hello Universe!'

8. LTRIM and RTRIM:

  • LTRIM: Removes leading spaces from a string.
  • RTRIM: Removes trailing spaces from a string.

Example:

SELECT LTRIM('   Hello');  -- Returns 'Hello'
SELECT RTRIM('Hello   ');  -- Returns 'Hello'

9. REVERSE:

Reverses a string.

Example:

SELECT REVERSE('Hello');  -- Returns 'olleH'

These are just a subset of character functions available in SQL. The specific set of functions and their exact behavior might vary based on the RDBMS you're using, so always refer to the official documentation for the precise syntax and examples for your database system.

  1. Examples of UPPER() and LOWER() Functions in SQL:

    SELECT UPPER(column1) AS upper_result,
       LOWER(column2) AS lower_result
FROM your_table;

  2. Using SQL CONCAT() Function with Strings:

    SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM your_table;

  3. Substring Extraction in SQL with SUBSTRING() Function:

    SELECT SUBSTRING(column1, 1, 3) AS substring_result
FROM your_table;

  4. SQL LENGTH() and LEN() Functions Examples:

    -- For MySQL, PostgreSQL
SELECT LENGTH(column1) AS length_result
FROM your_table;

-- For SQL Server
SELECT LEN(column1) AS length_result
FROM your_table;

  5. TRIM() Function in SQL for Whitespace Removal:

    SELECT TRIM(BOTH ' ' FROM column1) AS trimmed_result
FROM your_table;

  6. REPLACE() Function in SQL with Practical Use Cases:

    SELECT REPLACE(column1, 'old_text', 'new_text') AS replaced_result
FROM your_table;

  7. Character Functions in SQL for String Manipulation:

    SELECT CONCAT(LEFT(column1, 3), '...', RIGHT(column2, 2)) AS manipulated_result
FROM your_table;

  8. SQL LEFT() and RIGHT() Functions Examples:

    SELECT LEFT(column1, 3) AS left_result,
       RIGHT(column2, 2) AS right_result
FROM your_table;

  9. Searching for Patterns with SQL LIKE() Function:

    SELECT column1
FROM your_table
WHERE column1 LIKE 'ABC%';
Copyright © devanddep.com | All Rights Reserved