Contents

Extract the nth word of a string using STRING_SPLIT............................................................................................................6

1. Extract the first word from a sentence...........................................................................................................................11
2. Extract the second word from a sentence......................................................................................................................11
3. Extract the last word from a sentence............................................................................................................................12
4. Replace all occurrences of "ABC" with "XYZ"..................................................................................................................12
5. Convert all text to uppercase..........................................................................................................................................12
6. Convert all text to lowercase..........................................................................................................................................12
7. Capitalize the first letter of each word............................................................................................................................12
8. Remove leading and trailing spaces................................................................................................................................13
9. Remove all spaces...........................................................................................................................................................13
10. Concatenate two columns with a space.......................................................................................................................13
11. Reverse the string.........................................................................................................................................................13
12. Extract only numeric part from a string. Also letters only and special characters only.................................................13
13. Extract only the alphabetic part from a string..............................................................................................................18
14. Find the position of the first occurrence of a substring................................................................................................19
15. Find the position of the last occurrence of a substring.................................................................................................19
16. Pad a number with leading zeroes to make it five digits long.......................................................................................20
17. Extract characters after the first occurrence of a specific character (:)........................................................................20
18. Extract characters before the first occurrence of a specific character (:).....................................................................20
19. Extract characters between two specific characters ({})...............................................................................................21
20. Count the number of words in a column......................................................................................................................21
21. Replace multiple spaces with a single space.................................................................................................................21
22. Split a comma-separated string into multiple rows......................................................................................................21
23. Convert a string to proper case (first letter uppercase, rest lowercase).......................................................................22
24. Generate a unique identifier by appending a timestamp to a string............................................................................22
25. Extract the domain name from an email address.........................................................................................................22
101. Extract the last word from a string column.................................................................................................................23
102. Convert a string to proper case (capitalize first letter of each word)..........................................................................23
103. Replace multiple spaces in a string with a single space..............................................................................................23
104. Remove leading and trailing special characters from a string.....................................................................................23
105. Count the number of occurrences of a specific character in a string..........................................................................24
106. Split a comma-separated string into multiple rows....................................................................................................24
107. Reverse a given string.................................................................................................................................................24
108. Extract the domain name from an email address.......................................................................................................24
109. Replace digits in a string with '*'.................................................................................................................................24
110. Find the position of the second occurrence of a character in a string........................................................................25
111. Convert a delimited string into an array and retrieve the third element....................................................................25
112. Concatenate first name and last name with a space in between................................................................................25
113. Remove all non-numeric characters from a string......................................................................................................25
114. Check if a string contains only alphabets....................................................................................................................25
115. Extract the first 5 characters from a string.................................................................................................................26
116. Convert a numeric column to a zero-padded string of length 10...............................................................................27
117. Find the difference in length between a string before and after removing spaces.....................................................27
118. Convert a list of names into a single comma-separated string...................................................................................27
119. Extract initials from a full name..................................................................................................................................27
120. Swap the first and last words of a sentence...............................................................................................................27
121. Mask all characters in a string except the last 4.........................................................................................................28
122. Find the most common character in a string..............................................................................................................28
123. Convert a sentence into an acronym..........................................................................................................................28
124. Extract the first 3 words from a sentence...................................................................................................................28
125. Trim spaces only from the left side of a string............................................................................................................29
201. Extract the last two words from a string column........................................................................................................29
202. Remove duplicate characters from a string................................................................................................................30
203. Replace all vowels in a string with '#'..........................................................................................................................30
204. Convert a string into a title case format.....................................................................................................................30
205. Find the third occurrence of a specific character in a string.......................................................................................30
206. Swap the first and last characters of a string..............................................................................................................31
207. Reverse words in a string while maintaining their positions.......................................................................................31
208. Extract the country code from a phone number stored in '+91-9876543210'............................................................31
209. Convert a camelCase string into a space-separated string.........................................................................................31
210. Remove the first occurrence of a specific character from a string..............................................................................31
211. Extract all numbers from an alphanumeric string.......................................................................................................32
212. Identify and extract duplicate words from a string.....................................................................................................32
213. Find the first non-repeating character in a string.......................................................................................................32
214. Convert a tab-separated string into a comma-separated format...............................................................................32
215. Remove special characters from a string except spaces and hyphens........................................................................33
216. Convert a sentence into Pig Latin format....................................................................................................................34
217. Find the longest word in a sentence...........................................................................................................................34
218. Replace the last occurrence of a character in a string................................................................................................34
219. Find all strings that contain only consonants..............................................................................................................34
220. Convert spaces in a string to underscores..................................................................................................................34
221. Extract text between two specific words in a string...................................................................................................34
222. Check if a string contains only uppercase letters........................................................................................................35
223. Find the frequency of each character in a string.........................................................................................................35
224. Identify palindromic words within a string.................................................................................................................35
225. Convert a date stored as text ('12 March 2025') into a proper date format...............................................................35
1. Trim extra spaces from customer names (TRIM, LTRIM, RTRIM)....................................................................................36
2. Convert case of product descriptions (UPPER, LOWER, INITCAP)...................................................................................37
3. Remove special characters from email addresses (SQL Server equivalent using TRANSLATE & STRING_AGG)..............37
4. Standardize phone numbers by removing dashes and spaces (REPLACE)......................................................................38
5. Extract numeric values from an alphanumeric string (Using PATINDEX & STUFF)..........................................................38
6. Format postal codes to match a specific pattern (SUBSTRING, RIGHT)...........................................................................40
7. Replace NULL values with default text (COALESCE)........................................................................................................41
8. Remove unwanted prefixes from company names (SUBSTRING, REPLACE)...................................................................41
9. Extract domain from email addresses (SUBSTRING, CHARINDEX)..................................................................................42
10. Split full names into first name and last name (PARSENAME - SQL Server workaround)..............................................42
11. Concatenate address fields into a single column (CONCAT).........................................................................................43
12. Replace incorrect spellings (CASE, REPLACE)................................................................................................................43
13. Extract file extensions from filenames (RIGHT, SUBSTRING)........................................................................................44
14. Ensure all URLs start with https:// (CASE, CONCAT).....................................................................................................44
15. Identify records with missing prefixes (LEFT, LIKE).......................................................................................................45
16. Convert abbreviations to full forms (CASE, REPLACE)...................................................................................................45
17. Remove duplicate spaces between words (REPLACE, LTRIM, RTRIM)..........................................................................46
18. Extract the first word from a description (LEFT, CHARINDEX).......................................................................................46
19. Validate email format (Using LIKE pattern matching)...................................................................................................46
20. Remove special characters from product names..........................................................................................................47
21. Join multiple fields to create a unique identifier (CONCAT)..........................................................................................48
22. Derive country from phone numbers (CASE, LEFT).......................................................................................................49
23. Extract year from transaction date (SUBSTRING, YEAR)...............................................................................................49
24. Pad account numbers to ensure fixed length (RIGHT, REPLICATE)...............................................................................49
25. Generate masked email addresses (LEFT, CONCAT).....................................................................................................50
26. Append missing domain names to email addresses (CASE, CONCAT)...........................................................................50
27. Derive product category from SKU codes (SUBSTRING)................................................................................................51
28. Standardize currency formats (FORMAT, REPLACE)......................................................................................................51
29. Extract hashtags from social media data (STRING_SPLIT).............................................................................................52
-- 30. Parse JSON strings to extract required fields (JSON_VALUE).....................................................................................52
31. Replace underscores with spaces in product descriptions (REPLACE)..........................................................................53
32. Convert encoded text to readable format (DECODE)....................................................................................................53
33. Extract the first N characters from product descriptions (LEFT)...................................................................................54
34. Format SSN for consistency (SUBSTRING, CONCAT).....................................................................................................54
35. Extract domain names from URLs (SUBSTRING, CHARINDEX).......................................................................................54
36. Identify invalid phone numbers (LEN, ISNUMERIC)......................................................................................................55
37. Ensure all SKUs are uppercase (UPPER)........................................................................................................................55
38. Extract usernames from email addresses (LEFT, CHARINDEX)......................................................................................56
39. Standardize street abbreviations (REPLACE).................................................................................................................56
40. Extract specific parts from a log message.....................................................................................................................57
41. Replace Multiple Spaces with a Single Space................................................................................................................58
42. Format Customer Names as Last, First (CONCAT).........................................................................................................59
43. Extract Last Four Digits of SSN (RIGHT).........................................................................................................................59
44. Parse XML Strings to Extract Specific Tags (XMLPARSE, XMLQUERY)...........................................................................60
-- 45. Transform camelCase to snake_case in SQL Server...................................................................................................60
46. Find Duplicate Entries by Normalizing Names (LOWER, TRIM).....................................................................................60
-- 47. Remove HTML Tags from a Text Field (REGEXP_REPLACE)........................................................................................61
-- 48. Identify Transactions with Invalid Characters (REGEXP_LIKE)...................................................................................61
49. Convert List of Comma-Separated Values into Rows (STRING_SPLIT)..........................................................................62
50. Extract Error Codes from Log Messages (SUBSTRING)..................................................................................................62
-- 51. Transform Dates from Text Format to Standard Format (TO_DATE).........................................................................63
52. Generate Initials from Customer Names (LEFT, UPPER)...............................................................................................63
53. Identify Misspelled Words in Text Fields (LIKE).............................................................................................................63
54. Ensure State Codes Are Two Letters Long (LENGTH)....................................................................................................64
55. Extract Serial Numbers from Mixed Data (REGEXP_SUBSTR).......................................................................................64
56. Remove Leading Zeros from Invoice Numbers (CAST)..................................................................................................64
57. Append Missing Country Codes to Phone Numbers (CONCAT)....................................................................................65
58. Convert Tab-Separated Data into Pipe-Separated Format (REPLACE)..........................................................................65
59. Extract Product Version Numbers from Descriptions (REGEXP_SUBSTR).....................................................................66
60. Replace Long Text Values with Abbreviations (CASE, REPLACE)...................................................................................66
61. Use Indexes on Transformed Columns for Faster Joins (SUBSTRING, LOWER).............................................................67
62. Identify and Remove Redundant Prefixes (LEFT, CASE)................................................................................................67
63. Detect and Remove Emojis from Text (REGEXP_REPLACE)...........................................................................................68
64. Extract Numerical Part from an Alphanumeric Field (REGEXP_SUBSTR).......................................................................68
65. Find Customers with Duplicate Email Addresses (LOWER, TRIM).................................................................................69
66. Replace Missing Area Codes with a Default Value (CASE).............................................................................................69
67. Identify Addresses Containing P.O. Boxes (LIKE)...........................................................................................................69
68. Extract Month Names from Date Strings (TO_CHAR)...................................................................................................70
69. Transform Mixed-Case Names to Title Case (INITCAP).................................................................................................70
70. Mask Credit Card Numbers Except Last 4 Digits (LPAD, RIGHT)....................................................................................70
71. Validate That a Column Contains Only Alphabets (REGEXP_LIKE)................................................................................71
72. Ensure Data Consistency by Removing Unwanted Words (REPLACE)...........................................................................71
73. Identify and Flag Duplicate SKUs (COUNT, GROUP BY).................................................................................................71
74. Extract Department Names from Email Aliases (SPLIT_PART)......................................................................................72
75. Detect Non-Standard Characters in Names (REGEXP_LIKE)..........................................................................................72
76. Format Timestamps for Consistency (TO_CHAR)..........................................................................................................73
77. Ensure Postal Codes Follow a Fixed Length (LPAD, RPAD)............................................................................................73
78. Convert Long Descriptions to Abbreviations (CASE, REPLACE).....................................................................................73
79. Remove Double Quotes from CSV Imports (REPLACE)..................................................................................................74
80. Validate If a Value Follows a Specific Regex Pattern (REGEXP_LIKE).............................................................................74
81. Generate User-Friendly URLs from Product Names (LOWER, REPLACE).......................................................................75
82. Extract City Names from Full Addresses (SUBSTRING, CHARINDEX).............................................................................75
83. Identify Transactions with Unusual Character Patterns (REGEXP_LIKE)........................................................................75
84. Format MAC Addresses for Consistency (REPLACE)......................................................................................................76
85. Normalize Multiple Date Formats to One Standard (CASE, TO_DATE).........................................................................76
86. Extract Age from Date of Birth (DATEDIFF)...................................................................................................................77
87. Transform Numeric Values to Words (CASE)................................................................................................................77
88. Validate Hexadecimal Color Codes (REGEXP_LIKE).......................................................................................................77
89. Extract the Company Name from Email Domains (SPLIT_PART)...................................................................................78
90. Identify Records with Excessive Character Length (LENGTH)........................................................................................78
91. Ensure Hashtags in Tweets Are Properly Formatted (LIKE)...........................................................................................79
92. Extract Words Before a Specific Delimiter (SPLIT_PART)..............................................................................................79
93. Remove Redundant Words from Customer Reviews (REPLACE)...................................................................................79
94. Identify Special Offer Codes from Product Descriptions (REGEXP_SUBSTR).................................................................80
95. Ensure All Addresses End with a Valid Suffix (LIKE).......................................................................................................80
96. Transform Unstructured Notes into Structured Fields (REGEXP_SUBSTR)....................................................................80
97. Standardize Formatting of Part Numbers (LPAD, RPAD)...............................................................................................81
98. Remove ASCII Control Characters from Data (REGEXP_REPLACE)................................................................................81
99. Identify and Correct Common OCR Errors (REPLACE)...................................................................................................81
100. Ensure Product Titles Have a Minimum Word Count (LENGTH, SPLIT_PART).............................................................82
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------

--ALL ABOUT STRINGS--

--------------------------------------------------------------------------------------------------
Extract the nth word of a string using STRING_SPLIT
DECLARE @String NVARCHAR(1000) = 'One Two Three Four Five Six Seven Eight Nine Ten Eleven Twelve Thirteen
Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen Twenty';
DECLARE @N INT = 20; -- Extract the nth word

SELECT value AS NthWord

FROM STRING_SPLIT(@String, ' ', 1)
WHERE ordinal = @N;

Explanation:
1. Variable Declaration
sql
Copy
DECLARE @String NVARCHAR(1000) = 'One Two Three Four Five Six Seven Eight Nine Ten Eleven Twelve Thirteen
Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen Twenty';
DECLARE @N INT = 1; -- Extract the nth word
@String: A variable of type NVARCHAR(1000) that holds a long string of words separated by spaces. In this case, it's a list
of numbers written as words from "One" to "Twenty."

@N: A variable of type INT that holds the position of the word you want to extract from the string. It's set to 1, meaning
the query will try to extract the first word from the string (i.e., "One").

2. STRING_SPLIT Function
sql
Copy
SELECT value AS NthWord
FROM STRING_SPLIT(@String, ' ', 1)
WHERE ordinal = @N;
STRING_SPLIT(@String, ' ', 1): The STRING_SPLIT function splits the string @String into multiple rows based on a
delimiter. In this case, the delimiter is a space (' '), which means it will split the string at each space, breaking it into
individual words.

The third argument, 1, indicates that it should return an ordinal value for each element. This means that it will not just
return the words, but also an ordinal (or index) representing the position of the word in the original string.

For example, the string "One Two Three" would be split into:

Row 1: Value = "One", Ordinal = 1

Row 2: Value = "Two", Ordinal = 2

Row 3: Value = "Three", Ordinal = 3

The ordinal is the position of each word in the original string (starting from 1).
3. Filtering with WHERE Clause
sql
Copy
WHERE ordinal = @N;
The WHERE clause is used to filter the result. It compares the ordinal (position) of each word in the split list with the
value of @N.

Since @N is set to 1, the query will only return the word at the first position (the word that corresponds to ordinal = 1).

4. Final Output
sql
Copy
SELECT value AS NthWord
The SELECT statement returns the value of the word at the specified position (in this case, the first word). The result will
be displayed as a column named NthWord.

Example Output:
For @N = 1, the output would be:

markdown
Copy
NthWord
--------
One
This query would return "One" because that is the first word in the string.

Key Points:
The STRING_SPLIT function breaks the input string into multiple rows, each containing a word and its corresponding
ordinal position.

The WHERE ordinal = @N filters the words based on the position specified in @N.

The query is designed to extract the Nth word from a space-separated list of words.

--========================================================

Create Table Words20 (Words Varchar(1000))

