SQL Functions

SUBSTR

Returns specified substring with starting position and optional length

SUBSTR(@string,2,[4])

CHARINDEX

Returns the starting position of expression1 in expression2. It can take optional starting position for searching.

CHARINDEX('Search me',@search_string,[5])

CASE

SELECT store_name, CASE store_name
WHEN 'Los Angeles' THEN Sales * 2
WHEN 'San Diego' THEN Sales * 1.5
ELSE Sales
END
"New Sales",
Date
FROM Store_Information

SELECT CASE ("column_name")
WHEN "expression 1 is not NULL" THEN "expression 1"
WHEN "expression 2 is not NULL" THEN "expression 2"
ELSE "NULL"
END
FROM "table_name"

HAVING

It is like WHERE clause but used for Aggregate functions like SUM or COUNT

REPLACE

SELECT REPLACE(region_name, 'ast', 'astern')
FROM Geography;

IN

SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)

SELECT id, first_name
FROM student_details
WHERE first_name IN (SELECT first_name
FROM student_details
WHERE subject= 'Science');