SQL : String Manipulation

In PostgreSQL, the TRIM function is used to remove both leading and trailing spaces (or specified characters) from a string. It is essentially a combination of LTRIM (left trim) and RTRIM (right trim).

Syntax:

TRIM([LEADING | TRAILING | BOTH] [characters FROM] string)

LEADING: Removes only leading characters (default is spaces) from the string.
TRAILING: Removes only trailing characters (default is spaces) from the string.
BOTH (or no option specified): Removes both leading and trailing characters (default is spaces) from the string.

Example 1: Trimming Spaces

Sample Data:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT
);

INSERT INTO users (username) VALUES
    ('  Alice  '),
    ('  Bob'),
    ('Charlie  '),
    ('  Dave   ');

Query:

SELECT id, username, TRIM(username) AS trimmed_username
FROM users;

Output:

 id | username   | trimmed_username
----+------------+------------------
  1 |   Alice    | Alice
  2 |   Bob      | Bob
  3 | Charlie    | Charlie
  4 |   Dave     | Dave

In this example, TRIM removes both leading and trailing spaces from the username values.

Example 2: Trimming Specific Characters

You can specify the character(s) you want to trim. Let's trim underscores (_) from the beginning and end of a string.

Sample Data:

INSERT INTO users (username) VALUES
    ('__Eve__'),
    ('__Frank_');

Query:

SELECT id, username, TRIM('_' FROM username) AS trimmed_username
FROM users
WHERE username LIKE '__%';

Output:

 id | username   | trimmed_username
----+------------+------------------
  5 | __Eve__    | Eve
  6 | __Frank_   | Frank

In this case, TRIM('_' FROM username) removes the leading and trailing underscores.

Variations:

Removing only leading characters: sql SELECT TRIM(LEADING '_' FROM '__Alice__') AS trimmed_leading;

Output: ``` trimmed_leading

Alice__ ```

Removing only trailing characters: sql SELECT TRIM(TRAILING '_' FROM '__Alice__') AS trimmed_trailing;

Output: ``` trimmed_trailing

__Alice ```

Summary:

TRIM removes both leading and trailing spaces or specified characters from a string.
You can specify whether to trim only from the leading (left), trailing (right), or both (default) sides.

In PostgreSQL, the LTRIM and RTRIM functions are used to remove leading and trailing spaces (or specified characters) from a string.

LTRIM: Removes leading spaces (or specified characters) from the left side of a string.
RTRIM: Removes trailing spaces (or specified characters) from the right side of a string.

Example 1: Using `LTRIM` and `RTRIM` to remove spaces

Let's assume we have a table users with a column username that contains some values with extra spaces at the beginning and/or end.

Sample Data:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT
);

INSERT INTO users (username) VALUES
    ('  Alice  '),
    ('  Bob'),
    ('Charlie  '),
    ('  Dave   ');

Using `LTRIM` and `RTRIM`:

Removing Leading Spaces with LTRIM: sql SELECT id, username, LTRIM(username) AS trimmed_username FROM users;

The LTRIM function has removed the spaces from the left side of the username values.

Removing Trailing Spaces with RTRIM: sql SELECT id, username, RTRIM(username) AS trimmed_username FROM users;

The RTRIM function has removed the spaces from the right side of the username values.

Example 2: Removing Specific Characters

Both LTRIM and RTRIM can also be used to remove specific characters instead of just spaces. For example, you might want to remove leading or trailing underscores (_).

Sample Data:

INSERT INTO users (username) VALUES
    ('__Eve__'),
    ('__Frank_');

Removing Leading and Trailing Underscores with LTRIM and RTRIM: sql SELECT id, username, LTRIM(username, '_') AS ltrimmed, RTRIM(username, '_') AS rtrimmed FROM users WHERE username LIKE '__%';

Here, LTRIM removes leading underscores, and RTRIM removes trailing underscores.