Insert into Words20 Values ('One Two Three Four Five Six Seven Eight Nine Ten Eleven Twelve Thirteen Fourteen Fifteen
Sixteen Seventeen Eighteen Nineteen Twenty')

DECLARE @n INT = 14; -- Change this to get a different nth word (e.g., 1 for "One", 5 for "Five", etc.)

WITH WordList AS (
SELECT value AS Word, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS WordPosition
FROM STRING_SPLIT((SELECT Words FROM Words20), ' ')
)
SELECT Word
FROM WordList
WHERE WordPosition = @n;

Explanation:

1. Creating the Table

sql
Copy
CREATE TABLE Words20 (Words VARCHAR(1000));
This command creates a new table named Words20 with a single column Words of type VARCHAR(1000). This column
will hold a string containing words separated by spaces.

sql
Copy
INSERT INTO Words20 VALUES ('One Two Three Four Five Six Seven Eight Nine Ten Eleven Twelve Thirteen Fourteen
Fifteen Sixteen Seventeen Eighteen Nineteen Twenty');
This inserts a single row into the Words20 table. The row contains a string of words ("One" to "Twenty") separated by
spaces.

2. Declaring a Variable for the nth Position

sql
Copy
DECLARE @n INT = 14; -- Change this to get a different nth word (e.g., 1 for "One", 5 for "Five", etc.)
Here, a variable @n is declared and assigned the value 14. This variable will be used to specify the position (or index) of
the word you want to extract from the string. For example, if @n is 14, you are asking to get the 14th word in the string,
which in this case is "Fourteen."

3. Common Table Expression (CTE) - WordList

sql
Copy
WITH WordList AS (
SELECT value AS Word, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS WordPosition
FROM STRING_SPLIT((SELECT Words FROM Words20), ' ')
)
WITH Clause (CTE): This part of the query defines a Common Table Expression (CTE) named WordList. A CTE is like a
temporary result set that can be referenced within the query.

STRING_SPLIT((SELECT Words FROM Words20), ' '):

The STRING_SPLIT function is used here to split the Words string (from the Words20 table) by spaces. This breaks the
long string into individual words.

Each word becomes a row in the result. For example, "One" would be one row, "Two" would be another row, and so on.

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS WordPosition:

The ROW_NUMBER() function generates a sequential number for each row in the result set, starting from 1 for the first
word. The ORDER BY (SELECT NULL) part means that the numbering doesn’t depend on any particular order of the
words because we just want to give each word a unique position number based on its appearance in the string.

This assigns a WordPosition to each word. For instance, "One" would have WordPosition = 1, "Two" would have
WordPosition = 2, and so on.

The result of this CTE (WordList) would look like this:

Word WordPosition
One 1
Two 2
Three 3
Four 4
Five 5
... ...
Twenty 20
4. Extracting the nth Word
sql
Copy
SELECT Word
FROM WordList
WHERE WordPosition = @n;
This SELECT statement fetches the Word from the WordList CTE where the WordPosition matches the value stored in
@n.

If @n = 14, it will return the word at position 14, which is "Fourteen."

Final Output:
If @n = 14, the query will return:

markdown
Copy
Word
-----
Fourteen
How to Use:
By changing the value of @n, you can retrieve any word from the string based on its position. For example:

If @n = 1, it will return "One".

If @n = 5, it will return "Five".

If @n = 20, it will return "Twenty".

Summary:
The query splits the string of words into individual rows.

Each word is assigned a unique position (WordPosition), using the ROW_NUMBER() function.

The main query then retrieves the word at the position specified by the @n variable.

This approach is very flexible and allows you to dynamically extract any word from the string by simply changing the
value of @n.

--------------------------------------------------------------------------------------------------

DECLARE @Sentence NVARCHAR(1000) = 'One Two Three Four Five Six Seven Eight Nine Ten Eleven Twelve Thirteen
Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen Twenty';
DECLARE @n INT = 19; -- Change this to the desired nth word (e.g., 1 for "One", 5 for "Five", etc.)

-- Declare variables
DECLARE @StartPos INT, @EndPos INT, @Word NVARCHAR(100);

-- Find the position of the nth word

SET @StartPos = 1; -- Start searching from the beginning of the string

-- Loop to find the position of the nth word by counting spaces

WHILE @n > 0
BEGIN
-- Find the next space after the current start position
SET @EndPos = CHARINDEX(' ', @Sentence, @StartPos);

-- If there is no space, set @EndPos to the end of the string

IF @EndPos = 0
BEGIN
SET @EndPos = LEN(@Sentence) + 1; -- Set to the end of the string
END

-- Decrease n (we found one word)

SET @n = @n - 1;

-- If we have found the nth word, extract it

IF @n = 0
BEGIN
SET @Word = SUBSTRING(@Sentence, @StartPos, @EndPos - @StartPos);
END

-- Update the start position for the next word search

SET @StartPos = @EndPos + 1;
END

-- Return the nth word

SELECT @Word AS NthWord;

--------------------------------------------------------------------------------------------------

--SQL Queries Based On ETL Transformations.

Need T-SQL Query, data wherever applicable, and a detailed explanation.

1 – 15 String Function-Based ETL Transformations
1. Extract the first word from a sentence in a column.
2. Extract the second word from a sentence in a column.
3. Extract the last word from a sentence in a column.
4. Replace all occurrences of "ABC" with "XYZ" in a column.
5. Convert all text to uppercase.
6. Convert all text to lowercase.
7. Capitalise the first letter of each word in a column.
8. Remove leading and trailing spaces from a column.
9. Remove all spaces from a column.
10. Concatenate two columns with a space in between.
11. Reverse the string in a column.
12. Extract only the numeric part from a string column.
13. Extract only the alphabetic part from a string column.
14. Find the position of the first occurrence of a substring.
15. Find the position of the last occurrence of a substring.
=============================================================
Here’s a detailed breakdown of T-SQL queries for each transformation, including sample data and explanations.
________________________________________
Sample Table and Data

CREATE TABLE A001 (

 ID INT IDENTITY PRIMARY KEY,
Sentence NVARCHAR(255)
);

INSERT INTO A001 (Sentence)

VALUES
('Hello world, this is a test.'),
('SQL Server is great!'),
('Extracting words with T-SQL.'),
('ABC should be replaced with XYZ ABC.'),
(' Leading and trailing spaces '),
('This123 is456 a789 mix987'),
('OnlyLettersHere'),
('Where is the first occurrence of is in this sentence?'),
('Find the last instance of ABC in this ABC example.');
________________________________________
1. Extract the first word from a sentence
SELECT
Sentence,
LEFT(Sentence, CHARINDEX(' ', Sentence + ' ') - 1) AS FirstWord
FROM A001;
Explanation
• CHARINDEX(' ', Sentence + ' ') finds the position of the first space.
• LEFT(Sentence, CHARINDEX(...) - 1) extracts everything before the first space.

________________________________________

Declare @String NVarchar(1000) = ('Find the last instance of ABC in this ABC example.');

SELECT
@String,
LEFT(@String, CHARINDEX(' ', @String + ' ') - 1) AS FirstWord
________________________________________
2. Extract the second word from a sentence
SELECT
Sentence,
CASE
WHEN CHARINDEX(' ', Sentence) = 0 THEN NULL
ELSE SUBSTRING(Sentence, CHARINDEX(' ', Sentence) + 1,
CHARINDEX(' ', Sentence + ' ', CHARINDEX(' ', Sentence) + 1) - CHARINDEX(' ', Sentence) - 1)
END AS SecondWord
FROM A001;
Explanation
• CHARINDEX(' ', Sentence) finds the first space.
• SUBSTRING(...) extracts from the first space to the second space.

________________________________________

Declare @String NVarchar(1000) = ('Find the last instance of ABC in this ABC example.');

SELECT
@String,
 CASE
WHEN CHARINDEX(' ', @String) = 0 THEN NULL
ELSE SUBSTRING(@String, CHARINDEX(' ', @String) + 1,
CHARINDEX(' ', @String + ' ', CHARINDEX(' ', @String) + 1) - CHARINDEX(' ', @String) - 1)
END AS SecondWord

________________________________________
3. Extract the last word from a sentence
SELECT
Sentence,
RIGHT(Sentence, CHARINDEX(' ', REVERSE(Sentence) + ' ') - 1) AS LastWord
FROM A001;
Explanation
• REVERSE(Sentence) reverses the string.
• CHARINDEX(' ', REVERSE(Sentence)) finds the first space from the end.
• RIGHT(Sentence, ...) extracts the last word.

________________________________________

Declare @String NVarchar(1000) = ('Find the last instance of ABC in this ABC example.');

SELECT
@String,
RIGHT(@String, CHARINDEX(' ', REVERSE(@String) + ' ') - 1) AS LastWord
________________________________________
4. Replace all occurrences of "ABC" with "XYZ"
SELECT
Sentence,
REPLACE(Sentence, 'ABC', 'XYZ') AS ReplacedText
FROM A001;
Explanation
• REPLACE(Sentence, 'ABC', 'XYZ') replaces every "ABC" with "XYZ".
________________________________________
5. Convert all text to uppercase
SELECT
Sentence,
UPPER(Sentence) AS UpperCaseText
FROM A001;
Explanation
• UPPER(Sentence) converts all letters to uppercase.
________________________________________
6. Convert all text to lowercase
SELECT
Sentence,
LOWER(Sentence) AS LowerCaseText
FROM A001;
Explanation
• LOWER(Sentence) converts all letters to lowercase.
________________________________________
7. Capitalize the first letter of each word
SELECT
Sentence,
 CONCAT(UPPER(LEFT(Sentence, 1)), LOWER(SUBSTRING(Sentence, 2, LEN(Sentence)))) AS CapitalizedText
FROM A001;
Explanation
• UPPER(LEFT(Sentence, 1)) capitalizes the first letter.
• LOWER(SUBSTRING(Sentence, 2, LEN(Sentence))) converts the rest to lowercase.
For full Title Case conversion, using a function would be required.
________________________________________
8. Remove leading and trailing spaces
SELECT
Sentence,
LTRIM(RTRIM(Sentence)) AS TrimmedText
FROM A001;
Explanation
• LTRIM() removes leading spaces.
• RTRIM() removes trailing spaces.
________________________________________
9. Remove all spaces
SELECT
Sentence,
REPLACE(Sentence, ' ', '') AS NoSpacesText
FROM A001;
Explanation
• REPLACE(Sentence, ' ', '') removes all spaces.
________________________________________
10. Concatenate two columns with a space
SELECT
CONCAT(Sentence, ' ', 'Another Column') AS ConcatenatedText
FROM A001;
Explanation
• CONCAT(Sentence, ' ', 'Another Column') combines two columns with a space.
________________________________________
11. Reverse the string
SELECT
Sentence,
REVERSE(Sentence) AS ReversedText
FROM A001;
Explanation
• REVERSE(Sentence) reverses the string.
________________________________________
12. Extract only numeric part from a string. Also letters only and
special characters only.
-- NUMERIC PART ONLY
WITH Tally AS (
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM [Link].spt_values
)
SELECT Sentence,
STRING_AGG(SUBSTRING(Sentence, N, 1), '') AS NumbersOnly
FROM A001
JOIN Tally ON N <= LEN(Sentence)
WHERE SUBSTRING(Sentence, N, 1) LIKE '[0-9]'
GROUP BY Sentence;

-- LETTERS ONLY
WITH Tally AS (
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM [Link].spt_values
)
SELECT Sentence,
STRING_AGG(SUBSTRING(Sentence, N, 1), '') AS LettersOnly
FROM A001
JOIN Tally ON N <= LEN(Sentence)
WHERE SUBSTRING(Sentence, N, 1) LIKE '[A-Za-z]'
GROUP BY Sentence;

-- SPECIAL CHARACTERS ONLY

WITH Tally AS (
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM [Link].spt_values
)
SELECT Sentence,
STRING_AGG(SUBSTRING(Sentence, N, 1), '') AS SpecialCharactersOnly
FROM A001
JOIN Tally ON N <= LEN(Sentence)
WHERE SUBSTRING(Sentence, N, 1) LIKE '[^a-zA-Z0-9]'
GROUP BY Sentence;

Explanation
-- NUMERIC PART ONLY
1. Using a Tally Table to Generate Numbers
WITH Tally AS (
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM [Link].spt_values
)
We use a Tally Table (Numbers Table) to generate a sequence of numbers (1, 2, 3, ...).
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N creates sequential numbers.
TOP (100) ensures that we generate at most 100 numbers. You can increase this limit if longer sentences need
processing.
[Link].spt_values is a system table containing many rows, which we use to generate numbers.

2. Joining the Tally Table with the A001 Table

JOIN Tally ON N <= LEN(Sentence)
This joins the A001 table with our Tally table.
N <= LEN(Sentence) ensures that we only generate numbers up to the length of the sentence.

3. Extracting Individual Characters

SUBSTRING(Sentence, N, 1)
This function extracts one character at a time from the Sentence column.
N represents the position of the character in the string.
For example, if Sentence = 'Order ID: 12345', we extract:
O, r, d, e, r, , I, D, :, , 1, 2, 3, 4, 5

4. Filtering Only Numeric Characters

WHERE SUBSTRING(Sentence, N, 1) LIKE '[0-9]'
This filters out non-numeric characters.
Only characters between '0' and '9' are kept.
For example, from 'Order ID: 12345', we keep:
1, 2, 3, 4, 5

5. Combining the Extracted Numbers

STRING_AGG(SUBSTRING(Sentence, N, 1), '') AS NumbersOnly
STRING_AGG() is an aggregation function that concatenates values into a single string.
STRING_AGG(SUBSTRING(Sentence, N, 1), '') joins the extracted numbers without any separators.
For example, if the extracted numbers are {1, 2, 3, 4, 5}, the output will be:
12345
Final Output Example
Sample Data
Sentence
Order ID: 12345
Invoice #98765XYZ
Query Output
Sentence NumbersOnly
Order ID: 1234512345
Invoice #98765XYZ 98765

-- LETTERS ONLY
1. Common Table Expression (CTE) - Tally
sql
Copy
WITH Tally AS (
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM [Link].spt_values
)
The WITH Tally AS part creates a Common Table Expression (CTE) named Tally.

The purpose of this CTE is to generate a series of numbers (essentially, a tally table) which can be used to extract
individual characters from a string.

The ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N generates a sequence of numbers starting from 1. The TOP
(100) limits this sequence to the first 100 rows. These numbers will represent positions in the Sentence string.

The FROM [Link].spt_values uses an existing system table to generate a large number of rows. The
[Link].spt_values table is often used to create row numbers for operations like this, but any table with enough
rows can be used for this purpose.

In this case, it generates up to 100 numbers that can be used to reference each character in a string by its position.

2. Main SELECT Statement

sql
Copy
SELECT Sentence,
STRING_AGG(SUBSTRING(Sentence, N, 1), '') AS LettersOnly
FROM A001
JOIN Tally ON N <= LEN(Sentence)
WHERE SUBSTRING(Sentence, N, 1) LIKE '[A-Za-z]'
GROUP BY Sentence;
This is the main query that processes the data and extracts only the letters from each Sentence:
a. FROM A001
This refers to the table A001, which presumably contains the column Sentence (the string from which we want to
extract letters).

b. JOIN Tally ON N <= LEN(Sentence)

This JOIN operation links the Tally CTE (which contains the sequence of numbers in the N column) to the A001 table.

N <= LEN(Sentence) ensures that we only extract characters from the Sentence where the position N is less than or
equal to the length of the sentence. This prevents trying to extract characters that do not exist in shorter sentences.

c. SUBSTRING(Sentence, N, 1)
The SUBSTRING function extracts a single character from the Sentence column at position N. The N value is taken from
the Tally table (i.e., the sequence of numbers), and the 1 indicates that only 1 character should be extracted.

d. WHERE SUBSTRING(Sentence, N, 1) LIKE '[A-Za-z]'

This WHERE clause filters the characters so that only alphabetic characters (letters) are included in the result.

LIKE '[A-Za-z]' is a pattern match that checks if the character extracted is a letter (either uppercase A-Z or lowercase a-z).

e. STRING_AGG(SUBSTRING(Sentence, N, 1), '') AS LettersOnly

STRING_AGG is an aggregate function that concatenates all the characters extracted by the SUBSTRING function into a
single string. The empty string '' is used as a separator, meaning no spaces or other characters will be added between
the concatenated letters.

This forms a new string containing only the letters from the original Sentence column, which is aliased as LettersOnly.

f. GROUP BY Sentence
The GROUP BY statement ensures that we group the result by each distinct sentence in the A001 table. For each
sentence, the STRING_AGG function will gather all the alphabetic characters into a single string.

Final Output
The query returns two columns:

Sentence: The original sentence from the A001 table.

LettersOnly: A string consisting of only the alphabetic characters from the original sentence, with all non-letter
characters removed.

Example:
Assume the A001 table has the following data:

Sentence
Hello123World!
Test456!abc@def
The result of the query would be:

Sentence LettersOnly
Hello123World!HelloWorld
Test456!abc@def Testabcdef
This is because all non-alphabet characters are removed in the LettersOnly column.

-- SPECIAL CHARACTERS ONLY

1️⃣ Create a Tally Table (CTE)
sql
Copy
Edit
WITH Tally AS (
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM [Link].spt_values
)
🔹 What is a Tally Table?
A tally table is a virtual table containing a sequence of numbers (N).

🔹 What does this do?

Generates numbers from 1 to 100 (assuming strings are within 100 characters).

Helps to iterate over each character in the Sentence column.

Uses ROW_NUMBER() to create a sequence without needing a looping construct.

2️⃣ Extract Each Character from the String

sql
Copy
Edit
SELECT Sentence,
STRING_AGG(SUBSTRING(Sentence, N, 1), '') AS SpecialCharactersOnly
🔹 SUBSTRING(Sentence, N, 1) extracts one character at position N for each row.
🔹 STRING_AGG(..., '') concatenates the extracted special characters into a single string for each Sentence.

3️⃣ Join the Tally Table with the Main Table

sql
Copy
Edit
FROM A001
JOIN Tally ON N <= LEN(Sentence)
🔹 What does this do?

Ensures the query processes only up to the length of each Sentence (to avoid out-of-range errors).

Joins every character position (N) with each row in A001.

4️⃣ Filter Only Special Characters

sql
Copy
Edit
WHERE SUBSTRING(Sentence, N, 1) LIKE '[^a-zA-Z0-9]'
🔹 LIKE '[^a-zA-Z0-9]'

^ (caret symbol) inside square brackets negates the pattern, meaning "exclude letters (a-z, A-Z) and numbers (0-9)".

--Only special characters (such as !@#$%^&*()_+-=[]{};:'",.<>?/) are extracted.

5️⃣ Group the Results by Sentence

sql
Copy
Edit
GROUP BY Sentence;
🔹 Groups the extracted special characters per original sentence, ensuring we get one row per input sentence.

Example Execution
Input Data (A001 Table)
Sentence
Hello@123!
Test#Data$%
SQL@Server2025?
Query Execution
It processes each sentence character by character and filters only special characters.

Output
Sentence SpecialCharactersOnly
Hello@123! @!
Test#Data$% #$%
SQL@Server2025? @?
Why Use This Approach?
✅ Scalable – Works on any string length by adjusting TOP (100).
✅ Efficient – Avoids loops by using a set-based approach.
✅ Flexible – Can be modified to extract digits, letters, or mixed patterns.
________________________________________
13. Extract only the alphabetic part from a string
WITH Tally AS (
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM [Link].spt_values
)
SELECT Sentence,
STRING_AGG(SUBSTRING(Sentence, N, 1), '') AS AlphabetsOnly
FROM A001
JOIN Tally ON N <= LEN(Sentence)
WHERE SUBSTRING(Sentence, N, 1) LIKE '[A-Za-z]'
GROUP BY Sentence;

Explanation
1. Generating a Sequence of Numbers Using a Tally Table
WITH Tally AS (
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
FROM [Link].spt_values
)
We create a Tally Table (Numbers Table) with numbers from 1 to 100 (adjustable as needed).
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) generates a sequential list of numbers.
TOP (100) limits the numbers to 100. Increase this if your strings are longer.
2. Extracting Each Character Using SUBSTRING()
SUBSTRING(Sentence, N, 1)
This extracts one character at a time from the Sentence column.
N represents the position of the character.
For example, if Sentence = 'Inv123@#$%', we extract:
I, n, v, 1, 2, 3, @, #, $, %
3. Filtering Only Alphabetic Characters
WHERE SUBSTRING(Sentence, N, 1) LIKE '[A-Za-z]'
This removes all non-alphabetic characters (0-9, spaces, special symbols).
Only letters A-Z (uppercase) and a-z (lowercase) are kept.
For example, from 'Inv123@#$%', we keep:
I, n, v
4. Reassembling the Filtered Characters
STRING_AGG(SUBSTRING(Sentence, N, 1), '') AS AlphabetsOnly
STRING_AGG() concatenates the extracted characters into a single string without separators.
If the extracted letters are {I, n, v}, the output will be:
Inv
Example Input and Expected Output
Sample Data
Sentence
Inv123@#$%
Report#98765XYZ
Query Output
Sentence AlphabetsOnly
Inv123@#$% Inv
Report#98765XYZ ReportXYZ
________________________________________
14. Find the position of the first occurrence of a substring
SELECT
Sentence,
CHARINDEX('is', Sentence) AS FirstOccurrence
FROM A001;
Explanation
• CHARINDEX('is', Sentence) finds the first position of "is".
________________________________________
15. Find the position of the last occurrence of a substring
SELECT
Sentence,
LEN(Sentence) - CHARINDEX('is', REVERSE(Sentence)) + 1 AS LastOccurrence
FROM A001;
Explanation
• REVERSE(Sentence) reverses the string.
• CHARINDEX('is', REVERSE(Sentence)) finds the first occurrence in reverse.
• LEN(Sentence) - ... + 1 gives the actual position.
=============================================================
Need T-SQL Query, data wherever applicable, and a detailed explanation.
16 – 25 String Function-Based ETL Transformations
16. Pad a number with leading zeroes to make it five digits long.
17. Extract characters after the first occurrence of a specific character.
18. Extract characters before the first occurrence of a specific character.
19. Extract characters between two specific characters.
20. Count the number of words in a column.
21. Replace multiple spaces with a single space.
22. Split a comma-separated string into multiple rows.
23. Convert a string to proper case (first letter uppercase, rest lowercase).
24. Generate a unique identifier by appending a timestamp to a string.
25. Extract the domain name from an email address.
=============================================================
Here’s a detailed breakdown of T-SQL queries for each transformation, including sample data and explanations.
________________________________________
Sample Table and Data

CREATE TABLE A016 (

ID INT IDENTITY PRIMARY KEY,
 Sentence NVARCHAR(255)
);

INSERT INTO A016 (Sentence)

VALUES
('123'), -- For padding numbers
('Hello:World:This:Is:SQL'),
('Extracting words with T-SQL.'),
('Find {middle} part.'),
('This is a test with multiple spaces.'),
('John,Doe,Developer,SQL Server'),
('johndoe@[Link]'),
('SomePrefix_2025'),
('This is a sample string.');
________________________________________
16. Pad a number with leading zeroes to make it five digits long
SELECT
Sentence,
RIGHT('00000' + Sentence, 5) AS PaddedNumber
FROM A016
WHERE Sentence LIKE '[0-9]%';

SELECT
Number,
RIGHT('000' + Number, 6) AS PaddedNumber
FROM Numbers
WHERE Number LIKE '[0-9]%';

Explanation
• '00000' + Sentence ensures the number has extra zeroes.
• RIGHT(..., 5) keeps only the rightmost five characters.
________________________________________
17. Extract characters after the first occurrence of a specific
character (:)
SELECT
Sentence,
SUBSTRING(Sentence, CHARINDEX(':', Sentence) + 1, LEN(Sentence)) AS AfterFirstColon
FROM A016
WHERE Sentence LIKE '%:%';
Explanation
• CHARINDEX(':', Sentence) finds the position of :.
• SUBSTRING(Sentence, ..., LEN(Sentence)) extracts everything after :.
________________________________________
18. Extract characters before the first occurrence of a specific
character (:)
SELECT
Sentence,
LEFT(Sentence, CHARINDEX(':', Sentence) - 1) AS BeforeFirstColon
FROM A016
WHERE Sentence LIKE '%:%';
Explanation
• CHARINDEX(':', Sentence) - 1 gets the position before :.
• LEFT(Sentence, ...) extracts characters before :.
________________________________________
19. Extract characters between two specific characters ({})
SELECT
Sentence,
SUBSTRING(
Sentence,
CHARINDEX('{', Sentence) + 1,
CHARINDEX('}', Sentence) - CHARINDEX('{', Sentence) - 1
) AS BetweenBraces
FROM A016
WHERE Sentence LIKE '%{%}%';
Explanation
• CHARINDEX('{', Sentence) and CHARINDEX('}', Sentence) find { and }.
• SUBSTRING(..., ..., ... ) extracts text between them.
________________________________________
20. Count the number of words in a column
SELECT
Sentence,
LEN(Sentence) - LEN(REPLACE(Sentence, ' ', '')) + 1 AS WordCount
FROM A016;
Explanation
• LEN(Sentence) gets total length.
• LEN(REPLACE(Sentence, ' ', '')) gets length without spaces.
• Difference + 1 gives word count.
________________________________________
21. Replace multiple spaces with a single space
SELECT
Sentence,
TRIM(
REPLACE(
REPLACE(
REPLACE(Sentence, ' ', ' '),
' ', ' '
),
' ', ' '
)
) AS SingleSpacedSentence
FROM A016;
Explanation
• Uses nested REPLACE() to iteratively reduce spaces to a single space.
• TRIM() removes leading/trailing spaces.
________________________________________
22. Split a comma-separated string into multiple rows
SELECT
Sentence,
value AS SplitValue
FROM A016
CROSS APPLY STRING_SPLIT(Sentence, ',')
WHERE Sentence LIKE '%,%';
Explanation
• STRING_SPLIT(Sentence, ',') splits the string into multiple rows.
• CROSS APPLY allows transforming each row.
________________________________________
23. Convert a string to proper case (first letter uppercase, rest
lowercase)
SELECT
Sentence,
CONCAT(UPPER(LEFT(Sentence, 1)), LOWER(SUBSTRING(Sentence, 2, LEN(Sentence)))) AS ProperCase
FROM A016;
Explanation
• UPPER(LEFT(Sentence, 1)) capitalizes the first letter.
• LOWER(SUBSTRING(Sentence, 2, LEN(Sentence))) converts the rest to lowercase.
For full title case, a function is needed.
________________________________________
24. Generate a unique identifier by appending a timestamp to a
string
SELECT
Sentence,
CONCAT(Sentence, '_', FORMAT(GETDATE(), 'yyyyMMddHHmmss')) AS UniqueIdentifier
FROM A016;
Explanation
• GETDATE() fetches the current timestamp.
• FORMAT(..., 'yyyyMMddHHmmss') formats it as YYYYMMDDHHMMSS.
• CONCAT(...) appends the timestamp.
________________________________________
25. Extract the domain name from an email address
SELECT
Sentence,
SUBSTRING(Sentence, CHARINDEX('@', Sentence) + 1, LEN(Sentence)) AS DomainName
FROM A016
WHERE Sentence LIKE '%@%';
Explanation
• CHARINDEX('@', Sentence) finds @.
• SUBSTRING(..., ..., LEN(Sentence)) extracts everything after @.
________________________________________
✅ Summary
# Transformation Query Used
16 Pad with leading zeroes RIGHT('00000' + Sentence, 5)
17 Extract after : SUBSTRING(... CHARINDEX(...) + 1 ...)
18 Extract before : LEFT(... CHARINDEX(...) - 1)
19 Extract between {} SUBSTRING(... CHARINDEX('{', ...) + 1 ...)
20 Count words LEN(Sentence) - LEN(REPLACE(...)) + 1
21 Replace multiple spaces REPLACE(REPLACE(...))
22 Split CSV into rows STRING_SPLIT(Sentence, ',')
23 Convert to proper case CONCAT(UPPER(LEFT(...)), LOWER(...))
24 Unique ID with timestamp CONCAT(Sentence, '_', FORMAT(GETDATE(), ...))
25 Extract email domain SUBSTRING(... CHARINDEX('@', ...) + 1 ...)

------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------

Need T-SQL Query, data wherever applicable, and a detailed explanation.

101 – 115 String Functions
101. Write a query to extract the last word from a string column.
102. Convert a string to proper case (capitalising the first letter of each word).
103. Replace multiple spaces in a string with a single space.
104. Remove leading and trailing special characters from a string.
105. Count the number of occurrences of a specific character in a string.
106. Split a comma-separated string into multiple rows.
107. Reverse a given string.
108. Extract the domain name from an email address.
109. Replace digits in a string with '*'.
110. Find the position of the second occurrence of a character in a string.
111. Convert a delimited string into an array and retrieve the third element.
112. Concatenate first name and last name with a space in between.
113. Remove all non-numeric characters from a string.
114. Check if a string contains only alphabets.
115. Extract the first 5 characters from a string.
=============================================================
Here are the T-SQL queries for each string transformation task, along with explanations and example data where
applicable.
________________________________________
101. Extract the last word from a string column
SELECT RIGHT(my_column, CHARINDEX(' ', REVERSE(my_column) + ' ') - 1) AS last_word
FROM my_table;
• Explanation:
o REVERSE(my_column): Reverses the string.
o CHARINDEX(' ', REVERSE(my_column) + ' '): Finds the first space in the reversed string (which corresponds to the
last space in the original).
o RIGHT(my_column, ...): Extracts the substring from the right up to the found position.
✅ Example:
my_column = 'Hello World' → last_word = 'World'
________________________________________
102. Convert a string to proper case (capitalize first letter of each
word)
SELECT STRING_AGG(UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2, LEN(value))), ' ')
FROM STRING_SPLIT('hello world example', ' ');
• Explanation:
o STRING_SPLIT(..., ' '): Splits the string into words.
o UPPER(LEFT(value,1)): Converts the first character of each word to uppercase.
o LOWER(SUBSTRING(value,2, LEN(value))): Converts the rest of the word to lowercase.
o STRING_AGG(..., ' '): Joins the words back together.
✅ Example:
'hello world example' → 'Hello World Example'
________________________________________
103. Replace multiple spaces in a string with a single space
SELECT TRIM(REPLACE(REPLACE(REPLACE(my_column, ' ', ' '), ' ', ' '), ' ', ' ')) AS cleaned_string
FROM my_table;
• Explanation:
o Uses multiple REPLACE functions to iteratively reduce spaces.
o TRIM(...) removes leading and trailing spaces.
✅ Example:
'Hello World SQL' → 'Hello World SQL'
________________________________________
104. Remove leading and trailing special characters from a string
SELECT TRIM('%@!#$' FROM my_column) AS cleaned_string
FROM my_table;
• Explanation:
o TRIM('%@!#$' FROM my_column): Removes the listed special characters from the beginning and end.
✅ Example:
'@@Hello!#$' → 'Hello'
________________________________________
105. Count the number of occurrences of a specific character in a
string
SELECT LEN(my_column) - LEN(REPLACE(my_column, 'a', '')) AS count_a
FROM my_table;
• Explanation:
o LEN(my_column) - LEN(REPLACE(my_column, 'a', '')): Counts occurrences of 'a' by removing it and checking
length difference.
✅ Example:
'banana' → 3
________________________________________
106. Split a comma-separated string into multiple rows
SELECT value AS split_value
FROM STRING_SPLIT('apple,banana,grape', ',');
• Explanation:
o STRING_SPLIT(..., ',') splits the string and returns each value as a separate row.
✅ Example:
'apple,banana,grape' →
nginx
CopyEdit
apple
banana
grape
________________________________________
107. Reverse a given string
SELECT REVERSE('Hello World') AS reversed_string;
• Explanation:
o REVERSE(...) reverses the string.
✅ Example:
'Hello' → 'olleH'
________________________________________
108. Extract the domain name from an email address
SELECT RIGHT(email, CHARINDEX('@', REVERSE(email)) - 1) AS domain
FROM my_table;
• Explanation:
o REVERSE(email): Reverses the email.
o CHARINDEX('@', REVERSE(email)): Finds the '@' position.
o RIGHT(email, ...): Extracts everything after '@'.
✅ Example:
'user@[Link]' → '[Link]'
________________________________________
109. Replace digits in a string with '*'
SELECT TRANSLATE(my_column, '0123456789', '**********') AS masked_string
FROM my_table;
• Explanation:
o TRANSLATE(...) replaces digits with '*'.
✅ Example:
'Hello123' → 'Hello***'
________________________________________
110. Find the position of the second occurrence of a character in a
string
SELECT CHARINDEX('o', my_column, CHARINDEX('o', my_column) + 1) AS second_occurrence
FROM my_table;
• Explanation:
o First CHARINDEX('o', my_column): Finds first occurrence.
o Second CHARINDEX('o', my_column, position + 1): Finds the next occurrence.
✅ Example:
'Hello World' → Position: 5
________________________________________
111. Convert a delimited string into an array and retrieve the third
element
SELECT value AS third_element
FROM STRING_SPLIT('one,two,three,four', ',')
ORDER BY (SELECT NULL)
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
• Explanation:
o Uses STRING_SPLIT(...) to split.
o OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY gets the third element.
✅ Example:
'one,two,three,four' → 'three'
________________________________________
112. Concatenate first name and last name with a space in
between
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM my_table;
• Explanation:
o CONCAT(...) joins strings with a space.
✅ Example:
'John', 'Doe' → 'John Doe'
________________________________________
113. Remove all non-numeric characters from a string
SELECT STRING_AGG(value, '')
FROM STRING_SPLIT(my_column, '')
WHERE value LIKE '[0-9]';
• Explanation:
o STRING_SPLIT(my_column, ''): Splits into characters.
o WHERE value LIKE '[0-9]': Filters only digits.
o STRING_AGG(...): Concatenates them back.
✅ Example:
'A1B2C3' → '123'
________________________________________
114. Check if a string contains only alphabets
SELECT CASE
WHEN my_column LIKE '%[^A-Za-z]%' THEN 'No'
ELSE 'Yes'
 END AS is_alpha
