SELECT CONCAT(col_A,' ', col_B) AS concat_col FROM table_name; -- Concat 2 columns
SELECT col_A || ' ' || col_B AS concat_col FROM table_name; -- Concat 2 columns
SELECT UPPER(col) FROM table_name; -- Turning a column into all uppercase
SELECT LOWER(col) FROM table_name; -- Turning a column into all lowercase
SELECT LEFT(col, length) FROM table_name; -- Take n no of characters from the left side of the column
SELECT RIGHT(col, length) FROM table_name; -- Take n no of characters from the right side of the column
-- Extracting Substring
SELECT SUBSTRING(col, starting_index, length) FROM table_name; -- starting_index, length are integer values
SELECT SUBSTRING(col FROM starting_index FOR POSITION('@' IN col)) FROM table_name; -- starting_index, length are integer values
SELECT SUBSTR(col, starting_index, length) FROM table_name; -- starting_index, length are integer values
SELECT REVERSE(col) FROM table_name; -- Reverse a string
SELECT CHAR_LENGTH(col) FROM table_name; -- length of a string column
SELECT LENGTH(col) FROM table_name; -- length of a string column
SELECT POSITION('@' IN col) FROM table_name; -- position of a character in the string
SELECT STRPOS(email, '@') FROM table_name; -- position of a character in the string
SELECT REPLACE(col, 'old string', 'new string') FROM table_name; -- replacing characters
SELECT LPAD('padded', total_string_size, '#'); -- appending character from the left (####padded)
SELECT RPAD('padded', total_string_size, '#'); -- appending character from the right (padded####)
-- TRIM([leading | trailing | both] [characters] from string)
SELECT TRIM(BOTH 'x' FROM 'xxxxHelloxxxx') -- Hello
SELECT TRIM(TRAILING 'x' FROM 'xHelloxxxx') -- xHello
SELECT TRIM(LEADING 'x' FROM 'xxxxHellox') -- Hellox
-- Pattern matching
SELECT * FROM table_name WHERE col LIKE '_ELF%'; -- Case sensitive match
SELECT * FROM table_name WHERE col ILIKE '_ELF%'; -- Case insensitive match
SELECT * FROM table_name WHERE to_tsvector(col) @@ to_tsquery('elf'); -- Case insensitive match
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
SELECT levenshtein('GUMBO', 'GAMBOL'); -- levenshtein score of similarity between 2 words
CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT similarity('GUMBO', 'GAMBOL'); -- trigram similarity scoreASCII -- Returns the equivalent ASCII value for a specific character.
CHAR_LENGTH -- Returns the character length of a string.
CHARACTER_LENGTH -- Same as CHAR_LENGTH.
CONCAT -- Adds expressions together, with a minimum of 2.
CONCAT_WS -- Adds expressions together, but with a separator between each value.
FIELD -- Returns an index value relative to the position of a value within a list of values.
FIND IN SET -- Returns the position of a string in a list of strings.
FORMAT -- When passed a number, returns that number formatted to include commas (eg 3,400,000).
INSERT -- Allows you to insert one string into another at a certain point, for a certain number of characters.
INSTR -- Returns the position of the first time one string appears within another.
LCASE -- Converts a string to lowercase.
LEFT -- Starting from the left, extracts the given number of characters from a string and returns them as another.
LENGTH -- Returns the length of a string, but in bytes.
LOCATE -- Returns the first occurrence of one string within another,
LOWER -- Same as LCASE.
LPAD -- Left pads one string with another, to a specific length.
LTRIM -- Removes any leading spaces from the given string.
MID -- Extracts one string from another, starting from any position.
POSITION -- Returns the position of the first time one substring appears within another.
REPEAT -- Allows you to repeat a string
REPLACE -- Allows you to replace any instances of a substring within a string, with a new substring.
REVERSE -- Reverses the string.
RIGHT -- Starting from the right, extracts the given number of characters from a string and returns them as another.
RPAD -- Right pads one string with another, to a specific length.
RTRIM -- Removes any trailing spaces from the given string.
SPACE -- Returns a string full of spaces equal to the amount you pass it.
STRCMP -- Compares 2 strings for differences
SUBSTR -- Extracts one substring from another, starting from any position.
SUBSTRING -- Same as SUBSTR
SUBSTRING_INDEX -- Returns a substring from a string before the passed substring is found the number of times equals to the passed number.
TRIM -- Removes trailing and leading spaces from the given string. Same as if you were to run LTRIM and RTRIM together.
UCASE -- Converts a string to uppercase.
UPPER -- Same as UCASE.-- Creating a table to demonstrate the use of strings in SQL
CREATE TABLE players (
id INT,
name VARCHAR(100)
);
-- Inserting sample data
INSERT INTO players (id, name)
VALUES (1, 'John Doe'), (2, 'Jane Smith'), (3, 'Michael Johnson');
-- Searching for a specific string within the 'name' column using LIKE clause
SELECT * FROM players WHERE name LIKE '%Smith%';
-- Updating a string value
UPDATE players SET name = REPLACE(name, 'John', 'Mike') WHERE id = 1;
-- Concatenating strings
SELECT CONCAT('Hello', ' ', name) AS greeting FROM players WHERE id = 2;