In PostgreSQL, UPPER and LOWER are string functions used to convert text to uppercase and lowercase, respectively.

1. `UPPER`:

Converts all characters in a string to uppercase.

Syntax:

UPPER(string)

Example:

SELECT UPPER('hello world') AS uppercase_string;

Output:

 uppercase_string
------------------
 HELLO WORLD

Example with a table:

Assume we have a table users with the following data:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT
);

INSERT INTO users (username) VALUES
    ('Alice'),
    ('Bob'),
    ('charlie'),
    ('dave');

You can convert all the usernames to uppercase:

SELECT id, username, UPPER(username) AS uppercase_username
FROM users;

Output:

 id | username | uppercase_username
----+----------+--------------------
  1 | Alice    | ALICE
  2 | Bob      | BOB
  3 | charlie  | CHARLIE
  4 | dave     | DAVE

2. `LOWER`:

Converts all characters in a string to lowercase.

Syntax:

LOWER(string)

Example:

SELECT LOWER('HELLO WORLD') AS lowercase_string;

Output:

 lowercase_string
------------------
 hello world

Example with a table:

To convert all the usernames to lowercase:

SELECT id, username, LOWER(username) AS lowercase_username
FROM users;

Output:

 id | username | lowercase_username
----+----------+--------------------
  1 | Alice    | alice
  2 | Bob      | bob
  3 | charlie  | charlie
  4 | dave     | dave

3. Practical Use Cases:

Case-insensitive searches: When you want to make searches case-insensitive, you can convert both the input and the stored data to the same case.

sql SELECT * FROM users WHERE LOWER(username) = LOWER('ALICE');

Standardizing data: You can use UPPER or LOWER to standardize data for consistency (e.g., converting all email addresses to lowercase).

Summary:

UPPER converts all characters to uppercase.
LOWER converts all characters to lowercase.

In PostgreSQL, you can convert a numerical column to a string (text) and vice versa using the following functions:

1. Convert Numerical Column to String

To convert a numerical value to a string (text), you can use the ::text cast or the TO_CHAR() function.

Using `::text`:

The easiest way to convert a number to a string is by using the ::text casting.

Syntax:

SELECT column_name::text FROM table_name;

Example:

Assume we have a table products with a column price (numeric):

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC
);

INSERT INTO products (name, price) VALUES
    ('Product A', 100),
    ('Product B', 250),
    ('Product C', 99.99);

To convert the price column from a numeric value to a string:

SELECT id, name, price::text AS price_text
FROM products;

Output:

 id |   name    | price_text
----+-----------+------------
  1 | Product A | 100
  2 | Product B | 250
  3 | Product C | 99.99

Using `TO_CHAR()`:

You can also use the TO_CHAR() function to convert a number to a string, with the option to format it.

Syntax:

SELECT TO_CHAR(numeric_column, 'format') FROM table_name;

Example with formatting:

SELECT id, name, TO_CHAR(price, 'FM999.00') AS price_formatted
FROM products;

Output:

 id |   name    | price_formatted
----+-----------+----------------
  1 | Product A | 100.00
  2 | Product B | 250.00
  3 | Product C | 99.99

2. Convert String Column to Numerical

To convert a string (text) column to a number, you can use the ::numeric (or ::integer, ::bigint) casting or the TO_NUMBER() function.

Using `::numeric` or `::integer`:

You can cast a string to a numeric value using the ::numeric or ::integer cast.

Syntax:

SELECT column_name::numeric FROM table_name;

Example:

Assume you have a text column price_text that contains numbers as strings:

CREATE TABLE products_with_text_price (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price_text TEXT
);

INSERT INTO products_with_text_price (name, price_text) VALUES
    ('Product X', '100'),
    ('Product Y', '250'),
    ('Product Z', '99.99');

To convert the price_text column to a numeric value:

SELECT id, name, price_text::numeric AS price_numeric
FROM products_with_text_price;

