21. String Functions -- Manipulating Text in SQL Server

Level: Beginner

ℹ️ What You'll Learn

• LEN -- string length
• SUBSTRING, LEFT, RIGHT -- extract parts
• UPPER, LOWER -- case conversion
• REPLACE -- find and replace text
• TRIM, LTRIM, RTRIM -- remove spaces
• CONCAT -- join strings
• CHARINDEX, PATINDEX -- find position
• Common string mistakes
• Best practices

Query data as-is. APIs transform text. String functions manipulate in database. This article teaches text operations for reporting and display.

Why String Functions Matter for Backend Developers

APIs need formatted data:

Frontend asks: "Display student initials and class level"
    v
Backend needs:
- Extract initials from Name (Ravi Kumar -> R.K.)
- Extract class number from ClassName (10-A -> 10)
    v
String functions transform in SQL
    v
Return formatted data to frontend

Transform text in database, not backend code.

String Function Reference

LEN(string)                    -> Length
SUBSTRING(string, start, len)  -> Extract part
LEFT(string, n)                -> First n chars
RIGHT(string, n)               -> Last n chars
UPPER(string)                  -> Uppercase
LOWER(string)                  -> Lowercase
REPLACE(string, old, new)      -> Find/replace
LTRIM(string)                  -> Remove left spaces
RTRIM(string)                  -> Remove right spaces
TRIM(string)                   -> Remove both sides
CONCAT(str1, str2, ...)        -> Join strings
CHARINDEX(find, string)        -> Position

Example 1: Extract Student Initials

Get student initials:

SELECT 
    Name AS FullName,
    LEFT(Name, 1) + '.' + 
    SUBSTRING(Name, CHARINDEX(' ', Name) + 1, 1) + '.' AS Initials
FROM Student;

Result (initials):

FullName     | Initials
Ravi Kumar   | R.K.
Priya Sharma | P.S.
Arjun Reddy  | A.R.

LEFT: First char. CHARINDEX: Find space position. SUBSTRING: First char after space.

Example 2: Extract Class Number

Get class level from class name:

SELECT 
    ClassName,
    LEFT(ClassName, CHARINDEX('-', ClassName) - 1) AS ClassLevel,
    RIGHT(ClassName, 1) AS Section
FROM Class;

Result (class parts):

ClassName | ClassLevel | Section
10-A      | 10         | A
10-B      | 10         | B
11-A      | 11         | A

CHARINDEX finds hyphen. LEFT extracts before it. RIGHT gets last char.

Example 3: Search Names (LIKE vs CHARINDEX)

Find all students with "Kumar" in name:

SELECT 
    StudentId,
    Name
FROM Student
WHERE CHARINDEX('Kumar', Name) > 0;

Result (Kumar students):

StudentId | Name
1         | Ravi Kumar
5         | Ashok Kumar

CHARINDEX > 0: Found. = 0: Not found.

Alternative with LIKE:

WHERE Name LIKE '%Kumar%';

Both work. CHARINDEX more explicit.

Example 4: Format Names (Case conversion)

Display formatted names:

SELECT 
    StudentId,
    UPPER(Name) AS NameUpper,
    LOWER(Name) AS NameLower,
    UPPER(LEFT(Name, 1)) + LOWER(SUBSTRING(Name, 2, LEN(Name))) AS ProperCase
FROM Student;

Result (formatted):

StudentId | NameUpper   | NameLower   | ProperCase
1         | RAVI KUMAR  | ravi kumar  | Ravi kumar
2         | PRIYA SHARMA| priya sharma| Priya sharma

UPPER/LOWER transform. Combine to create proper case formatting.

Example 5: REPLACE -- Fix Data

Replace typo in subject names:

UPDATE Subject
SET Name = REPLACE(Name, 'Phisics', 'Physics');

SELECT Name FROM Subject;

Result (corrected):

Name
Physics
Chemistry
English

REPLACE finds "Phisics", replaces with "Physics".

Example 6: CONCAT -- Build Strings

Create full address:

SELECT 
    Name,
    CONCAT(Address, ', ', City, ', ', State, ' - ', PinCode) AS FullAddress
FROM School;

Result (concatenated):

Name            | FullAddress
NexCoding Academy | 123 Main St, Bangalore, Karnataka - 560001

CONCAT joins strings with delimiters.

Alternative (+ operator):

SELECT 
    Name,
    Address + ', ' + City + ', ' + State + ' - ' + PinCode AS FullAddress
FROM School;

Same result. CONCAT safer with NULL (treats as empty).

Example 7: TRIM -- Clean Data

Remove extra spaces:

SELECT 
    Name,
    TRIM(Name) AS NameTrimmed,
    LEN(Name) AS OriginalLen,
    LEN(TRIM(Name)) AS TrimmedLen
FROM Student;

Result (spaces removed):

Name          | NameTrimmed | OriginalLen | TrimmedLen
"  Ravi  "    | Ravi        | 9           | 4
"Priya "      | Priya       | 6           | 5

TRIM removes leading/trailing spaces. LTRIM: left only. RTRIM: right only.

Example 8: String Validation

Check email format:

SELECT 
    Email,
    CASE 
        WHEN CHARINDEX('@', Email) > 0 
             AND CHARINDEX('.', Email, CHARINDEX('@', Email)) > 0 
        THEN 'Valid'
        ELSE 'Invalid'
    END AS EmailStatus
FROM User;

Result (validation):

Email                        | EmailStatus
nagarjuna.thota@outlook.com | Valid
invalid.email.com           | Invalid

Check @ exists and . after @. Basic validation.

---

Common String Function Mistakes

