.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
.svg)
Updated and tested 5 October 2026
Two strings match or don't match according to the collation on the column. Under a case-insensitive collation, 'Acme' and 'ACME' are the same value. Trailing spaces are ignored in a comparison, so 'Acme ' matches 'Acme'. LEN ignores them too. DATALENGTH counts them.
A non-breaking space is not a space to any of those rules. TRIM leaves it where it is and the join fails anyway. A partner file I loaded last month had 40,000 customer rows and roughly 900 company names carrying an invisible trailing non-breaking space, fine on screen and unmatchable on join. The syntax of the SQL Server string functions is trivial. The behaviour is where the work is. Most of this page is queries.
Everything below runs against the same three tables used across this series, plus a staging table for the import examples.
CREATE TABLE dbo.customers (
customer_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
company_name nvarchar(200) NOT NULL,
contact_email varchar(255) NULL,
country_code char(2) NOT NULL,
created_utc datetime2(3) NOT NULL DEFAULT (SYSUTCDATETIME())
);
CREATE TABLE dbo.orders (
order_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
customer_id int NOT NULL REFERENCES dbo.customers (customer_id),
order_date datetime2(3) NOT NULL,
shipped_date datetime2(3) NULL,
status varchar(20) NOT NULL,
total_amount decimal(12,2) NOT NULL
);
CREATE TABLE dbo.order_items (
order_item_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
order_id int NOT NULL REFERENCES dbo.orders (order_id),
sku varchar(40) NOT NULL,
quantity int NOT NULL,
unit_price decimal(12,2) NOT NULL
);
CREATE TABLE dbo.order_import (
import_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
raw_line varchar(400) NOT NULL,
sku_list varchar(400) NULL,
company_raw nvarchar(200) NULL
);Concatenate in SQL Server: the + operator versus CONCAT
Start with the version everyone writes first. It's the one that loses data without raising anything.
SELECT company_name + ' <' + contact_email + '>' AS label
FROM dbo.customers;For every customer with a contact_email, that returns exactly what you'd expect. For every customer without one, it returns NULL. Not the company name with an empty pair of angle brackets. The entire expression, gone, because NULL propagates through + under the default SET CONCAT_NULL_YIELDS_NULL ON. In a 40,000 row export that's 900 blank cells nobody notices until a mail merge goes out addressed to nothing.
CONCAT, added in SQL Server 2012, converts NULL arguments to an empty string before joining them:
SELECT CONCAT(company_name, ' <', contact_email, '>') AS label
FROM dbo.customers;Now a missing email gives you Northwind Traders <>. Visibly wrong on screen beats invisibly wrong inside a NULL. CONCAT in SQL Server takes 2 to 254 arguments. It converts every one of them to a string type implicitly. It truncates the result at 8,000 characters unless one input is a max type. Cast one argument to varchar(max) and that ceiling goes away.
CONCAT also fixes type juggling. Try SELECT 'Order ' + order_id FROM dbo.orders. You get Conversion failed when converting the varchar value 'Order ' to data type int. int outranks varchar in the precedence list, so SQL Server tries to turn your text into a number. CONCAT('Order ', order_id) just works.
ISNULL, COALESCE and CASE are the tools for deliberate NULL control, and they compose with CONCAT without any trouble. I use + only when I want NULL to propagate, which is rarely.
CONCAT_WS in SQL Server 2017 and later
CONCAT_WS takes a separator as its first argument, then up to 254 values, and it skips NULLs along with their separators. Building an address line is the case it was written for.
SELECT CONCAT_WS(', ', company_name, contact_email, country_code) AS directory_line
FROM dbo.customers;A NULL contact_email disappears cleanly. Nothing like Northwind Traders, , GB comes back. Microsoft's CONCAT_WS reference carries the argument limits. It ignores SET CONCAT_NULL_YIELDS_NULL entirely, and if every argument is NULL you get an empty varchar(1) back. New in 2017, which for once means new.
REPLACE and the collation trap
REPLACE in SQL Server takes REPLACE(expression, pattern, replacement) and swaps every occurrence. It obeys the collation of the input, which is the surprising part. No literal byte match is happening.
SELECT REPLACE('SKU-1001-BLUE', '-', '_') AS underscored; -- SKU_1001_BLUE
-- Under a case-insensitive collation, this replaces:
SELECT REPLACE('Server Farm', 'SERVER', 'Node') AS ci_result; -- Node Farm
-- Force a case-sensitive comparison:
SELECT REPLACE('Server Farm' COLLATE Latin1_General_CS_AS, 'SERVER', 'Node') AS cs_result;
-- Server FarmMost SQL Server databases are created with a case-insensitive collation, so a script that strips the literal token Server will happily strip SERVER and server too. That's usually what you want. When it isn't, the COLLATE clause on the first argument is the fix. A _CS_AS or a _BIN2 collation both do the job. _BIN2 is stricter, since it also stops treating accented characters as equivalents.
Three more rules. An empty pattern raises no error and hands the input back unchanged. NULL in any of the three arguments returns NULL for the whole expression. The result truncates at 8,000 bytes unless a max type is in play. On nvarchar that means 4,000 characters. A chain of nested REPLACE calls on a long description column can lose the tail without saying so. Same ceiling as CONCAT, same fix: cast to nvarchar(max) before you nest.
CHARINDEX and PATINDEX
CHARINDEX in SQL Server and PATINDEX both find a position in a string. Both are 1-based. Both return 0 when there's no match, so WHERE CHARINDEX(...) > 0 is the existence test you want.
SELECT contact_email,
CHARINDEX('@', contact_email) AS at_position,
RIGHT(contact_email, LEN(contact_email) - CHARINDEX('@', contact_email)) AS email_domain
FROM dbo.customers
WHERE CHARINDEX('@', contact_email) > 0;CHARINDEX takes an optional third argument, a start position. That is how you find the second occurrence: search once, then search again starting one character past what you found. On a max type it returns bigint and not int, which trips up code that assigns the result straight into an int variable.
PATINDEX is the one to reach for when you don't know the exact text you're looking for. It accepts the LIKE wildcard set, so %, _, a character list in brackets and a negated list with [^...].
-- Position of the first digit
SELECT PATINDEX('%[0-9]%', 'SKU-1001-BLUE') AS first_digit; -- 5
-- Rows where a supposedly numeric field contains anything else
SELECT import_id, raw_line
FROM dbo.order_import
WHERE PATINDEX('%[^0-9]%', raw_line) > 0;The negated-class search is the one I use constantly on imported data. It finds the bad rows directly. Nobody has to eyeball 40,000 of them. PATINDEX needs the leading and trailing % to search anywhere in the string, and its NULL handling is asymmetric: a NULL pattern gives you NULL back, a NULL expression raises an error.
Both of these wreck an index seek the moment you put them in a WHERE clause, since the column ends up wrapped in a function. What stays sargable and what doesn't is the subject of the WHERE clause in SQL Server. On a staging table of 40,000 rows I don't care and neither should you.
LEFT, RIGHT and SUBSTRING
LEFT and RIGHT take a count. SUBSTRING takes a start position and, historically, a length.
DECLARE @sku varchar(40) = 'SKU-1001-BLUE';
SELECT LEFT(@sku, 3) AS prefix, -- SKU
RIGHT(@sku, 4) AS suffix, -- BLUE
SUBSTRING(@sku, 5, 4) AS item_code; -- 1001In SQL Server 2025 the length argument became optional, so SELECT SUBSTRING('123abc', 4) returns abc. Microsoft's SUBSTRING reference prints both syntax blocks side by side, which is the quickest way to check what your instance accepts. That form is 2025 and later only. On 2022, 2019 and 2017 the same call fails, because length is required there. The portable version is SUBSTRING(@s, 4, LEN(@s)) or RIGHT(@s, LEN(@s) - 3). An over-long length is clamped and not an error.
I have not run the two-argument form against a production instance. Everything above about it comes off the reference page and a scratch database, so take the syntax as reliable and my experience of it as nil.
A start below 1 doesn't error. SUBSTRING(@sku, -2, 6) starts counting from a position before the string and returns only the characters that fall inside it, so you get three characters back and not six. If you're computing the start from another function's output, check it before you pass it.
The pattern that turns up most in real parsing work is a delimiter you have to locate first:
DECLARE @email varchar(255) = '[email protected]';
SELECT LEFT(@email, CHARINDEX('@', @email) - 1) AS local_part,
SUBSTRING(@email,
CHARINDEX('@', @email) + 1,
LEN(@email) - CHARINDEX('@', @email)) AS domain_part;LEN versus DATALENGTH
LEN returns characters and excludes trailing spaces. DATALENGTH returns bytes and trims nothing. The gap between them is a diagnostic.
SELECT LEN('lumadock ') AS len_result, -- 8
DATALENGTH('lumadock ') AS bytes_varchar, -- 11
LEN(N'lumadock') AS len_unicode, -- 8
DATALENGTH(N'lumadock') AS bytes_nvarchar; -- 16nvarchar stores two bytes per character, so on an nvarchar column a clean value satisfies DATALENGTH(col) = LEN(col) * 2. Anything that fails that test has trailing whitespace of some kind, and this is exactly how I found those 900 non-breaking spaces:
SELECT customer_id, company_name,
LEN(company_name) AS chars,
DATALENGTH(company_name) AS bytes
FROM dbo.customers
WHERE DATALENGTH(company_name) <> LEN(company_name) * 2;LEN counts leading spaces and skips trailing ones. That asymmetry is documented behaviour and not a bug. It also explains why WHERE company_name = 'Acme' matches a value stored as 'Acme '. Trailing-space padding is ignored in comparisons. The row joins fine and your export file looks wrong. A non-breaking space is a different character entirely and gets no such courtesy.
TRIM in SQL Server, and what arrived in which version
Most tutorials get the version story wrong here, so here it is precisely. TRIM in SQL Server is 2017. The characters FROM argument, which strips something other than spaces, shipped with it in 2017 and works on 2019 as well. Only the LEADING, TRAILING and BOTH keywords are new in SQL Server 2022, and they need the database compatibility level set to 160, which is a separate thing from the server version.
-- SQL Server 2017 and later
SELECT TRIM(' LumaDock ') AS spaces_gone;
SELECT TRIM('.,! ' FROM ' ...Order #1001!! ') AS punctuation_gone;
-- SQL Server 2022 and later, needs compatibility level 160
SELECT TRIM(TRAILING '.' FROM 'northwind.example.') AS trailing_dot_gone;That last one is where people get caught after an in-place upgrade. The server is 2022 or 2025, the database was restored from an older instance and kept its old compatibility level, and TRIM(TRAILING ...) throws a syntax error on a server that supports it perfectly well. Check before you blame the version:
SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();On SQL Server 2016 and earlier there's no TRIM at all. The idiom is LTRIM(RTRIM(company_name)), which handles spaces only. Microsoft's TRIM reference puts the compatibility-level note in an Important box. Neither TRIM nor the old pair touches tabs, carriage returns or non-breaking spaces, which is why the cleanup section below nests REPLACE calls inside the trim.