Output:

 id |   name    | price_numeric
----+-----------+---------------
  1 | Product X | 100
  2 | Product Y | 250
  3 | Product Z | 99.99

Using `TO_NUMBER()`:

You can also use the TO_NUMBER() function to convert a string to a number, especially if the string has a specific format.

Syntax:

SELECT TO_NUMBER(text_column, 'format') FROM table_name;

Example:

If price_text has formatting characters like commas or currency symbols:

SELECT id, name, TO_NUMBER(price_text, '999G999D99') AS price_numeric
FROM products_with_text_price;

Output:

 id |   name    | price_numeric
----+-----------+---------------
  1 | Product X | 100
  2 | Product Y | 250
  3 | Product Z | 99.99

Summary:

Convert number to string:
Use ::text or TO_CHAR() to convert numerical values to string format.
Convert string to number:
Use ::numeric, ::integer, or TO_NUMBER() to convert text to numerical format.

In PostgreSQL, the LENGTH and POSITION functions are used to manipulate and analyze strings.

1. `LENGTH`

The LENGTH function returns the number of characters in a string.

Syntax:

LENGTH(string)

Example 1: Basic Usage

SELECT LENGTH('PostgreSQL') AS string_length;

Output:

 string_length
---------------
            10

In this example, the string 'PostgreSQL' contains 10 characters.

Example 2: Length of a Column Value

Assume we have a table employees with a column name:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT
);

INSERT INTO employees (name) VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie');

You can use LENGTH to get the length of each name:

SELECT id, name, LENGTH(name) AS name_length
FROM employees;

Output:

 id |  name   | name_length
----+---------+-------------
  1 | Alice   | 5
  2 | Bob     | 3
  3 | Charlie | 7

2. `POSITION`

The POSITION function finds the location of a substring within a string. It returns the position (starting from 1) where the substring is first found. If the substring is not found, it returns 0.

Syntax:

POSITION(substring IN string)

Example 1: Basic Usage

SELECT POSITION('gre' IN 'PostgreSQL') AS substring_position;

Output:

 substring_position
--------------------
                  5

In this case, the substring 'gre' starts at the 5th position in the string 'PostgreSQL'.

Example 2: Finding Substring in a Column Value

Using the same employees table:

SELECT id, name, POSITION('a' IN name) AS position_of_a
FROM employees;

Output:

 id |  name   | position_of_a
----+---------+---------------
  1 | Alice   |             1
  2 | Bob     |             0
  3 | Charlie |             4

In this case: - 'a' is found at position 1 in the name 'Alice'. - 'a' is not found in the name 'Bob', so the position is 0. - 'a' is found at position 4 in the name 'Charlie'.

Practical Use Cases:

Finding String Lengths for Validation: You can use LENGTH to validate the length of a string, e.g., to check if a name exceeds a maximum length.
Searching for Substrings: POSITION can be useful for finding the position of specific characters or substrings in a column, for example, finding a domain in email addresses.

Summary:

LENGTH: Returns the number of characters in a string.
POSITION: Returns the starting position of a substring in a string, or 0 if the substring is not found.

Concat strings

In a table we have two columns first_name and last_name, while running the sql query i want to combine first_name and second_name and output it as a full_name , how to do it ?

You can concatenate the first_name and last_name columns in PostgreSQL using the CONCAT() function or the || operator. Here's an example SQL query for both methods:

Using `CONCAT()` function:

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

Using `||` operator:

SELECT first_name || ' ' || last_name AS full_name
FROM your_table;

Both of these queries will combine the first_name and last_name into a full_name, separated by a space.

SUBSTRING function

The SUBSTRING function in PostgreSQL is used to extract a portion (or substring) of a string. It allows you to specify the starting position and the length of the substring you want to extract from a larger string.

Syntax:

SUBSTRING(string FROM start_position FOR length)

string: The string from which the substring will be extracted.
start_position: The starting position in the string (1-based index).
length: The number of characters to extract (optional).

