String Functions

String Functions

What it means

SQL Server provides built-in functions for manipulating text data — combining, extracting, searching, trimming, and changing case. Common ones: CONCAT, SUBSTRING, LEN, TRIM, UPPER/LOWER, REPLACE, CHARINDEX.

Examples

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

SELECT LEN('Hello');              -- 5
SELECT UPPER('hello');            -- 'HELLO'
SELECT LOWER('HELLO');            -- 'hello'
SELECT TRIM('  hello  ');         -- 'hello' -- removes leading/trailing whitespace

SELECT SUBSTRING('Hello World', 1, 5);    -- 'Hello' -- start position, length
SELECT REPLACE('Hello World', 'World', 'SQL');   -- 'Hello SQL'

SELECT CHARINDEX('World', 'Hello World');  -- 7 -- position where 'World' starts (1-based)

-- Combining functions
SELECT UPPER(TRIM(FirstName)) AS CleanedName FROM Employees;

Common mistake

Assuming SUBSTRING’s second argument is a zero-based index like many programming languages use — SQL Server string positions are 1-based, so SUBSTRING('Hello', 1, 3) starts at the very first character, not the second.

SELECT SUBSTRING('Hello', 0, 3);   -- careful! position 0 behaves oddly (SQL Server treats it near position 1)
SELECT SUBSTRING('Hello', 1, 3);   -- correct: 'Hel' -- starts at the FIRST character (1-based)

Quick practice

1. Is SQL Server’s SUBSTRING function 0-based or 1-based for its starting position?

   Answer

   1-based — the first character of a string is at position 1, not 0.

2. What does CONCAT(FirstName, ' ', LastName) do?

   Answer

   Joins the values together into one string, with a literal space in between — e.g. combining "Alice" and "Smith" into "Alice Smith".

3. What does CHARINDEX('World', 'Hello World') return?

   Answer

   7 — the 1-based position where the substring 'World' first appears.