String Functions.md

String Functions + Operations

Create BOOLEAN Result

SELECT Name, Age, Designation, (Designation LIKE 'Data Scientist') AS DataScience
FROM Employee;

Name	Age	Designation	DataScience
Kirankumar Yadav	26	Data Scientist	true
Paramveer Yadav	27	Data Analyst	false
Suraj MS	27	Data Scientist	true
Gaurav Sonar	28	Angular Developer	false
Pranit Sorte	29	Program Manager	false

Extract SUBSTRING from a string

SELECT SUBSTRING('Kirankumar Yadav', 1, 10) AS First_Name;

First_Name
Kirankumar

LIKE: Extract string using Patterns

• "_": represents a single character
• "%": represents wild character

SELECT 
Name, Age, Designation 
FROM Employee
WHERE Designation LIKE '%Developer' -- Ends with Developer LIKE (Software Developer, Hardware Developer...)

...
WHERE Designation LIKE 'Data%' -- Starts with Data LIKE (Data Scientist, Data Analyst, Data Engineer...)

...
WHERE Designation LIKE 'Data%|%Developer' -- Starts with Data or Ends with Developer.

Concat multiple strings

SELECT CONCAT(FirstName, ' ', LastName) AS Name;

-- Using pipe operator:
SELECT 'Kirankumar' || 'Yadav' AS Name;
SELECT first_name || last_name AS Name;

Name
Kirankumar Yadav

UPPER | LOWER | INITCAP

SELECT 
UPPER('Kirankumar Yadav') AS UpperCaseName,
LOWER('Kirankumar Yadav') AS LowerCaseName,
INITCAP('kirankumar yadav') AS TitleCaseName;

UpperCaseName	LowerCaseName	TitleCaseName
KIRANKUMAR YADAV	kirankumar yadav	Kirankumar Yadav

TRIM

SELECT "  Kirankumar Yadav  " AS Name;

Name
Kirankumar Yadav

Select DISTINCT values from column

SELECT 
DISTINCT(Designation)
FROM Employee;

Extension

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch

SOUNDEX: Finding similar sounding words.

SELECT 
SOUNDEX('Postgres') = SOUNDEX('Postgress') AS Soundex

Soundex
true

SELECT 
LEVENSHTEIN('Kiran', 'Kisan') AS LS

LS
1

SELECT LEVENSHTEIN('Happy', 'Unhappy') AS LS

LS
2