Mistake 1: Ignoring NULL in string functions

Wrong:

SELECT CONCAT(FirstName, ' ', LastName) FROM User;
-- If LastName is NULL: result is NULL (CONCAT ignores NULLs)

Result: NULL instead of partial name.

Fix: Handle NULL:

SELECT 
    CONCAT(FirstName, ' ', ISNULL(LastName, '')) AS FullName
FROM User;

Or use + (must ISNULL both):

SELECT 
    ISNULL(FirstName, '') + ' ' + ISNULL(LastName, '') AS FullName
FROM User;

Mistake 2: SUBSTRING with wrong position (1-based, not 0)

Wrong:

SELECT SUBSTRING('Ravi Kumar', 0, 4);
-- Expected: 'Ravi'
-- Actual: 'Rav' (position 0 treated as 1)

Result: Off-by-one error.

Fix: Use 1-based indexing:

SELECT SUBSTRING('Ravi Kumar', 1, 4);  -- Correct: 'Ravi'

SQL uses 1-based indexing, not 0-based like programming languages.

Mistake 3: CHARINDEX returns 0 if not found

Wrong:

SELECT CHARINDEX('X', 'Ravi Kumar');
-- Returns 0 (not found)

WHERE CHARINDEX('Kumar', Name) = 1;
-- Always false: first char never contains 'Kumar'

Result: Logic error.

Fix: Compare > 0:

WHERE CHARINDEX('Kumar', Name) > 0;  -- Correct: found

Mistake 4: LEN doesn't count trailing spaces

Wrong:

SELECT LEN('Ravi  ');
-- Returns 4 (trailing spaces ignored)
-- Need to RTRIM to see actual string

Result: Hidden trailing spaces.

Fix: Use DATALENGTH if spaces matter:

SELECT DATALENGTH('Ravi  ');  -- Returns 6 (with spaces)

Or TRIM first:

SELECT LEN(TRIM('Ravi  '));  -- Returns 4 (clean)

Mistake 5: Performance -- string functions on large datasets

Wrong:

SELECT * FROM Student
WHERE SUBSTRING(ClassName, 1, 2) = '10';
-- Function on every row, can't use index

Result: Table scan, slow on 1M rows.

Fix: Avoid functions in WHERE if possible:

WHERE ClassName LIKE '10%';
-- Can use index on ClassName

---

Best Practices for String Functions

1. Use CONCAT for multiple strings -- Handles NULL better
2. TRIM incoming data -- Clean on insert, not query
3. Use LIKE for pattern matching -- Easier to read than CHARINDEX
4. 1-based indexing in SUBSTRING -- Start at 1, not 0
5. Handle NULL explicitly -- Don't assume CONCAT does
6. Avoid functions in WHERE on indexed columns -- Use LIKE instead
7. Test with sample data -- String functions error-prone

---

🎯 Q1: What's the difference between SUBSTRING and LEFT?

SUBSTRING: Extract from any position.

SUBSTRING('Ravi Kumar', 6, 5)  -- Returns 'Kumar'

LEFT: Extract first N chars only.

LEFT('Ravi Kumar', 4)  -- Returns 'Ravi'

Use LEFT for prefix. SUBSTRING for specific range.

🎯 Q2: Why use CONCAT instead of + operator?

+ operator: NULL breaks chain.

'Ravi' + ' ' + NULL + ' Kumar'  -- Returns NULL (entire result)

CONCAT: NULL ignored.

CONCAT('Ravi', ' ', NULL, ' Kumar')  -- Returns 'Ravi Kumar'

CONCAT safer with possible NULL values.

🎯 Q3: How do I extract email domain?

Find @ position, extract after:

SELECT 
    Email,
    SUBSTRING(Email, CHARINDEX('@', Email) + 1, LEN(Email)) AS Domain
FROM User;

Result:
Email                    | Domain
nagarjuna@outlook.com    | outlook.com
ravi@gmail.com           | gmail.com

CHARINDEX finds @. SUBSTRING extracts after (+1).

🎯 Q4: What's the difference between LIKE and CHARINDEX?

LIKE: Pattern matching (wildcards).

WHERE Name LIKE '%Kumar%'  -- Contains Kumar
WHERE ClassName LIKE '10%' -- Starts with 10

CHARINDEX: Find exact substring position.

WHERE CHARINDEX('Kumar', Name) > 0  -- Contains Kumar

LIKE more readable. CHARINDEX returns position (useful for extraction).

🎯 Q5: How do I make proper case (Title Case)?

Capitalize first letter, lowercase rest:

SELECT 
    UPPER(LEFT(Name, 1)) + LOWER(SUBSTRING(Name, 2, LEN(Name))) AS ProperCase
FROM Student;

Result:
ProperCase
Ravi
Priya
Arjun

Combine UPPER, LEFT, LOWER, SUBSTRING.

Note: SQL Server has no built-in proper case function.

🎯 Q6: Why avoid string functions in WHERE clause?

Functions prevent index use:

WHERE LEFT(ClassName, 2) = '10'  -- Table scan

SQL can't use ClassName index (function applied to each row).

Better:

WHERE ClassName LIKE '10%'  -- Can use index

LIKE pattern: SQL optimizer uses index effectively.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on String functions for text manipulation in SQL Server. Try these prompts:

• "Show how to extract student initials using SUBSTRING and CHARINDEX"
• "What's the difference between SUBSTRING, LEFT, and RIGHT?"
• "How do I use REPLACE to fix data in a column?"
• "How do I validate email format using string functions?"
• "Quiz me: ask 5 questions about string functions, CONCAT, CHARINDEX"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

22. Date Functions -- Working with Dates and Times ->