CAST, CONCAT
1. CAST - Ensure Phone is Stored as a String
SELECT CustomerID, '006' + CAST(Phone AS NVARCHAR) AS Phone_Number
FROM [SalesLT].[Customer]
WHERE Phone LIKE '93%';
2. COALESCE - Handling NULL Phone Numbers
-- Handling NULL phone values
SELECT CustomerID, '006' + COALESCE(Phone, '') AS Phone_Number --if Phone is
NULL, return NULL instead of '006' + Phone.
FROM [SalesLT].[Customer]
WHERE Phone LIKE '93%';
-- Convert Phone into string
SELECT CustomerID, '006' + CAST(Phone AS NVARCHAR) AS Phone_Number
FROM [SalesLT].[Customer]
WHERE Phone LIKE '93%';
-- Combine
SELECT CustomerID, '006' + CAST(COALESCE(Phone, '') AS NVARCHAR) AS
Phone_Number
FROM [SalesLT].[Customer]
WHERE Phone LIKE '93%';
3. Using ISNULL() Instead of COALESCE()
SELECT CustomerID,
'006' + CAST(ISNULL(Phone, '') AS NVARCHAR(20)) AS Phone_Number
FROM [SalesLT].[Customer]
WHERE Phone LIKE '93%';
4. Using CONCAT
SELECT AddressID, CONCAT(PostalCode, 'X') AS PostalCode -- using concat
instead of +
FROM [SalesLT].[Address]
WHERE PostalCode LIKE '%[A-Za-z -]%';
CONCAT, ISNULL, CAST
CONCAT(ISNULL(CAST(Weight AS NVARCHAR(50)), 'Unknown'), ' grams') AS Weight
-- Convert data type
-- Treating Null values
-- Concatenate values
WILDCARDS %, ^, _, []
Like Example Explanation
'%[A-Za-z -]%' K4B 1S2X => OK Contain at least 1 of letters (A-Z,
1234 => NG a-z), spaces ( ), hyphens (-)
'%[^A-Za-z]%' K4B 1S2X => OK Contain at least 1 numeric character,
ABCD => NG CANN’T contain only letters A-Z, a-z.
'%[0-9]%' K4B 1S2X => OK Contains at least any single digit
ABCD => NG (0-9).
'%[^0-9]%' K4B 1S2X => OK Contain at least 1 non-numeric
ABCD => NG character. CANN’T contain all numeric
characters
[^0-9]%' Matches values where the first
character is NOT a digit.
'%[0-9]' Matches values where the last
character is a digit.
1. Non-numeric postal code
'%[A-Za-z -]%' -- Letters (A-Z, a-z), spaces ( ), hyphens (-)
'%[^0-9]%' – Not any numeric from 0-9
[ ]: Match any single character within a set of characters.
WHERE PostalCode LIKE '[A-Za-z]' -- Matches any single letter (A-Z, a-z).
WHERE PostalCode LIKE '[0-9]' -- Matches any single digit (0-9).
^: Caret - Negates a character set, meaning “NOT these characters”
Select AddressID, ISNULL(PostalCode, '') + 'X' as PostalCode
From [SalesLT].[Address]
Where PostalCode like '%[A-Za-z -]%';
Select AddressID, ISNULL(PostalCode, '') + 'X' as PostalCode
From [SalesLT].[Address]
Where PostalCode like '%[^0-9]%';
2. Numeric postal code
SELECT AddressID, CONCAT(PostalCode, 'X') AS PostalCode -- using concat
instead of +
FROM [SalesLT].[Address]
WHERE PostalCode Not LIKE '%[^0-9]%';
3. _ (Underscore): Matches exactly one character at the specified position
WHERE PostalCode LIKE 'A_1'
Between … and… / In ( , , )
-- Using Between and
Select ProductID, ProductCategoryID
From [SalesLT].[Product]
Where (DiscontinuedDate < Getdate() or SellEndDate < Getdate()) and ProductCategoryID
between 5 and 7;
-- Using in ()
Select ProductID, ProductCategoryID
From [SalesLT].[Product]
Where (DiscontinuedDate < Getdate() or SellEndDate < Getdate()) and ProductCategoryID
in (5,6,7);
GETDATE(): Retrieves the current date and time.
BETWEEN 5 AND 7: Ensures that ProductCategoryID is within the range [5, 7], inclusive.
IN (5,6,7): Explicitly lists the allowed values for ProductCategoryID.
Select Products with Max Cost
Using nested select statement
-- DON'T select top 1, as we may have multiple products with max standard cost
Select ProductID, Name, ProductNumber, StandardCost
From [SalesLT].[Product]
Where Color = 'Black'
and StandardCost = (Select Max(StandardCost)
From [SalesLT].[Product]
Where color = 'Black')
Using CTE
With MaxCost As (
Select Max(StandardCost) As MaxCost
From [SalesLT].[Product]
Where Color = 'Black')
Select ProductID, Name, ProductNumber, StandardCost
From [SalesLT].[Product]
Where Color = 'Black'
And StandardCost = (Select MaxCost From MaxCost)
Left, Right, Len, Substring & Charindex
1. Left & Charindex
Select CustomerID, Left(EmailAddress, Charindex('@', EmailAddress) - 1) As
UserName
From [SalesLT].[Customer]
Order by CustomerID
CHARINDEX('@', EmailAddress) -- Finds the position of @ in the
EmailAddress.
LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1) -- Extracts the
substring from the start of EmailAddress up to the character before @.
CHARINDEX finds the exact position of a specific substring in a string.
CHARINDEX(expressionToFind, expressionToSearch, [start_location])
expressionToFind: The substring you want to locate.
expressionToSearch: The column or text where you are searching.
start_location (optional): The position where the search should start (default is 1).
2. Right & Len-Charindex
Select CustomerID, Right(EmailAddress, Len(EmailAddress) - Charindex('@',
EmailAddress) + 1) As EmailProvider
From [SalesLT].[Customer]
Order by CustomerID
3. Patindex
-- Extract the username part first
With UserName as (
Select CustomerID, Left(EmailAddress, Charindex('@', EmailAddress) - 1) As
UserName
From [SalesLT].[Customer])
-- Left & Patindex to find letter, Right & Len – Patindex to find number
Select UserName,
Left(UserName, PATINDEX('%[0-9]%', Username)-1) As Letter,
Right(Username, Len(Username)- PATINDEX('%[0-9]%', Username) +1) as
Number
From UserName
PATINDEX finds the position of a pattern in a string using wildcards (%, [0-9], [A-Z], etc.).
PATINDEX('%pattern%', expressionToSearch)
pattern: A wildcard pattern (supports %, _, [range]).
expressionToSearch: The column or text where you are searching.
Feature CHARINDEX PATINDEX
Search Type Exact match of a substring Pattern matching with wildcards
Wildcard Support ❌ No ✅ Yes (%, [A-Z], [0-9])
Case Sensitivity Case-insensitive (default collation) Case-insensitive (default collation)
Performance Faster (uses direct search) Slower (uses pattern matching)
When to Use Find exact words like '@', '-', 'abc' Find patterns like numbers, letters
4. Handle NULL values, Using case when
-- Handle null values
WITH UserName AS (
SELECT CustomerID,
LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1) AS UserName
FROM [SalesLT].[Customer]
)
SELECT
UserName,
CASE
WHEN PATINDEX('%[0-9]%', UserName) > 0 -- Check null values
THEN LEFT(UserName, PATINDEX('%[0-9]%', UserName) - 1)
ELSE 'No Letter'
END AS Letter,
CASE
WHEN PATINDEX('%[0-9]%', UserName) > 0 -- Check null values
THEN RIGHT(UserName, LEN(UserName) - PATINDEX('%[0-9]%', UserName) + 1)
ELSE 'No Number'
END AS Number
FROM UserName;
5. Substring & Charindex & Len
SELECT BusinessEntityID, FirstName, MiddleName, LastName,
SUBSTRING(FirstName, 2, 3) AS MidPart
FROM [Link];
SUBSTRING: Extracts 3 characters from the FirstName, starting from the 2nd character.
SELECT ProductID, ProductNumber,
SUBSTRING(ProductNumber, CHARINDEX('-', ProductNumber) + 1,
LEN(ProductNumber)) AS AfterDash
FROM [Link]
WHERE ProductNumber LIKE '%-%';
SUBSTRING(ProductNumber, CHARINDEX('-', ProductNumber) + 1, LEN(ProductNumber))
Extract the part after (-) till the end
And/Or
/* 11. Write a query to display the ProductID, ProductNumber, Name, Size, Weight
for Products that are still for sale and have a Size but that size is not “S”.
SELECT ProductID, ProductNumber, Name, Weight,
ISNULL(CAST(Size AS NVARCHAR(50)), 'Unknown Size') AS Size
FROM [SalesLT].[Product]
WHERE (SellEndDate IS NULL OR SellEndDate > GETDATE())
AND (DiscontinuedDate IS NULL OR DiscontinuedDate > GETDATE())
AND (Size IS NULL OR Size NOT LIKE 'S');
1. Using ISNULL/COALESCE to deal with null values, instead of CASE WHEN
2. CAST size into larger characters to store ‘Unknown Size’
3. Be careful of the condition ‘… is not…’:
If we only use Size NOT LIKE ‘S’, products with Size = NULL will be excluded because NULL
does not compare to anything.
Similar to SellEndDate, if we don’t include IS NULL, we might accidentally exclude products
that have never been discontinued.
Using ( ) in each condition
WHERE (SellEndDate IS NULL OR SellEndDate > GETDATE())
AND (DiscontinuedDate IS NULL OR DiscontinuedDate > GETDATE())
AND (Size IS NULL OR Size NOT LIKE 'S');
Replace, Ltrim, Rtrim
SELECT CustomerID, FirstName, MiddleName, LastName
FROM [Link]
WHERE LEN(REPLACE(CONCAT(Title, FirstName, MiddleName, LastName, Suffix), ' ',
''))> 30;
Remove leading spaces
SELECT LTRIM(' Hello World ') AS Result;
Remove Trailing spaces
SELECT RTRIM(' Hello World ') AS Result;
Remove both leading and trailing spaces
SELECT LTRIM(RTRIM(' Hello World ')) AS Result;
Getdate() / Datediff() / Dateadd()
--8. List the ProductID, Name, and Weight of products that have a Weight above
the average Weight and were modified in the last 20 years.
Select *
From [SalesLT].[Product]
Select ProductID, Name, Weight
From [SalesLT].[Product]
Where Weight > (Select AVG(Weight)
From [SalesLT].[Product])
AND Datediff (Year, ModifiedDate, Getdate()) <= 20
Order by Weight DESC
DATEDIFF(YEAR, '2000-01-01', '2024-03-25') 3 arguments
The time unit to measure the difference (e.g., YEAR, MONTH, DAY).
start_date → The earlier date.
end_date → The later date.
– Getdate() will return todays date and 20 years have been substracted from it
using the DATEADD function
SELECT ProductID, Name, Weight
FROM [Link]
WHERE
Weight > (SELECT AVG(Weight) FROM [Link])
AND ModifiedDate >= DATEADD(YEAR, -20, GETDATE())
ORDER BY Weight DESC;
DATEADD(YEAR, 5, '2020-01-01') AS NewDate
interval → The unit of time to add or subtract (e.g., YEAR, MONTH, DAY).
number → The number of units to add (positive) or subtract (negative).
date → The starting date.
Comparison and IS NULL
-- Be careful when comparing TotalAmt < 1000. Check NULL first
SELECT [Link], CONCAT([Link], ' ', [Link]) AS FullName,
CASE
WHEN (SUM([Link]) IS NULL or SUM([Link]) < 1000) THEN 'Low
Spender'
WHEN SUM([Link]) BETWEEN 1000 AND 5000 THEN 'Medium Spender'
ELSE 'High Spender'
END AS SpendingCategory
FROM [SalesLT].[Customer] c
INNER JOIN [SalesLT].[SalesOrderHeader] soh ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
ORDER BY
CASE
WHEN (SUM([Link]) IS NULL or SUM([Link]) < 1000) THEN 1
WHEN SUM([Link]) BETWEEN 1000 AND 5000 THEN 2
ELSE 3
END ASC;
WHEN (SUM([Link]) IS NULL or SUM([Link]) < 1000) THEN 'Low Spender'
When customers spend nothing, we also consider them as low spender
Check for NULL:
Compare – less than
Compare – is not something
Distinct - Be cautious when using DISTINCT as NULL values are treated as equal in some cases
WHERE and LEFT JOIN … ON <2 conditions>
List ALL customers along with their most recent order details. Include customers who have never
placed an order.
-- CORRECT ANSWER: Using Left join with 2 ON conditions, instead of WHERE
WITH RECENT AS (
SELECT
[Link],
MAX([Link]) AS 'Most recent order date'
FROM
[SalesLT].[SalesOrderHeader] AS soh
GROUP BY
[Link])
SELECT
[Link],
CONCAT([Link], ' ', [Link]) AS FullName,
[Link],
[Link]
FROM
[SalesLT].[Customer] AS c
LEFT JOIN
[SalesLT].[SalesOrderHeader] AS soh
ON [Link] = [Link]
LEFT JOIN
RECENT AS r
ON [Link] = [Link] -- Link recent table with customer table
AND [Link] = r.[Most recent order date] --Link order date
ORDER BY
[Link];
----- WRONG -------
With RECENT as (Select [Link],
Max([Link]) as 'Most recent order date'
From [SalesLT].[SalesOrderHeader] as soh
Group by [Link])
SELECT
[Link],
CONCAT([Link], ' ', [Link]) AS FullName,
[Link],
[Link]
FROM [SalesLT].[Customer] AS c
LEFT JOIN [SalesLT].[SalesOrderHeader] AS soh
ON [Link] = [Link]
LEFT JOIN RECENT as r
ON [Link] = [Link]
Where [Link] = r.[Most recent order date] -- Auto excludes customers who
have no orders => Not Handling customers who never placed an order
Be careful:
SELECT
[Link],
SalesorderID, –- WRONG
MAX ([Link]) as 'Most recent order date' –- WRONG, as each customer ID
+ SalesorderiD is a unique combination => Return nothing when adding MAX