Example 1: Extract a portion of the string

SELECT SUBSTRING('Hello World' FROM 1 FOR 5) AS result;

Output:

 result
--------
 Hello

In this example, it extracts the first 5 characters from the string 'Hello World'.

Example 2: Extract without specifying the length

SELECT SUBSTRING('Hello World' FROM 7) AS result;

Output:

 result
--------
 World

Here, the substring starts from position 7 and continues to the end of the string.

Example 3: Using with a column

SELECT SUBSTRING(first_name FROM 1 FOR 3) AS short_name
FROM your_table;

This extracts the first 3 characters from the first_name column.

Using REPLACE

The REPLACE function in PostgreSQL is used to replace all occurrences of a specified substring within a string with another substring.

Syntax:

REPLACE(string, substring_to_replace, replacement)

string: The original string where you want to perform the replacement.
substring_to_replace: The substring you want to find and replace.
replacement: The substring to replace the old one with.

Example 1: Replace part of a string

SELECT REPLACE('PostgreSQL is awesome', 'awesome', 'great') AS result;

Output:

       result
---------------------
 PostgreSQL is great

In this example, the word "awesome" is replaced by "great".

Example 2: Replace multiple occurrences of a substring

SELECT REPLACE('banana', 'a', 'o') AS result;

Output:

 result
---------
 bonono

Here, all occurrences of the letter "a" are replaced with "o", resulting in "bonono".

Example 3: Using `REPLACE` with a column

SELECT REPLACE(first_name, 'a', 'e') AS updated_name
FROM your_table;

This replaces all occurrences of the letter "a" with "e" in the first_name column for all rows in the table.

Example 4: Removing a substring

If you want to remove a substring entirely, you can replace it with an empty string '':

SELECT REPLACE('2024-09-12', '-', '') AS result;

Output:

  result
----------
 20240912

This removes all hyphens - from the date string, resulting in 20240912.

Syntax:

Example 1: Trimming Spaces

Sample Data:

Query:

Output:

Example 2: Trimming Specific Characters

Sample Data:

Query:

Output:

Variations:

Summary:

Example 1: Using LTRIM and RTRIM to remove spaces

Sample Data:

Using LTRIM and RTRIM:

Example 2: Removing Specific Characters

Sample Data:

1. UPPER:

Syntax:

Example:

Example with a table:

2. LOWER:

Syntax:

Example:

Example with a table:

3. Practical Use Cases:

Summary:

1. Convert Numerical Column to String

Using ::text:

Syntax:

Example:

Using TO_CHAR():

Syntax:

Example with formatting:

2. Convert String Column to Numerical

Using ::numeric or ::integer:

Syntax:

Example:

Using TO_NUMBER():

Syntax:

Example:

Summary:

1. LENGTH

Syntax:

Example 1: Basic Usage

Example 2: Length of a Column Value

2. POSITION

Syntax:

Example 1: Basic Usage

Example 2: Finding Substring in a Column Value

Practical Use Cases:

Summary:

Concat strings

Using CONCAT() function:

Using || operator:

SUBSTRING function

Syntax:

Example 1: Extract a portion of the string

Example 2: Extract without specifying the length

Example 3: Using with a column

Using REPLACE

Syntax:

Example 1: Replace part of a string

Example 2: Replace multiple occurrences of a substring

Example 3: Using REPLACE with a column

Example 4: Removing a substring

links

social

Example 1: Using `LTRIM` and `RTRIM` to remove spaces

Using `LTRIM` and `RTRIM`:

1. `UPPER`:

2. `LOWER`:

Using `::text`:

Using `TO_CHAR()`:

Using `::numeric` or `::integer`:

Using `TO_NUMBER()`:

1. `LENGTH`

2. `POSITION`

Using `CONCAT()` function:

Using `||` operator:

Example 3: Using `REPLACE` with a column