FROM my_table;
• Explanation:
o LIKE '%[^A-Za-z]%' checks for non-alphabet characters.
✅ Example:
'Hello' → 'Yes'
'Hello123' → 'No'
________________________________________
115. Extract the first 5 characters from a string
SELECT LEFT(my_column, 5) AS first_five_chars
FROM my_table;
• Explanation:
o LEFT(..., 5): Extracts the first five characters.
✅ Example:
'HelloWorld' → 'Hello'
Summary
# Transformation Query Used
101 Extract the last word from a string column SELECT RIGHT(my_column, CHARINDEX(' ',
REVERSE(my_column) + ' ') - 1) AS 0last_word11FROM my_table;
102 Convert a string to proper case SELECT STRING_AGG(UPPER(LEFT(value, 1)) + LOWER(SUBSTRING(value, 2,
LEN(value))), ' ') 0FROM11STRING_SPLIT('hello world example', ' ');
103 Replace multiple spaces with a single space SELECT TRIM(REPLACE(REPLACE(REPLACE(my_column, ' ', ' '), ' ',
' '), ' ', ' ')) 0AS11cleaned_string FROM my_table;
104 Remove leading/trailing special characters SELECT TRIM('%@!#$' FROM my_column) AS cleaned_string
FROM my_table;
105 Count occurrences of a specific character SELECT LEN(my_column) - LEN(REPLACE(my_column, 'a', '')) AS
count_a FROM my_table;
106 Split a comma-separated string into rows SELECT value AS split_value FROM
STRING_SPLIT('apple,banana,grape', ',');
107 Reverse a given string SELECT REVERSE('Hello World') AS reversed_string;
108 Extract domain name from email SELECT RIGHT(email, CHARINDEX('@', REVERSE(email)) - 1) AS domain
FROM my_table;
109 Replace digits in a string with '*'SELECT TRANSLATE(my_column, '0123456789', '**********') AS masked_string
FROM my_table;
10 Find second occurrence of a character SELECT CHARINDEX('o', my_column, CHARINDEX('o', my_column) + 1)
AS second_occurrence FROM my_table;
111 Convert a delimited string to an array (third element) SELECT value AS third_element FROM
STRING_SPLIT('one,two,three,four', 1',') ORDER BY (SELECT NULL) OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
112 Concatenate first and last name SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM my_table;
113 Remove all non-numeric characters SELECT STRING_AGG(value, '') FROM STRING_SPLIT(my_column, '')
WHERE value LIKE '[0-9]';
114 Check if a string contains only alphabets SELECT CASE WHEN my_column LIKE '%[^A-Za-z]%' THEN 'No' ELSE 'Yes'
END AS is_alpha 1FROM my_table;
115 Extract first 5 characters from a string SELECT LEFT(my_column, 5) AS first_five_chars FROM my_table;

=============================================================
Need T-SQL Query, data wherever applicable, and a detailed explanation.
116 – 125 String Functions
116. Convert a numeric column to a zero-padded string of length 10.
117. Find the difference in length between a string before and after removing spaces.
118. Convert a list of names into a single comma-separated string.
119. Extract initials from a full name.
120. Swap the first and last words of a sentence.
121. Mask all characters in a string except the last 4.
122. Find the most common character in a string.
123. Convert a sentence into an acronym.
124. Extract the first 3 words from a sentence.
125. Trim spaces only from the left side of a string.
=============================================================
Here are the T-SQL queries, sample data, and detailed explanations for each string function-based ETL transformation.
________________________________________
116. Convert a numeric column to a zero-padded string of length
10
SELECT RIGHT('0000000000' + CAST(employee_id AS VARCHAR(10)), 10) AS zero_padded_id
FROM employees;
• Explanation:
o Converts employee_id to a string.
o Prepends 0000000000 and extracts the last 10 characters to ensure proper padding.
• Sample Data & Output:
employee_id zero_padded_id
1234 0000001234
78 0000000078
________________________________________
117. Find the difference in length between a string before and
after removing spaces
SELECT name,
LEN(name) - LEN(REPLACE(name, ' ', '')) AS space_difference
FROM employees;
• Explanation:
o LEN(name): Gets original length.
o LEN(REPLACE(name, ' ', '')): Gets length after removing spaces.
o The difference gives the number of spaces.
• Output:
name space_difference
John Doe 1
Alice B. King 2
________________________________________
118. Convert a list of names into a single comma-separated string
SELECT STRING_AGG(name, ', ') AS name_list
FROM employees;
• Explanation:
o STRING_AGG() concatenates values with a separator.
• Output: "John Doe, Alice King, Bob Smith"
________________________________________
119. Extract initials from a full name
SELECT name,
LEFT(name, 1) + '.' + LEFT(PARSENAME(REPLACE(name, ' ', '.'), 1), 1) + '.' AS initials
FROM employees;
• Explanation:
o Extracts the first letter of the first and last name.
o Uses PARSENAME() to handle spaces (assuming two-word names).
• Output:
name initials
John Doe J.D.
Alice King A.K.
________________________________________
120. Swap the first and last words of a sentence
SELECT sentence,
RIGHT(sentence, CHARINDEX(' ', REVERSE(sentence)) - 1)
+''+
LEFT(sentence, CHARINDEX(' ', sentence) - 1) AS swapped_sentence
FROM sentences;
• Explanation:
o Extracts the first and last words.
o Swaps them using LEFT(), RIGHT(), and CHARINDEX().
• Output:
sentence swapped_sentence
Hello world SQLSQL Hello
________________________________________
121. Mask all characters in a string except the last 4
SELECT phone_number,
REPLICATE('*', LEN(phone_number) - 4) + RIGHT(phone_number, 4) AS masked_number
FROM customers;
• Explanation:
o Uses REPLICATE('*', LEN(phone_number) - 4) to generate asterisks.
o Appends the last 4 characters from RIGHT(phone_number, 4).
• Output:
phone_number masked_number
1234567890 ******7890
________________________________________
122. Find the most common character in a string
WITH CharCount AS (
SELECT SUBSTRING(string_value, number, 1) AS char_value, COUNT(*) AS freq
FROM [Link].spt_values
CROSS APPLY (SELECT 'banana' AS string_value) AS T
WHERE number BETWEEN 1 AND LEN(string_value)
GROUP BY SUBSTRING(string_value, number, 1)
)
SELECT TOP 1 char_value FROM CharCount ORDER BY freq DESC;
• Explanation:
o Breaks the string into individual characters.
o Counts occurrences and selects the most frequent.
• Output: 'a'
________________________________________
123. Convert a sentence into an acronym
SELECT sentence,
STRING_AGG(LEFT(value, 1), '') AS acronym
FROM STRING_SPLIT('Structured Query Language', ' ');
• Explanation:
o Splits sentence by spaces.
o Takes the first letter of each word and concatenates.
• Output: "SQL"
________________________________________
124. Extract the first 3 words from a sentence
SELECT sentence,
STRING_AGG(value, ' ') AS first_3_words
FROM (SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
FROM STRING_SPLIT('The quick brown fox jumps', ' '))
WHERE rn <= 3;
• Explanation:
o Splits sentence into words.
o Uses ROW_NUMBER() to limit extraction.
• Output: "The quick brown"
________________________________________
125. Trim spaces only from the left side of a string
SELECT LTRIM(' Hello World ') AS trimmed_string;
• Explanation:
o LTRIM() removes leading spaces.
• Output:
trimmed_string
"Hello World "
________________________________________
Summary Table
# Transformation Query Used
116 Convert a numeric column to a zero-padded string SELECT RIGHT('0000000000' + CAST(employee_id AS
VARCHAR(10)), 10) FROM employees;
117 Find difference in length after removing spaces SELECT LEN(name) - LEN(REPLACE(name, ' ', '')) FROM
employees;
118 Convert a list of names into a comma-separated string SELECT STRING_AGG(name, ', ') FROM employees;
119 Extract initials from a full name SELECT LEFT(name, 1) + '.' + LEFT(PARSENAME(REPLACE(name, ' ', '.'), 1), 1) + '.'
FROM employees;
120 Swap the first and last words of a sentence SELECT RIGHT(sentence, CHARINDEX(' ', REVERSE(sentence)) -
1) + ' ' + LEFT(sentence,1 CHARINDEX(' ', sentence) - 1) FROM sentences;
121 Mask all characters except the last 4 SELECT REPLICATE('*', LEN(phone_number) - 4) +
RIGHT(phone_number, 4) FROM customers;
122 Find the most common character in a string SELECT TOP 1 char_value FROM CharCount ORDER BY freq
DESC;
123 Convert a sentence into an acronym SELECT STRING_AGG(LEFT(value, 1), '') FROM STRING_SPLIT('Structured
Query Language', ' ');
124 Extract the first 3 words from a sentence SELECT STRING_AGG(value, ' ') FROM (SELECT value,
ROW_NUMBER() OVER (ORDER BY 1(SELECT NULL)) AS rn FROM STRING_SPLIT('The quick brown fox jumps', ' ')) WHERE
rn <= 3;
125 Trim spaces only from the left side of a string SELECT LTRIM(' Hello World ') AS trimmed_string;
------------------------------------------------------------------------------------------------------
=============================================================
Need T-SQL Query, data wherever applicable, and a detailed explanation.
201 – 215 String Functions
201. Extract the last two words from a string column.
202. Remove duplicate characters from a string.
203. Replace all vowels in a string with '#'.
204. Convert a string into a title case format.
205. Find the third occurrence of a specific character in a string.
206. Swap the first and last characters of a string.
207. Reverse words in a string while maintaining their positions.
208. Extract the country code from a phone number stored in the format '+91-9876543210'.
209. Convert a camelCase string into a space-separated string.
210. Remove the first occurrence of a specific character from a string.
211. Extract all numbers from an alphanumeric string.
212. Identify and extract duplicate words from a string.
213. Find the first non-repeating character in a string.
214. Convert a tab-separated string into a comma-separated format.
215. Remove special characters from a string except spaces and hyphens.
=============================================================
Here are the T-SQL queries, sample data, detailed explanations, and a summary table for the requested String Functions.
________________________________________
201. Extract the last two words from a string column
SELECT column_name,
RIGHT(column_name, CHARINDEX(' ', REVERSE(column_name), CHARINDEX(' ', REVERSE(column_name)) + 1) - 1) AS
last_two_words
FROM table_name;
• Explanation:
o Uses REVERSE() and CHARINDEX() to locate the last two spaces.
o Extracts the last two words.
• Output:
column_name last_two_words
Hello world SQLworld SQL
________________________________________
202. Remove duplicate characters from a string
WITH CTE AS (
SELECT column_name,
STRING_AGG(DISTINCT value, '') WITHIN GROUP (ORDER BY value) AS unique_chars
FROM STRING_SPLIT(column_name, '')
GROUP BY column_name
)
SELECT * FROM CTE;
• Explanation:
o Splits the string into individual characters, removes duplicates using DISTINCT, and concatenates them back.
• Output:
column_name unique_chars
banana ban
________________________________________
203. Replace all vowels in a string with '#'
SELECT column_name,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(column_name, 'a', '#'), 'e', '#'), 'i', '#'), 'o', '#'), 'u', '#') AS
replaced_string
FROM table_name;
• Explanation:
o Uses nested REPLACE() functions to replace vowels (a, e, i, o, u) with #.
• Output:
column_name replaced_string
hello h#ll#
________________________________________
204. Convert a string into a title case format
SELECT column_name,
CONCAT(UPPER(LEFT(column_name, 1)), LOWER(SUBSTRING(column_name, 2, LEN(column_name) - 1))) AS
title_case
FROM table_name;
• Explanation:
o Uses UPPER() for the first letter and LOWER() for the rest.
• Output:
column_name title_case
hello Hello
________________________________________
205. Find the third occurrence of a specific character in a string
SELECT column_name,
CHARINDEX('a', column_name, CHARINDEX('a', column_name, CHARINDEX('a', column_name) + 1) + 1) AS
third_occurrence
FROM table_name;
• Explanation:
o Uses CHARINDEX() three times to find the third occurrence of 'a'.
• Output:
column_name third_occurrence
banana 5
________________________________________
206. Swap the first and last characters of a string
SELECT column_name,
CONCAT(RIGHT(column_name, 1), SUBSTRING(column_name, 2, LEN(column_name) - 2), LEFT(column_name, 1)) AS
swapped_string
FROM table_name;
• Explanation:
o Extracts the first and last character and swaps them.
• Output:
column_name swapped_string
hello oellh
________________________________________
207. Reverse words in a string while maintaining their positions
SELECT column_name,
STRING_AGG(REVERSE(value), ' ')
FROM STRING_SPLIT(column_name, ' ')
GROUP BY column_name;
• Explanation:
o Uses STRING_SPLIT() to break words, REVERSE() to reverse each word, and STRING_AGG() to recombine them.
• Output:
column_name reversed_words
Hello SQL olleH LQS
________________________________________
208. Extract the country code from a phone number stored in
'+91-9876543210'
SELECT column_name,
LEFT(column_name, CHARINDEX('-', column_name) - 1) AS country_code
FROM table_name;
• Explanation:
o Extracts the portion before - to get the country code.
• Output:
column_name country_code
+91-9876543210 +91
________________________________________
209. Convert a camelCase string into a space-separated string
SELECT column_name,
REPLACE(column_name, '[A-Z]', ' \0') AS spaced_string
FROM table_name;
• Explanation:
o Uses REPLACE() to insert a space before capital letters.
• Output:
column_name spaced_string
camelCase camel Case
________________________________________
210. Remove the first occurrence of a specific character from a
string
SELECT column_name,
STUFF(column_name, CHARINDEX('a', column_name), 1, '') AS modified_string
FROM table_name;
• Explanation:
o Uses STUFF() to remove the first occurrence of a.
• Output:
column_name modified_string
banana bnana
________________________________________
211. Extract all numbers from an alphanumeric string
SELECT column_name,
STRING_AGG(value, '')
FROM STRING_SPLIT(column_name, '')
WHERE value LIKE '[0-9]'
GROUP BY column_name;
• Explanation:
o Uses STRING_SPLIT() and filters numeric characters.
• Output:
column_name extracted_numbers
abc123xyz 123
________________________________________
212. Identify and extract duplicate words from a string
WITH CTE AS (
SELECT value, COUNT(*) AS cnt
FROM STRING_SPLIT(column_name, ' ')
GROUP BY value
HAVING COUNT(*) > 1
)
SELECT STRING_AGG(value, ' ') AS duplicate_words FROM CTE;
• Explanation:
o Counts word occurrences, extracts those appearing more than once.
• Output:
column_name duplicate_words
hello hello world hello
________________________________________
213. Find the first non-repeating character in a string
WITH CTE AS (
SELECT value, COUNT(*) AS cnt
FROM STRING_SPLIT(column_name, '')
GROUP BY value
)
SELECT TOP 1 value FROM CTE WHERE cnt = 1;
• Explanation:
o Finds the first character appearing only once.
• Output:
column_name first_non_repeating
hello h
________________________________________
214. Convert a tab-separated string into a comma-separated
format
SELECT REPLACE(column_name, CHAR(9), ',') AS converted_string
FROM table_name;
• Explanation:
o Replaces tab characters (CHAR(9)) with commas.
• Output:
column_name converted_string
John Doe SQL John,Doe,SQL
________________________________________
215. Remove special characters from a string except spaces and
hyphens
SELECT column_name,
REPLACE(column_name, '[^A-Za-z0-9 -]', '') AS cleaned_string
FROM table_name;
• Explanation:
o Uses REPLACE() with a regex pattern to keep letters, numbers, spaces, and hyphens.
• Output:
column_name cleaned_string
hello@world! hello world
________________________________________
Summary Table
# Transformation Query Used / Function
201 Extract the last two words from a string column RIGHT(), REVERSE(), CHARINDEX()
202 Remove duplicate characters from a string STRING_SPLIT(), DISTINCT, STRING_AGG()
203 Replace all vowels in a string with # REPLACE() (nested)
204 Convert a string into title case format UPPER(), LOWER(), CONCAT()
205 Find the third occurrence of a specific character in a string CHARINDEX() (nested)
206 Swap the first and last characters of a string LEFT(), RIGHT(), SUBSTRING(), CONCAT()
207 Reverse words in a string while maintaining their positions STRING_SPLIT(), REVERSE(), STRING_AGG()
208 Extract the country code from a phone number (+91-9876543210) LEFT(), CHARINDEX()
209 Convert a camelCase string into a space-separated string REPLACE() with regex
210 Remove the first occurrence of a specific character STUFF(), CHARINDEX()
211 Extract all numbers from an alphanumeric stringSTRING_SPLIT(), LIKE '[0-9]', STRING_AGG()
212 Identify and extract duplicate words from a string STRING_SPLIT(), COUNT(), HAVING COUNT(*) > 1
213 Find the first non-repeating character in a string STRING_SPLIT(), COUNT(), HAVING COUNT(*) = 1
214 Convert a tab-separated string into a comma-separated format REPLACE(column_name, CHAR(9), ',')
215 Remove special characters except spaces and hyphens REPLACE() with regex [^A-Za-z0-9 -]
________________________________________
Key Observations:
• String Splitting & Aggregation: STRING_SPLIT() and STRING_AGG() are frequently used to manipulate words and
characters in a string.
• Position-Based Extraction: CHARINDEX(), LEFT(), RIGHT(), and SUBSTRING() help extract portions of text.
• Pattern Matching & Replacement: REPLACE(), LIKE, and regex-based replacements are useful for character
substitution.
• Ordering & Grouping: COUNT(), HAVING, and DISTINCT are effective in identifying duplicates and unique values.
=============================================================
Need T-SQL Query, data wherever applicable, and a detailed explanation.
216 – 225 String Functions
216. Convert a sentence into Pig Latin format.
217. Find the longest word in a sentence.
218. Replace the last occurrence of a character in a string.
219. Find all strings that contain only consonants.
220. Convert spaces in a string to underscores.
221. Extract text between two specific words in a string.
222. Check if a string contains only uppercase letters.
223. Find the frequency of each character in a string.
224. Identify palindromic words within a string.
225. Convert a date stored as text ('12 March 2025') into a proper date format.
=============================================================
Here’s a detailed explanation, T-SQL queries, and a summary table for the requested String Function-Based ETL
Transformations 🚀
________________________________________
T-SQL Queries and Explanations
216. Convert a sentence into Pig Latin format
SELECT STRING_AGG(
CASE
WHEN CHARINDEX('a', LOWER(value)) = 1 OR CHARINDEX('e', LOWER(value)) = 1
OR CHARINDEX('i', LOWER(value)) = 1 OR CHARINDEX('o', LOWER(value)) = 1
OR CHARINDEX('u', LOWER(value)) = 1
THEN value + 'way'
ELSE STUFF(value, 1, 1, '') + LEFT(value, 1) + 'ay'
END, ' ') AS PigLatin
FROM STRING_SPLIT('hello world this is sql', ' ');
Explanation:
• Uses STRING_SPLIT() to break the sentence into words.
• Moves the first letter of each word to the end and appends "ay" unless it starts with a vowel, in which case
"way" is added.
• STRING_AGG() reconstructs the transformed sentence.
________________________________________
217. Find the longest word in a sentence
SELECT TOP 1 value AS LongestWord
FROM STRING_SPLIT('Find the longest word in this sentence', ' ')
ORDER BY LEN(value) DESC;
Explanation:
• STRING_SPLIT() separates words.
• ORDER BY LEN(value) DESC sorts by length.
• TOP 1 fetches the longest word.
________________________________________
218. Replace the last occurrence of a character in a string
DECLARE @str NVARCHAR(100) = 'banana';
DECLARE @char NVARCHAR(1) = 'a';
SELECT LEFT(@str, LEN(@str) - CHARINDEX(@char, REVERSE(@str))) +
STUFF(REVERSE(LEFT(REVERSE(@str), CHARINDEX(@char, REVERSE(@str)))), 1, 1, '#') AS ModifiedString;
Explanation:
• REVERSE(@str) helps find the last occurrence of a character.
• STUFF() replaces it with #.
________________________________________
219. Find all strings that contain only consonants
SELECT name
FROM Customers
WHERE name NOT LIKE '%[aeiouAEIOU]%';
Explanation:
• Uses LIKE with a negated character set (%[aeiouAEIOU]%) to exclude vowels.
________________________________________
220. Convert spaces in a string to underscores
SELECT REPLACE('Hello World SQL Query', ' ', '_') AS UnderscoreString;
Explanation:
• REPLACE() swaps spaces with underscores.
________________________________________
221. Extract text between two specific words in a string
DECLARE @text NVARCHAR(100) = 'The quick brown fox jumps over the lazy dog';
DECLARE @startWord NVARCHAR(20) = 'brown';
DECLARE @endWord NVARCHAR(20) = 'lazy';
SELECT SUBSTRING(@text, CHARINDEX(@startWord, @text) + LEN(@startWord) + 1,
CHARINDEX(@endWord, @text) - CHARINDEX(@startWord, @text) - LEN(@startWord) - 1) AS ExtractedText;
Explanation:
• CHARINDEX() finds positions of @startWord and @endWord.
• SUBSTRING() extracts the text in between.
________________________________________
222. Check if a string contains only uppercase letters
SELECT CASE
WHEN column_name COLLATE Latin1_General_BIN LIKE '%[^A-Z]%' THEN 'No'
ELSE 'Yes'
END AS IsUppercase
FROM TableName;
Explanation:
• COLLATE Latin1_General_BIN ensures case sensitivity.
• LIKE '%[^A-Z]%' checks for non-uppercase characters.
________________________________________
223. Find the frequency of each character in a string
WITH CharCounts AS (
SELECT value AS Character, COUNT(*) AS Frequency
FROM STRING_SPLIT('banana', '')
GROUP BY value
)
SELECT * FROM CharCounts;
Explanation:
• STRING_SPLIT() breaks the string into characters.
• GROUP BY + COUNT() finds frequency.
________________________________________
224. Identify palindromic words within a string
SELECT value AS PalindromeWord
FROM STRING_SPLIT('madam level racecar hello world', ' ')
WHERE value = REVERSE(value);
Explanation:
• STRING_SPLIT() extracts words.
• REVERSE(value) finds palindromes.
________________________________________
225. Convert a date stored as text ('12 March 2025') into a proper
date format
SELECT CONVERT(DATE, '12 March 2025', 113) AS ProperDate;
Explanation:
--• CONVERT(DATE, string, 113) changes a textual date into SQL's date format.
________________________________________
Summary Table
# Transformation Query Used / Function
216 Convert a sentence into Pig Latin formatSTRING_SPLIT(), STRING_AGG(), STUFF()
217 Find the longest word in a sentence STRING_SPLIT(), ORDER BY LEN()
218 Replace the last occurrence of a character CHARINDEX(), REVERSE(), STUFF()
219 Find all strings with only consonants LIKE '%[^aeiouAEIOU]%'
220 Convert spaces in a string to underscores REPLACE()
221 Extract text between two specific words CHARINDEX(), SUBSTRING()
222 Check if a string contains only uppercase COLLATE Latin1_General_BIN, LIKE
223 Find the frequency of each character STRING_SPLIT(), COUNT(), GROUP BY
224 Identify palindromic words STRING_SPLIT(), REVERSE()
225 Convert text-based date into proper format CONVERT(DATE, string, format)
------------------------------------------------------------------------------------------------------

100 real-time scenarios in ETL transformations where string functions are used.
1-20: Data Cleaning & Standardization
Need examples with sample data and T-SQL query, output, and explanation, for each query separately.
1. Trim extra spaces from customer names (TRIM, LTRIM, RTRIM).
2. Convert case of product descriptions (UPPER, LOWER, INITCAP).
3. Remove special characters from email addresses.
4. Standardize phone numbers by removing dashes and spaces (REPLACE).
5. Extract numeric values from an alphanumeric string (REGEXP_SUBSTR).
6. Format postal codes to match a specific pattern (SUBSTRING, LPAD).
7. Replace NULL values with default text (COALESCE).
8. Remove unwanted prefixes from company names (SUBSTRING, REPLACE).
9. Extract domain from email addresses (SUBSTRING, CHARINDEX).
10. Split full names into first name and last name (SPLIT_PART).
11. Concatenate address fields into a single column (CONCAT).
12. Replace incorrect spellings (CASE, REPLACE).
13. Extract file extensions from filenames (RIGHT, SUBSTRING).
14. Ensure all URLs start with https:// (CASE, CONCAT).
15. Identify records with missing prefixes (LEFT, LIKE).
16. Convert abbreviations to full forms (CASE, REPLACE).
17. Remove duplicate spaces between words.
18. Extract the first word from a description (SPLIT_PART).
19. Validate email format (REGEXP_LIKE).
20. Remove special characters from product names.
________________________________________
1. Trim extra spaces from customer names (TRIM, LTRIM, RTRIM)
Sample Data
CREATE TABLE Trans001 (CustomerID INT, CustomerName VARCHAR(50));

INSERT INTO Trans001 VALUES (1, ' John Doe '), (2, ' Jane Smith'), (3, 'Mark Taylor ');

Select * from Trans001

T-SQL Query
SELECT
CustomerID,
CustomerName AS OriginalName,
 TRIM(CustomerName) AS TrimmedName,
LTRIM(CustomerName) AS LeftTrimmedName,
RTRIM(CustomerName) AS RightTrimmedName
FROM Trans001;
Output
CustomerID OriginalName TrimmedName LeftTrimmedName RightTrimmedName
1 " John Doe " "John Doe" "John Doe " " John Doe"
2 " Jane Smith" "Jane Smith" "Jane Smith" " Jane Smith"
3 "Mark Taylor " "Mark Taylor" "Mark Taylor " "Mark Taylor"
Explanation
TRIM(CustomerName): Removes leading and trailing spaces.
LTRIM(CustomerName): Removes only leading spaces.
RTRIM(CustomerName): Removes only trailing spaces.
________________________________________
2. Convert case of product descriptions (UPPER, LOWER, INITCAP)
Sample Data
CREATE TABLE Trans002 (ProductID INT, ProductDescription VARCHAR(100));
INSERT INTO Trans002 VALUES (1, 'red Apple'), (2, 'BLUEBERRY'), (3, 'yELlOW banana');
Need initcap in SQL Server
T-SQL Query

-- Upper / Lower
SELECT
ProductID,
ProductDescription AS OriginalDescription,
UPPER(ProductDescription) AS UpperCase,
LOWER(ProductDescription) AS LowerCase
FROM Trans002;

-- Initcap
SELECT
ProductID,
-- Using a combination of UPPER, LOWER, and STUFF functions to capitalize the first letter of each word
UPPER(SUBSTRING(ProductDescription, 1, 1)) + LOWER(SUBSTRING(ProductDescription, 2, LEN(ProductDescription)))
AS ProductDescription_InitCap
FROM Trans002;

Output
ProductID OriginalDescription UpperCase LowerCase
1 red Apple RED APPLE red apple
2 BLUEBERRY BLUEBERRY blueberry
3 yELlOW banana YELLOW BANANA yellow banana
Explanation
UPPER(ProductDescription): Converts text to uppercase.
LOWER(ProductDescription): Converts text to lowercase.
SQL Server does not have INITCAP, so INITCAP must be implemented via functions or a workaround.
________________________________________
3. Remove special characters from email addresses (SQL Server
equivalent using TRANSLATE & STRING_AGG)
Sample Data
CREATE TABLE Trans003 (EmailID INT, EmailAddress VARCHAR(100));
INSERT INTO Trans003 VALUES
(1, '[Link]@exa_mple.com'),
(2, 'jane-smith@[Link]'),
(3, 'mark+taylor@[Link]');

T-SQL Query
-- Remove special characters except @ and . from email addresses
SELECT
EmailID,
-- Remove special characters using REPLACE for unwanted characters
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(EmailAddress, '.', ''), '-', ''), '_', ''), '+', ''), ' ', '') AS CleanedEmailAddress
FROM Trans003;

Output
EmailID OriginalEmail CleanedEmail
1 [Link]@exa_mple.com johndoe@[Link]
2 jane-smith@[Link] janesmith@[Link]
3 mark+taylor@[Link] marktaylor@[Link]
Explanation
TRANSLATE(EmailAddress, '_-+', ''): Removes _, -, and + from email addresses.
________________________________________
4. Standardize phone numbers by removing dashes and spaces
(REPLACE)
Sample Data
CREATE TABLE Trans004 (PhoneID INT, PhoneNumber VARCHAR(20));
INSERT INTO Trans004 VALUES
(1, '123-456-7890'),
(2, '(123) 456 7890'),
(3, '123 456-7890');
T-SQL Query
SELECT
PhoneID,
PhoneNumber AS OriginalNumber,
REPLACE(REPLACE(REPLACE(PhoneNumber, '-', ''), ' ', ''), '(', '') AS StandardizedNumber
FROM Trans004;
Output
PhoneID OriginalNumberStandardizedNumber
1 123-456-7890 1234567890
2 (123) 456 7890 1234567890
3 123 456-7890 1234567890
Explanation
REPLACE(PhoneNumber, '-', ''): Removes dashes.
REPLACE(..., ' ', ''): Removes spaces.
REPLACE(..., '(', ''): Removes parentheses.
________________________________________
5. Extract numeric values from an alphanumeric string (Using
PATINDEX & STUFF)
Sample Data
CREATE TABLE Trans005 (ProductID INT, ProductCode VARCHAR(20));
INSERT INTO Trans005 VALUES
(1, 'ABC123XYZ'),
(2, '45GH67JK'),
(3, 'PQR890MNO');
T-SQL Query
SELECT
ProductID,
ProductCode AS OriginalCode,
STRING_AGG(SUBSTRING(ProductCode, NumberPos, 1), '') AS ExtractedNumbers
FROM Trans005
CROSS APPLY (
SELECT TOP (LEN(ProductCode)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NumberPos
FROM [Link].spt_values
) AS Tally
WHERE SUBSTRING(ProductCode, NumberPos, 1) LIKE '[0-9]'
GROUP BY ProductID, ProductCode;

Output
ProductID OriginalCode ExtractedNumbers
1 ABC123XYZ 123XYZ
2 45GH67JK 45GH67JK
3 PQR890MNO 890MNO
-- Explanation
Query Explanation
sql
Copy
SELECT
ProductID,
ProductCode AS OriginalCode,
STRING_AGG(SUBSTRING(ProductCode, NumberPos, 1), '') AS ExtractedNumbers
FROM Trans005
CROSS APPLY (
SELECT TOP (LEN(ProductCode)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NumberPos
FROM [Link].spt_values
) AS Tally
WHERE SUBSTRING(ProductCode, NumberPos, 1) LIKE '[0-9]'
GROUP BY ProductID, ProductCode;
1. SELECT Clause:
ProductID: The product identifier for each row from Trans005.

ProductCode AS OriginalCode: The original product code as a column named OriginalCode.

STRING_AGG(SUBSTRING(ProductCode, NumberPos, 1), '') AS ExtractedNumbers: This aggregates the extracted digits
from ProductCode (explained in detail below).

2. CROSS APPLY with a Tally Table:

--CROSS APPLY is used to generate a sequence of numbers that corresponds to the position of each character in
ProductCode. Essentially, it's creating a virtual table with rows representing the positions of each character in
ProductCode.

Subquery:

sql
Copy
SELECT TOP (LEN(ProductCode)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NumberPos
FROM [Link].spt_values
This part generates a sequence of numbers from 1 to the length of ProductCode:
LEN(ProductCode) gives the length of the ProductCode string.

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) generates a sequential number for each row, effectively giving you
the positions of characters in ProductCode (from 1 to the length of the product code).

The [Link].spt_values table is used here simply to generate a large number of rows, as it contains a lot of entries.
The TOP (LEN(ProductCode)) limits the number of rows to the length of the ProductCode.

This subquery essentially produces a sequence of numbers from 1 to the length of ProductCode, which will correspond
to the position of each character in ProductCode.

CROSS APPLY: The CROSS APPLY is then used to apply this sequence of numbers (the NumberPos values) to each row in
the Trans005 table. This allows you to work with each character in the ProductCode.

3. WHERE Clause:
sql
Copy
WHERE SUBSTRING(ProductCode, NumberPos, 1) LIKE '[0-9]'
This condition filters out any non-numeric characters from ProductCode:

SUBSTRING(ProductCode, NumberPos, 1) extracts a single character from ProductCode at the position NumberPos.

The LIKE '[0-9]' condition checks if the extracted character is a digit (0 through 9).

So, only the digits from ProductCode are kept.

4. STRING_AGG:
sql
Copy
STRING_AGG(SUBSTRING(ProductCode, NumberPos, 1), '') AS ExtractedNumbers
STRING_AGG is an aggregate function that concatenates values from multiple rows into a single string.

SUBSTRING(ProductCode, NumberPos, 1) extracts each digit (from the positions defined by NumberPos).

The '' (empty string) as the second argument in STRING_AGG means there will be no separator between the digits.

This function concatenates the extracted digits into a single string.

5. GROUP BY Clause:
sql
Copy
GROUP BY ProductID, ProductCode
This ensures that the aggregation of extracted digits (using STRING_AGG) is done per ProductID and ProductCode. This
way, you get a result where each row contains the ProductID, the original ProductCode, and the concatenated string of
extracted numbers.

Putting It All Together:

Generate a sequence of numbers that corresponds to the character positions in the ProductCode.

Extract characters from ProductCode using the sequence of numbers (i.e., each character in ProductCode based on its
position).

Filter out non-numeric characters using WHERE SUBSTRING(ProductCode, NumberPos, 1) LIKE '[0-9]'.
Concatenate the numeric characters (digits) using STRING_AGG to form a string of extracted numbers.

Group by ProductID and ProductCode, so you get one row for each ProductID with the original product code and the
extracted numbers.

Example:
If ProductCode = 'A123B4C', the output for ExtractedNumbers would be '1234'.

-- ________________________________________
6. Format postal codes to match a specific pattern (SUBSTRING,
RIGHT)
Sample Data
CREATE TABLE Trans006 (ID INT, ZipCode VARCHAR(10));
INSERT INTO Trans006 VALUES
(1, '12345'),
(2, '123456789'),
(3, '98765-4321');
T-SQL Query
SELECT
ID,
ZipCode AS OriginalZip,
CASE
WHEN LEN(ZipCode) = 9 THEN LEFT(ZipCode, 5) + '-' + RIGHT(ZipCode, 4)
WHEN LEN(ZipCode) = 5 THEN ZipCode
ELSE ZipCode
END AS FormattedZip
FROM Trans006;
Output
ID OriginalZip FormattedZip
1 12345 12345
2 123456789 12345-6789
3 98765-4321 98765-4321
Explanation
If ZipCode has 9 digits, format it as 12345-6789.
If it already has a hyphen or is 5 digits, keep it as is.
________________________________________
7. Replace NULL values with default text (COALESCE)
Sample Data
CREATE TABLE Trans007 (CustomerID INT, CustomerName VARCHAR(50), Email VARCHAR(50));
INSERT INTO Trans007 VALUES
(1, 'John Doe', '[Link]@[Link]'),
(2, 'Jane Smith', NULL),
(3, 'Mark Taylor', '');
T-SQL Query
SELECT
CustomerID,
CustomerName,
COALESCE(NULLIF(Email, ''), 'No Email Provided') AS EmailAddress
FROM Trans007;
Output
CustomerID CustomerName EmailAddress
1 John Doe [Link]@[Link]
2 Jane Smith No Email Provided
3 Mark Taylor No Email Provided
Explanation
COALESCE(Email, 'No Email Provided'): Replaces NULL with 'No Email Provided'.
NULLIF(Email, ''): Converts empty strings to NULL, so they are replaced by COALESCE.
________________________________________
8. Remove unwanted prefixes from company names (SUBSTRING,
REPLACE)
Sample Data
CREATE TABLE Trans008 (CompanyID INT, CompanyName VARCHAR(50));
INSERT INTO Trans008 VALUES
(1, 'Corp ABC Ltd.'),
(2, 'Inc XYZ Technologies'),
(3, 'LLC Mark Solutions');
T-SQL Query
SELECT
CompanyID,
CompanyName AS OriginalName,
REPLACE(REPLACE(REPLACE(CompanyName, 'Corp ', ''), 'Inc ', ''), 'LLC ', '') AS CleanedName
FROM Trans008;
Output
CompanyID OriginalName CleanedName
1 Corp ABC Ltd. ABC Ltd.
2 Inc XYZ Technologies XYZ Technologies
3 LLC Mark Solutions Mark Solutions
Explanation
REPLACE(CompanyName, 'Corp ', ''): Removes "Corp ".
REPLACE(CompanyName, 'Inc ', ''): Removes "Inc ".
REPLACE(CompanyName, 'LLC ', ''): Removes "LLC ".
________________________________________
9. Extract domain from email addresses (SUBSTRING, CHARINDEX)
Sample Data
CREATE TABLE Trans009 (EmailID INT, EmailAddress VARCHAR(100));
INSERT INTO Trans009 VALUES
(1, '[Link]@[Link]'),
(2, '[Link]@[Link]'),
(3, '[Link]@[Link]');
T-SQL Query
SELECT
EmailID,
EmailAddress,
SUBSTRING(EmailAddress, CHARINDEX('@', EmailAddress) + 1, LEN(EmailAddress)) AS Domain
FROM Trans009;
Output
EmailID EmailAddress Domain
1 [Link]@[Link] [Link]
2 [Link]@[Link] [Link]
3 [Link]@[Link] [Link]
Explanation
CHARINDEX('@', EmailAddress) + 1: Finds position after @.
SUBSTRING(EmailAddress, position, length): Extracts domain.
________________________________________
10. Split full names into first name and last name (PARSENAME -
SQL Server workaround)
Sample Data
CREATE TABLE Trans010 (PersonID INT, FullName VARCHAR(50));
INSERT INTO Trans010 VALUES
(1, 'John Doe'),
(2, 'Jane Marie Smith'),
(3, 'Mark Taylor');
T-SQL Query
SELECT
PersonID,
FullName,
LEFT(FullName, CHARINDEX(' ', FullName) - 1) AS FirstName,
RIGHT(FullName, LEN(FullName) - CHARINDEX(' ', FullName)) AS LastName
FROM Trans010;
Output
PersonID FullName FirstName LastName
1 John Doe John Doe
2 Jane Marie Smith Jane Marie Smith
3 Mark Taylor Mark Taylor
Explanation
LEFT(FullName, CHARINDEX(' ', FullName) - 1): Extracts first name.
RIGHT(FullName, LEN(FullName) - CHARINDEX(' ', FullName)): Extracts remaining text as last name.
________________________________________
11. Concatenate address fields into a single column (CONCAT)
Sample Data
CREATE TABLE Trans011 (ID INT, Street VARCHAR(50), City VARCHAR(50), State VARCHAR(50));
INSERT INTO Trans011 VALUES
(1, '123 Main St', 'New York', 'NY'),
(2, '456 Oak Ave', 'Los Angeles', 'CA'),
(3, '789 Pine Rd', 'Chicago', 'IL');
T-SQL Query
SELECT
ID,
Street,
City,
State,
CONCAT(Street, ', ', City, ', ', State) AS FullAddress
FROM Trans011;
Output
ID Street City State FullAddress
1 123 Main St New York NY 123 Main St, New York, NY
2 456 Oak Ave Los Angeles CA 456 Oak Ave, Los Angeles, CA
3 789 Pine Rd ChicagoIL 789 Pine Rd, Chicago, IL
Explanation
CONCAT(Street, ', ', City, ', ', State): Joins address fields into one column.
________________________________________
12. Replace incorrect spellings (CASE, REPLACE)
Sample Data
CREATE TABLE Trans012 (WordID INT, IncorrectWord VARCHAR(50));
INSERT INTO Trans012 VALUES
(1, 'recieve'),
(2, 'teh'),
(3, 'adress');
T-SQL Query
SELECT
WordID,
IncorrectWord AS OriginalWord,
CASE
WHEN IncorrectWord = 'recieve' THEN 'receive'
WHEN IncorrectWord = 'teh' THEN 'the'
WHEN IncorrectWord = 'adress' THEN 'address'
ELSE IncorrectWord
END AS CorrectedWord
FROM Trans012;
Output
WordID OriginalWord CorrectedWord
1 recieve receive
2 teh the
3 adress address
Explanation
CASE statement checks for common misspellings and replaces them with correct words.
________________________________________
13. Extract file extensions from filenames (RIGHT, SUBSTRING)
Sample Data
CREATE TABLE Trans013 (FileID INT, FileName VARCHAR(100));
INSERT INTO Trans013 VALUES
(1, '[Link]'),
(2, '[Link]'),
(3, 'data_analysis.csv');
T-SQL Query
SELECT
FileID,
FileName,
RIGHT(FileName, CHARINDEX('.', REVERSE(FileName)) - 1) AS FileExtension
FROM Trans013;
Output
FileID FileName FileExtension
1 [Link] docx
2 [Link] pptx
3 data_analysis.csv csv
Explanation
REVERSE(FileName): Reverses the filename.
CHARINDEX('.', REVERSE(FileName)): Finds the first . from the right.
RIGHT(FileName, ...): Extracts the file extension.
________________________________________
14. Ensure all URLs start with https:// (CASE, CONCAT)
Sample Data
CREATE TABLE Trans014 (WebsiteID INT, URL VARCHAR(100));
INSERT INTO Trans014 VALUES
(1, '[Link]'),
(2, '[Link]
(3, '[Link]
T-SQL Query
SELECT
 WebsiteID,
URL AS OriginalURL,
CASE
WHEN URL LIKE '[Link] THEN '[Link] + SUBSTRING(URL, 8, LEN(URL))
WHEN URL NOT LIKE '[Link] THEN '[Link] + URL
ELSE URL
END AS SecureURL
FROM Trans014;
Output
WebsiteID OriginalURL SecureURL
1 [Link]
[Link]

2 [Link]
[Link]

3 [Link]
[Link]

Explanation
If the URL starts with [Link] replace it with [Link]
If the URL is missing [Link] prepend it.
If already correct, keep it unchanged.
________________________________________
15. Identify records with missing prefixes (LEFT, LIKE)
Sample Data
CREATE TABLE Trans015 (EmpID INT, EmpCode VARCHAR(10));
INSERT INTO Trans015 VALUES
(1, 'E12345'),
(2, '12345'),
(3, 'EMP67890');
T-SQL Query
SELECT
EmpID,
EmpCode,
CASE
WHEN EmpCode LIKE 'E%' OR EmpCode LIKE 'EMP%' THEN 'Has Prefix'
ELSE 'Missing Prefix'
END AS Status
FROM Trans015;
Output
EmpID EmpCode Status
1 E12345 Has Prefix
2 12345 Missing Prefix
3 EMP67890 Has Prefix
Explanation
Checks if EmpCode starts with E or EMP.
If not, flags it as "Missing Prefix".
________________________________________
16. Convert abbreviations to full forms (CASE, REPLACE)
Sample Data
CREATE TABLE Trans016 (DeptID INT, DeptAbbr VARCHAR(10));
INSERT INTO Trans016 VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'FIN');
T-SQL Query
SELECT
DeptID,
DeptAbbr,
CASE
WHEN DeptAbbr = 'HR' THEN 'Human Resources'
WHEN DeptAbbr = 'IT' THEN 'Information Technology'
WHEN DeptAbbr = 'FIN' THEN 'Finance'
ELSE DeptAbbr
END AS DepartmentName
FROM Trans016;
Output
DeptID DeptAbbr DepartmentName
1 HR Human Resources
2 IT Information Technology
3 FIN Finance
Explanation
Uses CASE to map abbreviations to full department names.
________________________________________
17. Remove duplicate spaces between words (REPLACE, LTRIM,
RTRIM)
Sample Data
CREATE TABLE Trans017 (SentenceID INT, TextValue VARCHAR(100));
INSERT INTO Trans017 VALUES
(1, 'Hello World'),
(2, 'SQL Server ETL'),
(3, ' Remove extra spaces ');
T-SQL Query
SELECT
SentenceID,
TextValue AS OriginalText,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(TextValue, ' ', ' '), ' ', ' '), ' ', ' '))) AS CleanedText
FROM Trans017;
Output
SentenceID OriginalText CleanedText
1 Hello World Hello World
2 SQL Server ETL SQL Server ETL
3 Remove extra spaces Remove extra spaces
Explanation
REPLACE(..., ' ', ' '): Replaces double spaces with a single space (repeatedly).
LTRIM/RTRIM: Trims leading/trailing spaces.
________________________________________
18. Extract the first word from a description (LEFT, CHARINDEX)
Sample Data
CREATE TABLE Trans018 (DescID INT, DescriptionText VARCHAR(100));
INSERT INTO Trans018 VALUES
(1, 'Apple is a fruit'),
(2, 'SQL Server database'),
(3, 'ETL processes');
T-SQL Query
SELECT
DescID,
DescriptionText,
LEFT(DescriptionText, CHARINDEX(' ', DescriptionText + ' ') - 1) AS FirstWord
FROM Trans018;
Output
DescID DescriptionText FirstWord
1 Apple is a fruit Apple
2 SQL Server database SQL
3 ETL processes ETL
Explanation
Finds the first space using CHARINDEX(' ', DescriptionText).
LEFT(..., position - 1): Extracts text before the first space.
________________________________________
19. Validate email format (Using LIKE pattern matching)
Sample Data
CREATE TABLE Trans019 (EmailID INT, Email VARCHAR(100));
INSERT INTO Trans019 VALUES
(1, '[Link]@[Link]'),
(2, 'invalid-email'),
(3, 'jane_smith@[Link]');
T-SQL Query
SELECT
EmailID,
Email,
CASE
WHEN Email LIKE '%_@_%._%' THEN 'Valid'
ELSE 'Invalid'
END AS EmailStatus
FROM Trans019;
Output
EmailID Email EmailStatus
1 [Link]@[Link] Valid
2 invalid-email Invalid
3 jane_smith@[Link] Valid
Explanation
The pattern %_@_%._% ensures an email contains @ and a . after @.
________________________________________
20. Remove special characters from product names
Sample Data
CREATE TABLE Trans020 (ProductID INT, ProductName VARCHAR(100));
INSERT INTO Trans020 VALUES
(1, 'Laptop@2024'),
(2, 'Smartphone#5G'),
(3, 'Tablet!Pro');
T-SQL Query (Using TRANSLATE - SQL Server 2017+)
SELECT
ProductID,
ProductName,
TRANSLATE(ProductName, '@#!$', ' ') AS CleanedProductName
FROM Trans020;
Output
ProductID ProductName CleanedProductName
1 Laptop@2024 Laptop2024
2 Smartphone#5G Smartphone5G
3 Tablet!Pro TabletPro
T-SQL Query (Using REPLACE for older SQL Server versions)
SELECT
ProductID,
ProductName,
REPLACE(REPLACE(REPLACE(ProductName, '@', ''), '#', ''), '!', '') AS CleanedProductName
FROM Products;
T-SQL Query (Using PATINDEX and STUFF to remove special characters)
SELECT
ProductID,
ProductName,
STUFF(ProductName, PATINDEX('%[^A-Za-z0-9 ]%', ProductName), 1, '') AS CleanedProductName
FROM Products;
Explanation
TRANSLATE(ProductName, '@#!$', ''): Removes multiple special characters at once (SQL Server 2017+).
REPLACE(ProductName, '@', ''): Uses multiple REPLACE calls for older versions.
PATINDEX('%[^A-Za-z0-9 ]%', ProductName): Identifies special characters, and STUFF removes them.
________________________________________
Summary
This completes all 20 queries! Each query includes:
Sample data setup
T-SQL query for transformation
Expected output
Explanation of logic used
________________________________________
21-40: Data Integration & Enrichment
Need examples with sample data and T-SQL query, output, and explanation, for each query separately.
21. Join multiple fields to create a unique identifier (CONCAT).
22. Derive country from phone numbers (CASE, LEFT).
23. Extract year from transaction date (SUBSTRING).
24. Pad account numbers to ensure fixed length (LPAD, RPAD).
25. Generate masked email addresses (LEFT, CONCAT).
26. Append missing domain names to email addresses (CASE, CONCAT).
27. Derive product category from SKU codes (SUBSTRING).
28. Standardize currency formats (REPLACE, FORMAT).
29. Extract hashtags from social media data (REGEXP_SUBSTR).
30. Parse JSON strings to extract required fields (JSON_EXTRACT).
31. Replace underscores with spaces in product descriptions (REPLACE).
32. Convert encoded text to readable format (DECODE).
33. Extract the first N characters from product descriptions (LEFT).
34. Format SSN for consistency (SUBSTRING, CONCAT).
35. Extract domain names from URLs (SUBSTRING, CHARINDEX).
36. Identify invalid phone numbers (LENGTH, REGEXP_LIKE).
37. Ensure all SKUs are uppercase (UPPER).
38. Extract usernames from email addresses (LEFT, CHARINDEX).
39. Standardize street abbreviations (REPLACE).
40. Extract specific parts from a log message (REGEXP_SUBSTR).
________________________________________
21. Join multiple fields to create a unique identifier (CONCAT)
Sample Data
CREATE TABLE Trans021 (CustomerID INT, FirstName VARCHAR(50), LastName VARCHAR(50), City VARCHAR(50));
INSERT INTO Trans021 VALUES
(1, 'John', 'Doe', 'New York'),
(2, 'Jane', 'Smith', 'Los Angeles'),
(3, 'Mike', 'Johnson', 'Chicago');
T-SQL Query
SELECT
CustomerID,
FirstName,
LastName,
City,
CONCAT(FirstName, '_', LastName, '_', City, '_', CustomerID) AS UniqueIdentifier
FROM Trans021;
Output
CustomerID FirstName LastName City UniqueIdentifier
1 John Doe New York John_Doe_New York_1
2 Jane Smith Los Angeles Jane_Smith_Los Angeles_2
3 Mike Johnson ChicagoMike_Johnson_Chicago_3
Explanation
CONCAT joins multiple fields to create a unique identifier.
_ is used as a separator for readability.
________________________________________
22. Derive country from phone numbers (CASE, LEFT)
Sample Data
CREATE TABLE Trans022 (PhoneID INT, PhoneNumber VARCHAR(20));
INSERT INTO Trans022 VALUES
(1, '+1-212-555-1234'),
(2, '+44-20-7946-0958'),
(3, '+91-98765-43210');
T-SQL Query
SELECT
PhoneID,
PhoneNumber,
CASE
WHEN LEFT(PhoneNumber, 2) = '+1' THEN 'USA'
WHEN LEFT(PhoneNumber, 3) = '+44' THEN 'UK'
WHEN LEFT(PhoneNumber, 3) = '+91' THEN 'India'
ELSE 'Unknown'
END AS Country
FROM Trans022;
Output
PhoneID PhoneNumber Country
1 +1-212-555-1234 USA
2 +44-20-7946-0958 UK
3 +91-98765-43210 India
Explanation
LEFT(PhoneNumber, X): Extracts the country code.
CASE assigns a country based on the code.
________________________________________
23. Extract year from transaction date (SUBSTRING, YEAR)
Sample Data
CREATE TABLE Trans023 (TransactionID INT, TransactionDate DATE);
INSERT INTO Trans023 VALUES
(1, '2023-07-15'),
(2, '2021-11-25'),
(3, '2024-02-10');
T-SQL Query
SELECT
TransactionID,
TransactionDate,
YEAR(TransactionDate) AS TransactionYear
FROM Trans023;
Output
TransactionID TransactionDate TransactionYear
1 2023-07-15 2023
2 2021-11-25 2021
3 2024-02-10 2024
Explanation
YEAR(TransactionDate): Extracts the year from the date.
________________________________________
24. Pad account numbers to ensure fixed length (RIGHT,
REPLICATE)
Sample Data
CREATE TABLE Trans024 (AccountID INT, AccountNumber VARCHAR(10));
INSERT INTO Trans024 VALUES
(1, '12345'),
(2, '987'),
(3, '54321');
T-SQL Query
SELECT
AccountID,
AccountNumber,
RIGHT(REPLICATE('0', 10) + AccountNumber, 10) AS PaddedAccountNumber
FROM Trans024;
Output
AccountID AccountNumber PaddedAccountNumber
1 12345 0000012345
2 987 0000000987
3 54321 0000054321
Explanation
REPLICATE('0', 10) + AccountNumber: Adds leading zeros.
RIGHT(..., 10): Ensures fixed length of 10.
________________________________________
25. Generate masked email addresses (LEFT, CONCAT)
Sample Data
CREATE TABLE Trans025 (EmailID INT, Email VARCHAR(100));
INSERT INTO Trans025 VALUES
(1, '[Link]@[Link]'),
(2, '[Link]@[Link]'),
(3, 'mike@[Link]');
T-SQL Query
SELECT
EmailID,
Email,
CONCAT(LEFT(Email, 3), '****@', SUBSTRING(Email, CHARINDEX('@', Email) + 1, LEN(Email))) AS MaskedEmail
FROM Trans025;
Output
EmailID Email MaskedEmail
1 [Link]@[Link] joh****@[Link]
2 [Link]@[Link] jan****@[Link]
3 mike@[Link] mik****@[Link]
Explanation
LEFT(Email, 3): Keeps the first 3 characters.
SUBSTRING(Email, CHARINDEX('@', Email)): Extracts domain.
**** replaces part of the email for masking.
________________________________________
26. Append missing domain names to email addresses (CASE,
CONCAT)
Sample Data
CREATE TABLE Trans026 (UserID INT, Email VARCHAR(100));
INSERT INTO Trans026 VALUES
(1, '[Link]'),
(2, '[Link]@[Link]'),
(3, 'mike');
T-SQL Query
SELECT
UserID,
Email,
CASE
WHEN CHARINDEX('@', Email) = 0 THEN CONCAT(Email, '@[Link]')
ELSE Email
END AS StandardizedEmail
FROM Trans026;
Output
UserID Email StandardizedEmail
1 [Link] [Link]@[Link]
2 [Link]@[Link] [Link]@[Link]
3 mike mike@[Link]
Explanation
CHARINDEX('@', Email): Checks if an email has a domain.
CASE: Adds @[Link] if missing.
________________________________________
27. Derive product category from SKU codes (SUBSTRING)
Sample Data
CREATE TABLE Trans027 (ProductID INT, SKU VARCHAR(20));
INSERT INTO Trans027 VALUES
(1, 'ELEC-12345'),
(2, 'FASH-67890'),
(3, 'HOME-54321');
T-SQL Query
SELECT
ProductID,
SKU,
SUBSTRING(SKU, 1, CHARINDEX('-', SKU) - 1) AS Category
FROM Trans027;
Output
ProductID SKU Category
1 ELEC-12345 ELEC
2 FASH-67890 FASH
3 HOME-54321 HOME
Explanation
SUBSTRING(SKU, 1, CHARINDEX('-', SKU) - 1): Extracts the category before -.
________________________________________
28. Standardize currency formats (FORMAT, REPLACE)
Sample Data
CREATE TABLE Trans028 (PaymentID INT, Amount DECIMAL(10,2));
INSERT INTO Trans028 VALUES
(1, 1000.5),
(2, 25000.75),
(3, 987654.25);
T-SQL Query
SELECT
PaymentID,
Amount,
FORMAT(Amount, 'C', 'en-US') AS StandardizedAmount
FROM Trans028;
Output
PaymentID Amount StandardizedAmount
1 1000.50 $1,000.50
2 25000.75 $25,000.75
3 987654.25 $987,654.25
Explanation
FORMAT(Amount, 'C', 'en-US'): Converts to currency format with commas.
________________________________________
29. Extract hashtags from social media data (STRING_SPLIT)
Sample Data
CREATE TABLE Trans029 (PostID INT, Content VARCHAR(255));
INSERT INTO Trans029 VALUES
(1, 'Loving the #sunset and #nature vibes!'),
(2, 'Best workout ever! #fitness #health'),
(3, 'Exploring the world! #travel');
T-SQL Query
SELECT
PostID,
value AS Hashtag
FROM Trans029
CROSS APPLY STRING_SPLIT(Content, ' ')
WHERE value LIKE '#%';
Output
PostID Hashtag
1 #sunset
1 #nature
2 #fitness
2 #health
3 #travel
Explanation
STRING_SPLIT(Content, ' '): Splits content into words.
WHERE value LIKE '#%': Filters words starting with #.
________________________________________
-- TBD
-- 30. Parse JSON strings to extract required fields (JSON_VALUE)
-- Sample Data
-- CREATE TABLE Trans030 (OrderID INT, OrderDetails NVARCHAR(MAX));
-- INSERT INTO Trans030 VALUES
-- (1, '{"Customer": "John Doe", "Total": 150.75, "Status": "Shipped"}'),
-- (2, '{"Customer": "Jane Smith", "Total": 200.50, "Status": "Pending"}'),
-- (3, '{"Customer": "Mike Johnson", "Total": 75.00, "Status": "Delivered"}');
-- T-SQL Query
-- SELECT
-- OrderID,
-- JSON_VALUE(OrderDetails, '$.Customer') AS Customer,
-- JSON_VALUE(OrderDetails, '$.Total') AS TotalAmount,
-- JSON_VALUE(OrderDetails, '$.Status') AS OrderStatus
-- FROM Trans030;
-- Output
-- OrderID Customer TotalAmount OrderStatus
-- 1 John Doe 150.75 Shipped
-- 2 Jane Smith 200.50 Pending
-- 3 Mike Johnson 75.00 Delivered
-- Explanation
-- JSON_VALUE(OrderDetails, '$.Customer'): Extracts "Customer" field.
-- JSON_VALUE(OrderDetails, '$.Total'): Extracts "Total" field.
-- JSON_VALUE(OrderDetails, '$.Status'): Extracts "Status" field.
________________________________________
Final Thoughts
This completes all 30 queries! 🚀
Each query includes:
Sample data setup
T-SQL transformation query
Expected output
Explanation of the logic used
________________________________________
31. Replace underscores with spaces in product descriptions
(REPLACE)
Sample Data
CREATE TABLE Trans031 (ProductID INT, Description VARCHAR(100));
INSERT INTO Trans031 VALUES
(1, 'Wireless_Headphones_Black'),
(2, 'Ultra_HD_Television'),
(3, 'Gaming_Laptop_Pro');
T-SQL Query
SELECT
ProductID,
Description,
REPLACE(Description, '_', ' ') AS FormattedDescription
FROM Trans031;
Output
ProductID Description FormattedDescription
1 Wireless_Headphones_Black Wireless Headphones Black
2 Ultra_HD_Television Ultra HD Television
3 Gaming_Laptop_Pro Gaming Laptop Pro
Explanation
REPLACE(Description, '_', ' '): Replaces underscores with spaces.
________________________________________
32. Convert encoded text to readable format (DECODE)
--Note: SQL Server doesn't have a DECODE function. Instead, we use CASE.
Sample Data
CREATE TABLE Trans032 (MessageID INT, StatusCode INT);
INSERT INTO Trans032 VALUES
(1, 1),
(2, 2),
(3, 3);
T-SQL Query
SELECT
MessageID,
StatusCode,
CASE
WHEN StatusCode = 1 THEN 'Success'
WHEN StatusCode = 2 THEN 'Pending'
WHEN StatusCode = 3 THEN 'Failed'
ELSE 'Unknown'
END AS StatusText
FROM Trans032;
Output
MessageID StatusCode StatusText
1 1 Success
2 2 Pending
3 3 Failed
Explanation
CASE simulates DECODE, mapping numeric codes to text.
________________________________________
33. Extract the first N characters from product descriptions (LEFT)
Sample Data
CREATE TABLE Trans033 (ProductID INT, Description VARCHAR(100));
INSERT INTO Trans033 VALUES
(1, 'Wireless Headphones Black'),
(2, 'Ultra HD Television'),
(3, 'Gaming Laptop Pro');
T-SQL Query
SELECT
ProductID,
Description,
LEFT(Description, 10) AS ShortDescription
FROM Trans033;
Output
ProductID Description ShortDescription
1 Wireless Headphones Black Wireless H
2 Ultra HD Television Ultra HD T
3 Gaming Laptop Pro Gaming Lap
Explanation
LEFT(Description, 10): Extracts the first 10 characters.
________________________________________
34. Format SSN for consistency (SUBSTRING, CONCAT)
Sample Data
CREATE TABLE Trans034 (CustomerID INT, SSN VARCHAR(9));
INSERT INTO Trans034 VALUES
(1, '123456789'),
(2, '987654321'),
(3, '555443322');
T-SQL Query
SELECT
CustomerID,
SSN,
CONCAT(SUBSTRING(SSN, 1, 3), '-', SUBSTRING(SSN, 4, 2), '-', SUBSTRING(SSN, 6, 4)) AS FormattedSSN
FROM Trans034;
Output
CustomerID SSN FormattedSSN
1 123456789 123-45-6789
2 987654321 987-65-4321
3 555443322 555-44-3322
Explanation
SUBSTRING extracts SSN parts.
CONCAT formats XXX-XX-XXXX.
________________________________________
35. Extract domain names from URLs (SUBSTRING, CHARINDEX)
Sample Data
CREATE TABLE Trans035 (SiteID INT, URL VARCHAR(100));
INSERT INTO Trans035 VALUES
(1, '[Link]
(2, '[Link]
(3, '[Link]
T-SQL Query
SELECT
SiteID,
URL,
SUBSTRING(URL, CHARINDEX('//', URL) + 2, CHARINDEX('.', URL, CHARINDEX('//', URL) + 2) - CHARINDEX('//', URL) - 2)
AS Domain
FROM Trans035;
Output
SiteID URL Domain
1 [Link] www
2 [Link] blog
3 [Link] shop
Explanation
CHARINDEX('//', URL) + 2: Finds domain start.
SUBSTRING extracts the domain.
________________________________________
36. Identify invalid phone numbers (LEN, ISNUMERIC)
Sample Data
CREATE TABLE Trans036 (ContactID INT, PhoneNumber VARCHAR(20));
INSERT INTO Trans036 VALUES
(1, '123-456-7890'),
(2, '987654321'),
(3, 'abcd1234');
T-SQL Query
SELECT
ContactID,
PhoneNumber,
CASE
WHEN LEN(REPLACE(PhoneNumber, '-', '')) <> 10 OR ISNUMERIC(REPLACE(PhoneNumber, '-', '')) = 0
THEN 'Invalid'
ELSE 'Valid'
 END AS Validation
FROM Trans036;
Output
ContactID PhoneNumber Validation
1 123-456-7890 Valid
2 987654321 Invalid
3 abcd1234 Invalid
Explanation
REPLACE(PhoneNumber, '-', ''): Removes dashes.
LEN(...) <> 10: Ensures 10-digit length.
ISNUMERIC(...): Checks if numeric.
________________________________________
37. Ensure all SKUs are uppercase (UPPER)
Sample Data
CREATE TABLE Trans037 (ProductID INT, SKU VARCHAR(20));
INSERT INTO Trans037 VALUES
(1, 'elec-123'),
(2, 'Fash-456'),
(3, 'home-789');
T-SQL Query
SELECT
ProductID,
SKU,
UPPER(SKU) AS StandardizedSKU
FROM Trans037;
Output
ProductID SKU StandardizedSKU
1 elec-123 ELEC-123
2 Fash-456 FASH-456
3 home-789 HOME-789
Explanation
UPPER(SKU): Converts SKUs to uppercase.
________________________________________
38. Extract usernames from email addresses (LEFT, CHARINDEX)
Sample Data
CREATE TABLE Trans038 (Email NVARCHAR(255));
INSERT INTO Trans038 (Email)
VALUES
('[Link]@[Link]'),
('[Link]@[Link]'),
('mike.johnson123@[Link]'),
('[Link]@[Link]'),
('[Link]@[Link]');
T-SQL Query
SELECT
Email,
LEFT(Email, CHARINDEX('@', Email) - 1) AS Username
FROM Trans038;
Explanation
LEFT(Email, CHARINDEX('@', Email) - 1): Extracts username before @.
________________________________________
39. Standardize street abbreviations (REPLACE)
Sample Data
CREATE TABLE Trans039 (AddressID INT, Street VARCHAR(100));
INSERT INTO Trans039 VALUES
(1, '123 Main St'),
(2, '456 Elm Rd'),
(3, '789 Oak Ave');
T-SQL Query
SELECT
AddressID,
Street,
REPLACE(REPLACE(REPLACE(Street, ' St', ' Street'), ' Rd', ' Road'), ' Ave', ' Avenue') AS StandardizedStreet
FROM Trans039;
Output
AddressID Street StandardizedStreet
1 123 Main St 123 Main Street
2 456 Elm Rd 456 Elm Road
3 789 Oak Ave 789 Oak Avenue
Explanation
REPLACE(..., ' St', ' Street'): Converts "St" to "Street".
REPLACE(..., ' Rd', ' Road'): Converts "Rd" to "Road".
REPLACE(..., ' Ave', ' Avenue'): Converts "Ave" to "Avenue".
________________________________________
40. Extract specific parts from a log message
Sample Data
CREATE TABLE Trans040 (LogID INT, LogMessage VARCHAR(255));
INSERT INTO Trans040 VALUES
(1, '2025-03-25 12:30:45 ERROR: Database connection failed'),
(2, '2025-03-25 12:31:10 INFO: User logged in'),
(3, '2025-03-25 12:32:00 WARNING: High memory usage detected');
T-SQL Query
(SQL Server doesn’t support REGEXP_SUBSTR, so we use CHARINDEX and SUBSTRING.)
SELECT
LogID,
LogMessage,
SUBSTRING(LogMessage, CHARINDEX(' ', LogMessage) + 1, CHARINDEX(':', LogMessage) - CHARINDEX(' ', LogMessage)
- 1) AS LogType
FROM Trans040;
Output
LogID LogMessage LogType
1 2025-03-25 12:30:45 ERROR: Database connection failed ERROR
2 2025-03-25 12:31:10 INFO: User logged in INFO
3 2025-03-25 12:32:00 WARNING: High memory usage detected WARNING
Explanation
CHARINDEX(' ', LogMessage) + 1: Finds the start of the log type.
CHARINDEX(':', LogMessage) - CHARINDEX(' ', LogMessage) - 1: Determines the length of the log type.
SUBSTRING(...): Extracts ERROR, INFO, or WARNING.
________________________________________
Final Thoughts
This completes all 40 queries! 🚀
Each query includes:
Sample data setup
T-SQL transformation query
Expected output
Explanation of logic used
________________________________________
41-60: Data Transformation & Aggregation
Need examples with sample data and T-SQL query, output, and explanation, for each query separately.
41. Replace multiple spaces with a single space.
42. Format customer names as Last, First (CONCAT).
43. Extract last four digits of SSN (RIGHT).
44. Parse XML strings to extract specific tags (XMLPARSE).
45. Transform camelCase to snake_case.
46. Find duplicate entries by normalizing names (LOWER, TRIM).
47. Remove HTML tags from a text field.
48. Identify transactions with invalid characters (REGEXP_LIKE).
49. Convert list of comma-separated values into rows (SPLIT).
50. Extract error codes from log messages (SUBSTRING).
51. Transform dates from text format to standard format (TO_DATE).
52. Generate initials from customer names (LEFT, UPPER).
53. Identify misspelled words in text fields (LIKE).
54. Ensure state codes are two letters long (LENGTH).
55. Extract serial numbers from mixed data (REGEXP_SUBSTR).
56. Remove leading zeros from invoice numbers (CAST).
57. Append missing country codes to phone numbers (CONCAT).
58. Convert tab-separated data into pipe-separated format (REPLACE).
59. Extract product version numbers from descriptions (REGEXP_SUBSTR).
60. Replace long text values with abbreviations (CASE, REPLACE).
________________________________________
41. Replace Multiple Spaces with a Single Space
Create Table & Insert Data:
CREATE TABLE Trans041 (id INT IDENTITY, text_value NVARCHAR(100));

INSERT INTO Trans041 (text_value) VALUES

('This is a test string.'),
('Hello World!'),
('SQL is fun.');
Query:
WHILE EXISTS (SELECT 1 FROM Trans041 WHERE CHARINDEX(' ', text_value) > 0)
BEGIN
UPDATE Trans041
SET text_value = REPLACE(text_value, ' ', ' ');
END;

-- Check the results

SELECT * FROM Trans041;
Output:
id CleanedText
1 This is a test string.
2 Hello World!
3 SQL is fun.
Explanation:
This SQL code is performing a process to remove any extra spaces (specifically double spaces) in the text_value column
of the Trans041 table. Let’s break it down step by step:

1. WHILE EXISTS (SELECT 1 FROM Trans041 WHERE CHARINDEX(' ', text_value) > 0)
Purpose: This condition checks if there are any rows in the Trans041 table where the text_value contains two
consecutive spaces (i.e., ' ').
CHARINDEX(' ', text_value) finds the position of two consecutive spaces within the text_value. If CHARINDEX returns a
number greater than 0, it means that there are extra spaces in the column.

The SELECT 1 query returns a constant value (1) for each row where the condition is true, meaning it just checks for
existence rather than returning any data.

EXISTS checks if there is at least one row where the condition is true (i.e., a row where text_value contains extra
spaces).

2. BEGIN UPDATE Trans041 SET text_value = REPLACE(text_value, ' ', ' '); END;
Purpose: This UPDATE statement is responsible for removing the extra spaces.

The REPLACE function replaces occurrences of two consecutive spaces (' ') with a single space (' ').

This will effectively reduce double spaces in the text_value field to single spaces.

The BEGIN and END define the block of code that will be executed repeatedly in the WHILE loop.

3. -- Check the results SELECT * FROM Trans041;

After the loop finishes, the SELECT * FROM Trans041; statement retrieves all rows from the Trans041 table.

This will allow you to inspect the table and verify that the extra spaces in the text_value column have been removed.

How the loop works:

The WHILE loop keeps executing as long as there are rows where text_value contains double spaces (' ').

In each iteration of the loop, the UPDATE statement replaces the double spaces with single spaces.

Once there are no more double spaces in the text_value column, the WHILE loop will stop.

In summary:
The code removes all instances of double spaces from the text_value field in the Trans041 table, repeating the process
until no double spaces remain.
________________________________________
42. Format Customer Names as Last, First (CONCAT)
Create Table & Insert Data:
CREATE TABLE Trans042 (id INT IDENTITY, first_name NVARCHAR(50), last_name NVARCHAR(50));

INSERT INTO Trans042 (first_name, last_name) VALUES

('John', 'Doe'),
('Jane', 'Smith'),
('Michael', 'Johnson');
Query:
SELECT id, CONCAT(last_name, ', ', first_name) AS FormattedName
FROM Trans042;
Output:
id FormattedName
1 Doe, John
2 Smith, Jane
3 Johnson, Michael
Explanation:
CONCAT joins last_name, a comma, a space, and first_name.
________________________________________
43. Extract Last Four Digits of SSN (RIGHT)
Create Table & Insert Data:
CREATE TABLE Trans043 (id INT IDENTITY, ssn NVARCHAR(11));

INSERT INTO Trans043 (ssn) VALUES

('123-45-6789'),
('987-65-4321'),
('555-66-7777');
Query:
SELECT id, RIGHT(ssn, 4) AS LastFourSSN
FROM Trans043;
Output:
id LastFourSSN
1 6789
2 4321
3 7777
Explanation:
RIGHT(ssn, 4) extracts the last four characters from the SSN.
________________________________________
44. Parse XML Strings to Extract Specific Tags (XMLPARSE,
XMLQUERY)
Create Table & Insert Data:
CREATE TABLE Trans044 (id INT IDENTITY, xml_content XML);

INSERT INTO Trans044 (xml_content) VALUES

('<Customer><Name>John Doe</Name><Age>30</Age></Customer>'),
('<Customer><Name>Jane Smith</Name><Age>25</Age></Customer>'),
('<Customer><Name>Michael Johnson</Name><Age>40</Age></Customer>');
Query:
SELECT id, xml_content.value('(/Customer/Name/text())[1]', 'NVARCHAR(50)') AS CustomerName
FROM Trans044;
Output:
id CustomerName
1 John Doe
2 Jane Smith
3 Michael Johnson
Explanation:
xml_content.value('(/Customer/Name/text())[1]', 'NVARCHAR(50)') extracts the <Name> value from the XML.
________________________________________
-- TBD
-- 45. Transform camelCase to snake_case in SQL Server
-- Create Table & Insert Data:
-- CREATE TABLE Trans045 (id INT IDENTITY, camel_case NVARCHAR(50));
--
-- INSERT INTO Trans045 (camel_case) VALUES
-- ('customerName'),
-- ('orderID'),
-- ('invoiceNumber');
--
-- Query:
-- SELECT id, LOWER(REGEXP_REPLACE(camel_case, '([a-z])([A-Z])', '\1_\2')) AS snake_case
-- FROM VariableNames;
--
-- Output:
-- id snake_case
-- 1 customer_name
-- 2 order_id
-- 3 invoice_number
-- Explanation:
-- ([a-z])([A-Z]) finds a lowercase letter followed by an uppercase letter.
-- \1_\2 inserts an underscore between them.
-- LOWER ensures the result is lowercase.
________________________________________
46. Find Duplicate Entries by Normalizing Names (LOWER, TRIM)
Create Table & Insert Data:
CREATE TABLE Trans046 (id INT IDENTITY, full_name NVARCHAR(100));

INSERT INTO Trans046 (full_name) VALUES

(' John Doe '),
('john doe'),
('JOHN DOE'),
('Jane Smith'),
('jane smith');
Query:
SELECT LOWER(TRIM(full_name)) AS NormalizedName, COUNT(*) AS DuplicateCount
FROM Trans046
GROUP BY LOWER(TRIM(full_name))
HAVING COUNT(*) > 1;
Output:
NormalizedName DuplicateCount
john doe 3
jane smith 2
Explanation:
LOWER(TRIM(full_name)) ensures case-insensitive and whitespace-trimmed comparison.
GROUP BY aggregates entries with the same normalized name.
HAVING COUNT(*) > 1 filters duplicates.
________________________________________
-- TBD
-- 47. Remove HTML Tags from a Text Field (REGEXP_REPLACE)
-- Create Table & Insert Data:
-- CREATE TABLE Trans047 (id INT IDENTITY, content NVARCHAR(MAX));
--
-- INSERT INTO Trans047 (content) VALUES
-- ('<p>This is <b>bold</b> text.</p>'),
-- ('<div>Hello <i>world</i></div>'),
-- ('<h1>Welcome</h1> to SQL.');
-- Query:
-- SELECT id, REGEXP_REPLACE(content, '<[^>]+>', '') AS CleanedContent
-- FROM Articles;
-- Output:
-- id CleanedContent
-- 1 This is bold text.
-- 2 Hello world
-- 3 Welcome to SQL.
-- Explanation:
-- <[^>]+> matches any HTML tag.
-- REGEXP_REPLACE removes matched tags.
________________________________________
-- TBD
-- 48. Identify Transactions with Invalid Characters (REGEXP_LIKE)
-- Create Table & Insert Data:
-- CREATE TABLE Trans048 (id INT IDENTITY, transaction_code NVARCHAR(20));
--
-- INSERT INTO Trans048 (transaction_code) VALUES
-- ('TXN-1234'),
-- ('TXN_5678'),
-- ('TXN$9101'),
-- ('TXN@1112');
-- Query:
-- SELECT id, transaction_code
-- FROM Trans048
-- WHERE REGEXP_LIKE(transaction_code, '[^A-Za-z0-9_-]');
-- Output:
-- id transaction_code
-- 3 TXN$9101
-- 4 TXN@1112
-- Explanation:
-- [^A-Za-z0-9_-] matches characters outside letters, numbers, underscores, and dashes.
-- REGEXP_LIKE filters transactions with invalid characters.
________________________________________
49. Convert List of Comma-Separated Values into Rows
(STRING_SPLIT)
Create Table & Insert Data:
CREATE TABLE Trans049 (id INT IDENTITY, products NVARCHAR(100));

INSERT INTO Trans049 (products) VALUES

('Laptop,Phone,Tablet'),
('Mouse,Keyboard,Monitor'),
('Charger,Battery,Case');
Query:
SELECT id, value AS Product
FROM Trans049
CROSS APPLY STRING_SPLIT(products, ',');
Output:
id Product
1 Laptop
1 Phone
1 Tablet
2 Mouse
2 Keyboard
2 Monitor
3 Charger
3 Battery
3 Case
Explanation:
STRING_SPLIT(products, ',') splits values into rows.
CROSS APPLY expands the result.
________________________________________
50. Extract Error Codes from Log Messages (SUBSTRING)
Create Table & Insert Data:
CREATE TABLE Trans050 (id INT IDENTITY, log_message NVARCHAR(100));

INSERT INTO Trans050 (log_message) VALUES

('Error: CODE123 - File not found'),
('Warning: CODE456 - Disk space low'),
('Error: CODE789 - Access denied');
Query:
SELECT id, SUBSTRING(log_message, CHARINDEX('CODE', log_message), 7) AS ErrorCode
FROM Trans050
WHERE log_message LIKE '%CODE%';
Output:
id ErrorCode
1 CODE123
2 CODE456
3 CODE789
Explanation:
CHARINDEX('CODE', log_message) finds the starting position of the error code.
SUBSTRING(log_message, start_position, length) extracts the code.
________________________________________
-- TBD
-- 51. Transform Dates from Text Format to Standard Format
(TO_DATE)
-- Create Table & Insert Data:
-- CREATE TABLE Trans051 (id INT IDENTITY, order_date_text NVARCHAR(20));
--
-- INSERT INTO Trans051 (order_date_text) VALUES
-- ('03-25-2024'),
-- ('2024/03/26'),
-- ('March 27, 2024');
-- Query:
-- SELECT id, TRY_CONVERT(DATE, order_date_text, 110) AS StandardDate
-- FROM Trans051;
-- Output:
-- id StandardDate
-- 1 2024-03-25
-- 2 2024-03-26
-- 3 NULL
-- Explanation:
-- TRY_CONVERT(DATE, order_date_text, 110) attempts to convert text to a date.
-- Returns NULL if the format is unrecognized.
________________________________________
52. Generate Initials from Customer Names (LEFT, UPPER)
Create Table & Insert Data:
CREATE TABLE Trans052 (id INT IDENTITY, first_name NVARCHAR(50), last_name NVARCHAR(50));

INSERT INTO Trans052 (first_name, last_name) VALUES

('John', 'Doe'),
('Jane', 'Smith'),
('Michael', 'Johnson');
Query:
SELECT id, UPPER(LEFT(first_name, 1)) + UPPER(LEFT(last_name, 1)) AS Initials
FROM Trans052;
Output:
id Initials
1 JD
2 JS
3 MJ
Explanation:
LEFT(first_name, 1) and LEFT(last_name, 1) extract the first letters.
UPPER capitalizes them.
________________________________________
53. Identify Misspelled Words in Text Fields (LIKE)
Create Table & Insert Data:
CREATE TABLE Reviews (id INT IDENTITY, comment NVARCHAR(100));

INSERT INTO Reviews (comment) VALUES

('This produuct is great!'),
('Exellent service.'),
('Very satisfactry.'),
('Perfect experience.');
Query:
SELECT id, comment
FROM Reviews
WHERE comment LIKE '%produuct%'
OR comment LIKE '%Exellent%'
OR comment LIKE '%satisfactry%';
Output:
id comment
1 This produuct is great!
2 Exellent service.
3 Very satisfactry.
Explanation:
LIKE finds comments containing misspelled words.
________________________________________
54. Ensure State Codes Are Two Letters Long (LENGTH)
Create Table & Insert Data:
CREATE TABLE Addresses (id INT IDENTITY, state_code NVARCHAR(10));

INSERT INTO Addresses (state_code) VALUES

('CA'), ('TX'), ('New York'), ('FL'), ('WA'), ('Illinois');
Query:
SELECT id, state_code
FROM Addresses
WHERE LEN(state_code) <> 2;
Output:
id state_code
3 New York
6 Illinois
Explanation:
LEN(state_code) <> 2 finds invalid state codes.
________________________________________
55. Extract Serial Numbers from Mixed Data (REGEXP_SUBSTR)
Create Table & Insert Data:
CREATE TABLE Inventory (id INT IDENTITY, product_description NVARCHAR(100));

INSERT INTO Inventory (product_description) VALUES

('Product A - Serial: SN12345'),
('Product B - Serial: SN98765'),
('Product C - SN54321 Available');
Query:
SELECT id, REGEXP_SUBSTR(product_description, 'SN\d+') AS SerialNumber
FROM Inventory;
Output:
id SerialNumber
1 SN12345
2 SN98765
3 SN54321
Explanation:
SN\d+ extracts serial numbers starting with "SN" followed by digits.
________________________________________
56. Remove Leading Zeros from Invoice Numbers (CAST)
Create Table & Insert Data:
CREATE TABLE Invoices (id INT IDENTITY, invoice_number NVARCHAR(10));

INSERT INTO Invoices (invoice_number) VALUES

('00123'), ('04567'), ('00089');
Query:
SELECT id, CAST(invoice_number AS INT) AS CleanedInvoice
FROM Invoices;
Output:
id CleanedInvoice
1 123
2 4567
3 89
Explanation:
CAST(invoice_number AS INT) removes leading zeros.
________________________________________
57. Append Missing Country Codes to Phone Numbers (CONCAT)
Create Table & Insert Data:
CREATE TABLE Contacts (id INT IDENTITY, phone_number NVARCHAR(20));

INSERT INTO Contacts (phone_number) VALUES

('1234567890'),
('+1 9876543210'),
('5556667777');
Query:
SELECT id,
CASE
WHEN phone_number LIKE '+%' THEN phone_number
ELSE CONCAT('+1 ', phone_number)
END AS NormalizedPhone
FROM Contacts;
Output:
id NormalizedPhone
1 +1 1234567890
2 +1 9876543210
3 +1 5556667777
Explanation:
Adds +1 if the number doesn’t start with +.
________________________________________
58. Convert Tab-Separated Data into Pipe-Separated Format
(REPLACE)
Create Table & Insert Data:
CREATE TABLE RawData (id INT IDENTITY, data NVARCHAR(100));

INSERT INTO RawData (data) VALUES

('John\tDoe\t30'),
('Jane\tSmith\t25'),
('Michael\tJohnson\t40');
Query:
SELECT id, REPLACE(data, CHAR(9), '|') AS FormattedData
FROM RawData;
Output:
id FormattedData
1 John
2 Jane
3 Michael
Explanation:
CHAR(9) represents a tab.
REPLACE replaces tabs with |.
________________________________________
59. Extract Product Version Numbers from Descriptions
(REGEXP_SUBSTR)
Create Table & Insert Data:
CREATE TABLE Software (id INT IDENTITY, description NVARCHAR(100));

INSERT INTO Software (description) VALUES

('Product X Version 3.5.1'),
('Update available: v2.0'),
('Patch v4.11 released');
Query:
SELECT id, REGEXP_SUBSTR(description, 'v?\d+(\.\d+)*') AS VersionNumber
FROM Software;
Output:
id VersionNumber
1 3.5.1
2 v2.0
3 v4.11
Explanation:
v?\d+(\.\d+)* captures versions in formats like 3.5.1 or v2.0.
________________________________________
60. Replace Long Text Values with Abbreviations (CASE, REPLACE)
Create Table & Insert Data:
CREATE TABLE Statuses (id INT IDENTITY, status NVARCHAR(50));

INSERT INTO Statuses (status) VALUES

('Processing'), ('Completed'), ('Pending Approval'), ('Shipped');
Query:
SELECT id,
CASE
WHEN status = 'Processing' THEN 'PROC'
WHEN status = 'Completed' THEN 'COMP'
WHEN status = 'Pending Approval' THEN 'PEND'
WHEN status = 'Shipped' THEN 'SHIP'
ELSE status
END AS Abbreviation
FROM Statuses;
Output:
id Abbreviation
1 PROC
2 COMP
3 PEND
4 SHIP
Explanation:
CASE replaces long status names with abbreviations.
________________________________________
61-80: Performance Optimization & Validation
Need examples with sample data and T-SQL query, output, and explanation, for each query separately.
61. Use indexes on transformed columns for faster joins (SUBSTRING, LOWER).
62. Identify and remove redundant prefixes (LEFT, CASE).
63. Detect and remove emojis from text (REGEXP_REPLACE).
64. Extract numerical part from an alphanumeric field (REGEXP_SUBSTR).
65. Find customers with duplicate email addresses (LOWER, TRIM).
66. Replace missing area codes with a default value (CASE).
67. Identify addresses containing P.O. Boxes (LIKE).
68. Extract month names from date strings (TO_CHAR).
69. Transform mixed-case names to title case (INITCAP).
70. Mask credit card numbers except last 4 digits (LPAD, RIGHT).
71. Validate that a column contains only alphabets (REGEXP_LIKE).
72. Ensure data consistency by removing unwanted words (REPLACE).
73. Identify and flag duplicate SKUs (COUNT, GROUP BY).
74. Extract department names from email aliases (SPLIT_PART).
75. Detect non-standard characters in names (REGEXP_LIKE).
76. Format timestamps for consistency (TO_CHAR).
77. Ensure postal codes follow a fixed length (LPAD, RPAD).
78. Convert long descriptions to abbreviations (CASE, REPLACE).
79. Remove double quotes from CSV imports (REPLACE).
80. Validate if a value follows a specific regex pattern (REGEXP_LIKE).
________________________________________
61. Use Indexes on Transformed Columns for Faster Joins
(SUBSTRING, LOWER)
Create Tables & Insert Data:
CREATE TABLE Customers (id INT IDENTITY PRIMARY KEY, email NVARCHAR(100));
CREATE TABLE Orders (id INT IDENTITY PRIMARY KEY, customer_email NVARCHAR(100));

INSERT INTO Customers (email) VALUES

('[Link]@[Link]'), ('[Link]@[Link]'), ('[Link]@[Link]');

INSERT INTO Orders (customer_email) VALUES

('[Link]@[Link]'), ('[Link]@[Link]'), ('[Link]@[Link]');
Create Indexed Column for Faster Joins:
CREATE INDEX idx_lower_email ON Customers (LOWER(email));
Query:
SELECT [Link], [Link], [Link] AS order_id
FROM Customers c
JOIN Orders o
ON LOWER([Link]) = LOWER(o.customer_email);
Output:
id email order_id
1 [Link]@[Link] 1
2 [Link]@[Link] 2
3 [Link]@example.com3
Explanation:
LOWER(email) ensures case-insensitive matching.
CREATE INDEX idx_lower_email speeds up joins on transformed columns.
________________________________________
62. Identify and Remove Redundant Prefixes (LEFT, CASE)
Create Table & Insert Data:
CREATE TABLE Products (id INT IDENTITY, product_code NVARCHAR(20));

INSERT INTO Products (product_code) VALUES

('NEW-1234'), ('OLD-5678'), ('NEW-9012'), ('STD-3456');
Query:
SELECT id,
product_code AS OriginalCode,
CASE
WHEN LEFT(product_code, 4) IN ('NEW-', 'OLD-') THEN SUBSTRING(product_code, 5, LEN(product_code))
ELSE product_code
END AS CleanedCode
FROM Products;
Output:
id OriginalCode CleanedCode
1 NEW-1234 1234
2 OLD-5678 5678
3 NEW-9012 9012
4 STD-3456 STD-3456
Explanation:
LEFT(product_code, 4) checks for redundant prefixes.
SUBSTRING(product_code, 5, LEN(product_code)) removes them.
________________________________________
63. Detect and Remove Emojis from Text (REGEXP_REPLACE)
Create Table & Insert Data:
CREATE TABLE Messages (id INT IDENTITY, text NVARCHAR(255));

INSERT INTO Messages (text) VALUES

('Hello 😊, how are you?'),
('Great job! 🎉👍'),
('No emojis here.');
Query:
SELECT id, REGEXP_REPLACE(text, '[\x{1F600}-\x{1F64F}]', '', 1, 0, 'n') AS CleanedText
FROM Messages;
Output:
id CleanedText
1 Hello , how are you?
2 Great job!
3 No emojis here.
Explanation:
[\x{1F600}-\x{1F64F}] matches common emoji Unicode ranges.
REGEXP_REPLACE removes them.
________________________________________
64. Extract Numerical Part from an Alphanumeric Field
(REGEXP_SUBSTR)
Create Table & Insert Data:
CREATE TABLE Items (id INT IDENTITY, item_code NVARCHAR(50));

INSERT INTO Items (item_code) VALUES

('ABC123'), ('XYZ-4567'), ('Product789'), ('NoNumbers');
Query:
SELECT id, item_code, REGEXP_SUBSTR(item_code, '\d+') AS ExtractedNumber
FROM Items;
Output:
id item_code ExtractedNumber
1 ABC123 123
2 XYZ-4567 4567
3 Product789 789
4 NoNumbers NULL
Explanation:
\d+ extracts numeric values from alphanumeric strings.
Returns NULL if no numbers are found.
________________________________________
65. Find Customers with Duplicate Email Addresses (LOWER,
TRIM)
Create Table & Insert Data:
CREATE TABLE Customers (id INT IDENTITY, email NVARCHAR(100));

INSERT INTO Customers (email) VALUES

('[Link]@[Link] '),
('[Link]@[Link]'),
(' [Link]@[Link]'),
('[Link]@[Link]');
Query:
SELECT LOWER(TRIM(email)) AS NormalizedEmail, COUNT(*) AS DuplicateCount
FROM Customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;
Output:
NormalizedEmail DuplicateCount
[Link]@[Link] 2
[Link]@[Link] 2
Explanation:
LOWER(TRIM(email)) ensures case-insensitive and whitespace-trimmed matching.
HAVING COUNT(*) > 1 filters duplicates.
________________________________________
66. Replace Missing Area Codes with a Default Value (CASE)
Create Table & Insert Data:
CREATE TABLE PhoneNumbers (id INT IDENTITY, phone NVARCHAR(20));

INSERT INTO PhoneNumbers (phone) VALUES

('5551234'), ('+1 9876543210'), ('6667778888'), ('+44 2034567890');
Query:
SELECT id,
CASE
WHEN phone LIKE '+%' THEN phone
ELSE CONCAT('+1 ', phone)
END AS NormalizedPhone
FROM PhoneNumbers;
Output:
id NormalizedPhone
1 +1 5551234
2 +1 9876543210
3 +1 6667778888
4 +44 2034567890
Explanation:
Adds +1 if no country code is present.
________________________________________
67. Identify Addresses Containing P.O. Boxes (LIKE)
Create Table & Insert Data:
CREATE TABLE Addresses (id INT IDENTITY, address NVARCHAR(255));

INSERT INTO Addresses (address) VALUES

('123 Main St'), ('P.O. Box 456'), ('500 Elm St, Apt 3'), ('PO BOX 789');
Query:
SELECT id, address
FROM Addresses
WHERE address LIKE '%P.O. Box%' OR address LIKE '%PO BOX%';
Output:
id address
2 P.O. Box 456
4 PO BOX 789
Explanation:
Finds addresses containing "P.O. Box" or "PO BOX".
________________________________________
68. Extract Month Names from Date Strings (TO_CHAR)
Create Table & Insert Data:
CREATE TABLE Orders (id INT IDENTITY, order_date DATE);

INSERT INTO Orders (order_date) VALUES

('2024-01-15'), ('2024-06-22'), ('2024-12-10');
Query:
SELECT id, order_date, TO_CHAR(order_date, 'Month') AS MonthName
FROM Orders;
Output:
id order_date MonthName
1 2024-01-15 January
2 2024-06-22 June
3 2024-12-10 December
Explanation:
TO_CHAR(order_date, 'Month') extracts month names.
________________________________________
69. Transform Mixed-Case Names to Title Case (INITCAP)
Create Table & Insert Data:
CREATE TABLE Employees (id INT IDENTITY, name NVARCHAR(100));

INSERT INTO Employees (name) VALUES

('jOhN doE'), ('JANE smith'), ('michael JOHNSON');
Query:
SELECT id, name, INITCAP(name) AS TitleCasedName
FROM Employees;
Output:
id name TitleCasedName
1 jOhN doE John Doe
2 JANE smith Jane Smith
3 michael JOHNSON Michael Johnson
Explanation:
INITCAP(name) capitalizes each word correctly.
________________________________________
70. Mask Credit Card Numbers Except Last 4 Digits (LPAD, RIGHT)
Create Table & Insert Data:
CREATE TABLE Payments (id INT IDENTITY, card_number NVARCHAR(16));

INSERT INTO Payments (card_number) VALUES

('1234567812345678'), ('9876543210987654');
Query:
SELECT id,
CONCAT('****-****-****-', RIGHT(card_number, 4)) AS MaskedCard
FROM Payments;
Output:
id MaskedCard
1 --****-5678
2 --****-7654
Explanation:
RIGHT(card_number, 4) extracts the last 4 digits.
Hides other digits using ****.
________________________________________
71. Validate That a Column Contains Only Alphabets
(REGEXP_LIKE)
Create Table & Insert Data:
CREATE TABLE Names (id INT IDENTITY, full_name NVARCHAR(100));

INSERT INTO Names (full_name) VALUES

('JohnDoe'), ('Jane123'), ('Michael Johnson'), ('Alice_Wonderland');
Query:
SELECT id, full_name,
CASE
WHEN REGEXP_LIKE(full_name, '^[A-Za-z ]+$') THEN 'Valid'
ELSE 'Invalid'
END AS NameValidity
FROM Names;
Output:
id full_name NameValidity
1 JohnDoe Valid
2 Jane123 Invalid
3 Michael Johnson Valid
4 Alice_Wonderland Invalid
Explanation:
^[A-Za-z ]+$ ensures only letters and spaces are allowed.
Numbers or special characters make it invalid.
________________________________________
72. Ensure Data Consistency by Removing Unwanted Words
(REPLACE)
Create Table & Insert Data:
CREATE TABLE Descriptions (id INT IDENTITY, product_description NVARCHAR(255));

INSERT INTO Descriptions (product_description) VALUES

('Best quality product'), ('Limited time offer'), ('New arrival - best price');
Query:
SELECT id,
REPLACE(REPLACE(product_description, 'best', ''), 'Limited', '') AS CleanedDescription
FROM Descriptions;
Output:
id CleanedDescription
1 quality product
2 time offer
3 New arrival - price
Explanation:
Uses REPLACE to remove unwanted marketing words.
________________________________________
73. Identify and Flag Duplicate SKUs (COUNT, GROUP BY)
Create Table & Insert Data:
CREATE TABLE Products (id INT IDENTITY, sku NVARCHAR(50));

INSERT INTO Products (sku) VALUES

('A123'), ('B456'), ('A123'), ('C789'), ('B456');
Query:
SELECT sku, COUNT(*) AS DuplicateCount
FROM Products
GROUP BY sku
HAVING COUNT(*) > 1;
Output:
sku DuplicateCount
A123 2
B456 2
Explanation:
Groups by SKU and counts occurrences.
Shows only SKUs appearing more than once.
________________________________________
74. Extract Department Names from Email Aliases (SPLIT_PART)
Create Table & Insert Data:
CREATE TABLE Employees (id INT IDENTITY, email NVARCHAR(100));
INSERT INTO Employees (email) VALUES
('[Link]@[Link]'), ('[Link]@[Link]'), ('[Link]@[Link]');
Query:
SELECT id, email, SPLIT_PART(email, '.', 2) AS Department
FROM Employees;
Output:
id email Department
1 [Link]@[Link] sales
2 [Link]@[Link] hr
3 [Link]@[Link] it
Explanation:
SPLIT_PART(email, '.', 2) extracts the department name from the email.
________________________________________
75. Detect Non-Standard Characters in Names (REGEXP_LIKE)
Create Table & Insert Data:
CREATE TABLE CustomerNames (id INT IDENTITY, name NVARCHAR(100));

INSERT INTO CustomerNames (name) VALUES

('John Doe'), ('J@ne'), ('Michael123'), ('Carlos_Mendez');
Query:
SELECT id, name,
CASE
WHEN REGEXP_LIKE(name, '[^A-Za-z ]') THEN 'Contains Non-Standard Characters'
ELSE 'Valid'
END AS NameCheck
FROM CustomerNames;
Output:
id name NameCheck
1 John Doe Valid
2 J@ne Contains Non-Standard Characters
3 Michael123 Contains Non-Standard Characters
4 Carlos_Mendez Contains Non-Standard Characters
Explanation:
Detects names with numbers or special characters.
________________________________________
76. Format Timestamps for Consistency (TO_CHAR)
Create Table & Insert Data:
CREATE TABLE Logs (id INT IDENTITY, log_time DATETIME);

INSERT INTO Logs (log_time) VALUES

('2025-03-26 14:30:15'), ('2025-03-26 08:15:45');
Query:
SELECT id, log_time, TO_CHAR(log_time, 'YYYY-MM-DD HH24:MI:SS') AS FormattedTime
FROM Logs;
Output:
id log_time FormattedTime
1 2025-03-26 14:30:15 2025-03-26 14:30:15
2 2025-03-26 08:15:45 2025-03-26 08:15:45
Explanation:
TO_CHAR(log_time, 'YYYY-MM-DD HH24:MI:SS') ensures a standard format.
________________________________________
77. Ensure Postal Codes Follow a Fixed Length (LPAD, RPAD)
Create Table & Insert Data:
CREATE TABLE Addresses (id INT IDENTITY, postal_code NVARCHAR(10));

INSERT INTO Addresses (postal_code) VALUES

('123'), ('45678'), ('9876');
Query:
SELECT id, postal_code,
LPAD(postal_code, 5, '0') AS StandardizedPostalCode
FROM Addresses;
Output:
id postal_code StandardizedPostalCode
1 123 00123
2 45678 45678
3 9876 09876
Explanation:
LPAD(postal_code, 5, '0') ensures postal codes are at least 5 digits.
________________________________________
78. Convert Long Descriptions to Abbreviations (CASE, REPLACE)
Create Table & Insert Data:
CREATE TABLE Products (id INT IDENTITY, description NVARCHAR(255));

INSERT INTO Products (description) VALUES

('Limited Edition Product'), ('High-Quality Material'), ('Exclusive Offer');
Query:
SELECT id, description,
REPLACE(REPLACE(description, 'Limited Edition', 'LE'), 'High-Quality', 'HQ') AS AbbreviatedDescription
FROM Products;
Output:
id description AbbreviatedDescription
1 Limited Edition Product LE Product
2 High-Quality Material HQ Material
3 Exclusive Offer Exclusive Offer
Explanation:
Uses REPLACE to convert common phrases to abbreviations.
________________________________________
79. Remove Double Quotes from CSV Imports (REPLACE)
Create Table & Insert Data:
CREATE TABLE CSVData (id INT IDENTITY, raw_text NVARCHAR(255));

INSERT INTO CSVData (raw_text) VALUES

('"John,Doe,30"'), ('"Jane,Smith,25"');
Query:
SELECT id, REPLACE(raw_text, '"', '') AS CleanedText
FROM CSVData;
Output:
id CleanedText
1 John,Doe,30
2 Jane,Smith,25
Explanation:
REPLACE(raw_text, '"', '') removes double quotes.
________________________________________
80. Validate If a Value Follows a Specific Regex Pattern
(REGEXP_LIKE)
Create Table & Insert Data:
CREATE TABLE Usernames (id INT IDENTITY, username NVARCHAR(50));

INSERT INTO Usernames (username) VALUES

('john_doe123'), ('JaneDoe!'), ('mike-2024'), ('ValidUser99');
Query:
SELECT id, username,
CASE
WHEN REGEXP_LIKE(username, '^[a-zA-Z0-9_]+$') THEN 'Valid'
ELSE 'Invalid'
END AS UsernameValidity
FROM Usernames;
Output:
id username UsernameValidity
1 john_doe123 Valid
2 JaneDoe! Invalid
3 mike-2024 Invalid
4 ValidUser99 Valid
Explanation:
^[a-zA-Z0-9_]+$ allows only letters, numbers, and underscores.
Entries with special characters (!, -) are marked invalid.
________________________________________
81-100: Complex Business Logic Implementations
Need examples with sample data and T-SQL query, output, and explanation, for each query separately.
81. Generate user-friendly URLs from product names (LOWER, REPLACE).
82. Extract city names from full addresses (SUBSTRING, CHARINDEX).
83. Identify transactions with unusual character patterns (REGEXP_LIKE).
84. Format MAC addresses for consistency (REPLACE).
85. Normalize multiple date formats to one standard (CASE, TO_DATE).
86. Extract age from date of birth (DATEDIFF).
87. Transform numeric values to words (CASE).
88. Validate hexadecimal color codes (REGEXP_LIKE).
89. Extract the company name from email domains (SPLIT_PART).
90. Identify records with excessive character length (LENGTH).
91. Ensure hashtags in tweets are properly formatted (LIKE).
92. Extract words before a specific delimiter (SPLIT_PART).
93. Remove redundant words from customer reviews (REPLACE).
94. Identify special offer codes from product descriptions (REGEXP_SUBSTR).
95. Ensure all addresses end with a valid suffix (LIKE).
96. Transform unstructured notes into structured fields (REGEXP_SUBSTR).
97. Standardize formatting of part numbers (LPAD, RPAD).
98. Remove ASCII control characters from data (REGEXP_REPLACE).
99. Identify and correct common OCR errors (REPLACE).
100. Ensure product titles have a minimum word count (LENGTH, SPLIT_PART).
________________________________________
81. Generate User-Friendly URLs from Product Names (LOWER,
REPLACE)
Create Table & Insert Data:
CREATE TABLE Products (id INT IDENTITY, product_name NVARCHAR(100));
INSERT INTO Products (product_name) VALUES
('Wireless Headphones'), ('4K Smart TV'), ('Gaming Mouse');
Query:
SELECT id, product_name,
LOWER(REPLACE(REPLACE(product_name, ' ', '-'), '4K', '4k')) AS url_slug
FROM Products;
Output:
id product_name url_slug
1 Wireless Headphones wireless-headphones
2 4K Smart TV 4k-smart-tv
3 Gaming Mouse gaming-mouse
Explanation:
Converts product names to lowercase using LOWER.
Replaces spaces with - for URL-friendly formatting.
________________________________________
82. Extract City Names from Full Addresses (SUBSTRING,
CHARINDEX)
Create Table & Insert Data:
CREATE TABLE Addresses (id INT IDENTITY, full_address NVARCHAR(255));

INSERT INTO Addresses (full_address) VALUES

('123 Main St, Los Angeles, CA 90001'), ('456 Oak St, New York, NY 10001');
Query:
SELECT id, full_address,
SUBSTRING(full_address, CHARINDEX(',', full_address) + 2,
CHARINDEX(',', full_address, CHARINDEX(',', full_address) + 1) - CHARINDEX(',', full_address) - 2) AS city
FROM Addresses;
Output:
id full_address city
1 123 Main St, Los Angeles, CA 90001 Los Angeles
2 456 Oak St, New York, NY 10001 New York
Explanation:
CHARINDEX finds the positions of the commas.
SUBSTRING extracts the city name between commas.
________________________________________
83. Identify Transactions with Unusual Character Patterns
(REGEXP_LIKE)
Create Table & Insert Data:
CREATE TABLE Transactions (id INT IDENTITY, transaction_code NVARCHAR(50));

INSERT INTO Transactions (transaction_code) VALUES

('ABC1234'), ('X$%^789'), ('1234567');
Query:
SELECT id, transaction_code,
CASE
WHEN REGEXP_LIKE(transaction_code, '[^A-Za-z0-9]') THEN 'Unusual Pattern'
ELSE 'Normal'
END AS Status
FROM Transactions;
Output:
id transaction_code Status
1 ABC1234 Normal
2 X$%^789 Unusual Pattern
3 1234567 Normal
Explanation:
Detects transactions with special characters using REGEXP_LIKE.
________________________________________
84. Format MAC Addresses for Consistency (REPLACE)
Create Table & Insert Data:
CREATE TABLE Devices (id INT IDENTITY, mac_address NVARCHAR(50));

INSERT INTO Devices (mac_address) VALUES

('A1-B2-C3-D4-E5-F6'), ('A1B2C3D4E5F6'), ('A1:B2:C3:D4:E5:F6');
Query:
SELECT id, mac_address,
UPPER(STUFF(STUFF(STUFF(STUFF(STUFF(mac_address, 3, 0, ':'), 6, 0, ':'), 9, 0, ':'), 12, 0, ':'), 15, 0, ':')) AS
formatted_mac
FROM Devices;
Output:
id mac_address formatted_mac
1 A1-B2-C3-D4-E5-F6 A1:B2:C3:D4:E5:F6
2 A1B2C3D4E5F6 A1:B2:C3:D4:E5:F6
3 A1:B2:C3:D4:E5:F6 A1:B2:C3:D4:E5:F6
Explanation:
Ensures a consistent :-separated MAC address format using STUFF and UPPER.
________________________________________
85. Normalize Multiple Date Formats to One Standard (CASE,
TO_DATE)
Create Table & Insert Data:
CREATE TABLE Orders (id INT IDENTITY, order_date NVARCHAR(50));

INSERT INTO Orders (order_date) VALUES

('03/26/2025'), ('26-03-2025'), ('2025.03.26');
Query:
SELECT id, order_date,
CASE
WHEN order_date LIKE '%/%' THEN CONVERT(DATE, order_date, 101)
WHEN order_date LIKE '%-%' THEN CONVERT(DATE, order_date, 105)
ELSE CONVERT(DATE, order_date, 102)
END AS standardized_date
FROM Orders;
Output:
id order_date standardized_date
1 03/26/2025 2025-03-26
2 26-03-2025 2025-03-26
3 2025.03.26 2025-03-26
Explanation:
Uses CASE to detect different date formats and convert them using TO_DATE.
________________________________________
86. Extract Age from Date of Birth (DATEDIFF)
Create Table & Insert Data:
CREATE TABLE People (id INT IDENTITY, dob DATE);

INSERT INTO People (dob) VALUES

('1990-06-15'), ('1985-09-22'), ('2000-01-10');
Query:
SELECT id, dob, DATEDIFF(YEAR, dob, GETDATE()) AS age
FROM People;
Output:
id dob age
1 1990-06-15 34
2 1985-09-22 39
3 2000-01-10 25
Explanation:
Uses DATEDIFF(YEAR, dob, GETDATE()) to calculate age.
________________________________________
87. Transform Numeric Values to Words (CASE)
Create Table & Insert Data:
CREATE TABLE Numbers (id INT IDENTITY, num INT);

INSERT INTO Numbers (num) VALUES

(1), (2), (3), (4);
Query:
SELECT id, num,
CASE num
WHEN 1 THEN 'One'
WHEN 2 THEN 'Two'
WHEN 3 THEN 'Three'
ELSE 'Other'
END AS num_in_words
FROM Numbers;
Output:
id num num_in_words
1 1 One
2 2 Two
3 3 Three
4 4 Other
Explanation:
Converts numeric values into words using CASE.
________________________________________
88. Validate Hexadecimal Color Codes (REGEXP_LIKE)
Create Table & Insert Data:
CREATE TABLE Colors (id INT IDENTITY, hex_code NVARCHAR(10));

INSERT INTO Colors (hex_code) VALUES

('#FF5733'), ('123ABC'), ('#XYZ123'), ('#000');
Query:
SELECT id, hex_code,
CASE
WHEN REGEXP_LIKE(hex_code, '^#?[A-Fa-f0-9]{6}$') THEN 'Valid'
ELSE 'Invalid'
END AS hex_validity
FROM Colors;
Output:
id hex_code hex_validity
1 #FF5733 Valid
2 123ABC Invalid
3 #XYZ123 Invalid
4 #000 Invalid
Explanation:
Checks for valid hex colors with REGEXP_LIKE.
________________________________________
89. Extract the Company Name from Email Domains (SPLIT_PART)
Create Table & Insert Data:
CREATE TABLE Employees (id INT IDENTITY, email NVARCHAR(100));

INSERT INTO Employees (email) VALUES

('[Link]@[Link]'), ('[Link]@[Link]'), ('[Link]@[Link]');
Query:
SELECT id, email, SPLIT_PART(email, '@', 2) AS company_domain
FROM Employees;
Output:
id email company_domain
1 [Link]@[Link] [Link]
2 [Link]@[Link] [Link]
3 [Link]@[Link] [Link]
Explanation:
Extracts the domain after @ using SPLIT_PART.
________________________________________
90. Identify Records with Excessive Character Length (LENGTH)
Create Table & Insert Data:
CREATE TABLE Comments (id INT IDENTITY, review NVARCHAR(255));

INSERT INTO Comments (review) VALUES

('Great product!'),
('This is an exceptionally long review that exceeds normal expectations.');
Query:
SELECT id, review,
CASE
WHEN LEN(review) > 50 THEN 'Excessive Length'
ELSE 'Normal'
END AS length_status
FROM Comments;
Output:
id review length_status
1 Great product! Normal
2 This is an exceptionally long review... Excessive Length
Explanation:
Flags records with excessive length using LEN.
________________________________________
91. Ensure Hashtags in Tweets Are Properly Formatted (LIKE)
Create Table & Insert Data:
CREATE TABLE Tweets (id INT IDENTITY, tweet_text NVARCHAR(255));

INSERT INTO Tweets (tweet_text) VALUES

('#TechNews is trending!'), ('No hashtag here.'), ('#123Invalid'), ('#ValidHashtag');
Query:
SELECT id, tweet_text,
CASE
WHEN tweet_text LIKE '%#%' AND tweet_text LIKE '%#[A-Za-z]%' THEN 'Valid Hashtag'
 ELSE 'Invalid Hashtag'
END AS hashtag_status
FROM Tweets;
Output:
id tweet_text hashtag_status
1 #TechNews is trending! Valid Hashtag
2 No hashtag here. Invalid Hashtag
3 #123Invalid Invalid Hashtag
4 #ValidHashtag Valid Hashtag
Explanation:
Ensures hashtags start with # and contain letters.
________________________________________
92. Extract Words Before a Specific Delimiter (SPLIT_PART)
Create Table & Insert Data:
CREATE TABLE FileNames (id INT IDENTITY, file_name NVARCHAR(255));

INSERT INTO FileNames (file_name) VALUES

('report_2024.csv'), ('invoice_123.pdf'), ('[Link]');
Query:
SELECT id, file_name, SPLIT_PART(file_name, '_', 1) AS extracted_word
FROM FileNames;
Output:
id file_name extracted_word
1 report_2024.csv report
2 invoice_123.pdfinvoice
3 [Link] [Link]
Explanation:
Extracts text before _ using SPLIT_PART.
________________________________________
93. Remove Redundant Words from Customer Reviews (REPLACE)
Create Table & Insert Data:
CREATE TABLE Reviews (id INT IDENTITY, review_text NVARCHAR(255));

INSERT INTO Reviews (review_text) VALUES

('This product is very very good!'), ('Absolutely love this this product.');
Query:
SELECT id, review_text,
REPLACE(REPLACE(review_text, 'very very', 'very'), 'this this', 'this') AS cleaned_review
FROM Reviews;
Output:
id review_text cleaned_review
1 This product is very very good! This product is very good!
2 Absolutely love this this product. Absolutely love this product.
Explanation:
Removes redundant words using REPLACE.
________________________________________
94. Identify Special Offer Codes from Product Descriptions
(REGEXP_SUBSTR)
Create Table & Insert Data:
CREATE TABLE Products (id INT IDENTITY, description NVARCHAR(255));

INSERT INTO Products (description) VALUES

('Buy now and use code SAVE20!'), ('Limited offer: DISCOUNT50 available.');
Query:
SELECT id, description,
REGEXP_SUBSTR(description, '[A-Z]+[0-9]+') AS offer_code
FROM Products;
Output:
id description offer_code
1 Buy now and use code SAVE20! SAVE20
2 Limited offer: DISCOUNT50 available. DISCOUNT50
Explanation:
Extracts offer codes with uppercase letters followed by numbers.
________________________________________
95. Ensure All Addresses End with a Valid Suffix (LIKE)
Create Table & Insert Data:
CREATE TABLE Addresses (id INT IDENTITY, address NVARCHAR(255));

INSERT INTO Addresses (address) VALUES

('123 Main St'), ('456 Oak Avenue'), ('789 Elm Road');
Query:
SELECT id, address,
CASE
WHEN address LIKE '%Street' OR address LIKE '%Avenue' OR address LIKE '%Road' THEN 'Valid'
ELSE 'Invalid'
END AS address_status
FROM Addresses;
Output:
id address address_status
1 123 Main St Invalid
2 456 Oak Avenue Valid
3 789 Elm Road Valid
Explanation:
Ensures addresses end with valid suffixes.
________________________________________
96. Transform Unstructured Notes into Structured Fields
(REGEXP_SUBSTR)
Create Table & Insert Data:
CREATE TABLE Notes (id INT IDENTITY, note_text NVARCHAR(255));

INSERT INTO Notes (note_text) VALUES

('Order#12345 placed on 2024-03-25'), ('Ref#67890 processed on 2024-03-20');
Query:
SELECT id, note_text,
REGEXP_SUBSTR(note_text, 'Order#\d+') AS order_number
FROM Notes;
Output:
id note_text order_number
1 Order#12345 placed on 2024-03-25 Order#12345
2 Ref#67890 processed on 2024-03-20 NULL
Explanation:
Extracts order numbers prefixed with Order#.
________________________________________
97. Standardize Formatting of Part Numbers (LPAD, RPAD)
Create Table & Insert Data:
CREATE TABLE Parts (id INT IDENTITY, part_number NVARCHAR(20));

INSERT INTO Parts (part_number) VALUES

('1234'), ('56789');
Query:
SELECT id, part_number,
LPAD(part_number, 6, '0') AS standardized_part
FROM Parts;
Output:
id part_number standardized_part
1 1234 001234
2 56789 056789
Explanation:
Ensures part numbers are at least 6 digits by padding with 0.
________________________________________
98. Remove ASCII Control Characters from Data
(REGEXP_REPLACE)
Create Table & Insert Data:
CREATE TABLE Messages (id INT IDENTITY, text_content NVARCHAR(255));

INSERT INTO Messages (text_content) VALUES

('Hello! Welcome\x0Ato our store.');
Query:
SELECT id, text_content,
REGEXP_REPLACE(text_content, '[\x00-\x1F\x7F]', '') AS cleaned_text
FROM Messages;
Output:
id text_content cleaned_text
1 Hello! Welcome\x0Ato our store. Hello! Welcome to our store.
Explanation:
Removes ASCII control characters (\x00-\x1F\x7F).
________________________________________
99. Identify and Correct Common OCR Errors (REPLACE)
Create Table & Insert Data:
CREATE TABLE OCRData (id INT IDENTITY, scanned_text NVARCHAR(255));

INSERT INTO OCRData (scanned_text) VALUES

('1nvoice Number: 12345'), ('0rder 56789');
Query:
SELECT id, scanned_text,
REPLACE(REPLACE(scanned_text, '1nvoice', 'Invoice'), '0rder', 'Order') AS corrected_text
FROM OCRData;
Output:
id scanned_text corrected_text
1 1nvoice Number: 12345Invoice Number: 12345
2 0rder 56789 Order 56789
Explanation:
Corrects common OCR errors using REPLACE.
________________________________________
100. Ensure Product Titles Have a Minimum Word Count (LENGTH,
SPLIT_PART)
Create Table & Insert Data:
CREATE TABLE Products (id INT IDENTITY, product_title NVARCHAR(255));

INSERT INTO Products (product_title) VALUES

('Wireless Mouse'), ('Ultra HD Smart TV'), ('Book');
Query:
SELECT id, product_title,
CASE
WHEN (LEN(product_title) - LEN(REPLACE(product_title, ' ', '')) + 1) >= 2
THEN 'Valid'
ELSE 'Invalid'
END AS title_status
FROM Products;
Output:
id product_title title_status
1 Wireless MouseValid
2 Ultra HD Smart TV Valid
3 Book Invalid
Explanation:
Counts spaces in product_title to determine the word count.
Ensures at least two words in the title.