String functions and collation

Clean text without losing meaning.

UnderstandStudy an examplePractise

Understand the concept

LEN, LEFT, RIGHT, SUBSTRING, REPLACE, TRIM and CONCAT format text. SQL Server LEN excludes trailing spaces; DATALENGTH counts bytes. Unicode character counts and byte counts differ. CONCAT treats NULL inputs like empty strings, which may or may not match the business meaning.

Case and accent comparisons follow collation rules. Wrapping every search column in LOWER can make index access harder; consider the column type and comparison rules. Preserve original values when automatically transforming emails or identifiers.

WHEN TO USE IT

Use it to inspect imported text and format report output.

COMMON PITFALL

Confusing LEN with byte length or silently losing NULL information.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT FullName, UPPER(FullName) AS DisplayName FROM lab.Customer;

The stored name is unchanged; only its presentation changes.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Show the first three characters of product names.

Show the solution and explanation

Worked solution

SELECT ProductName, LEFT(ProductName, 3) AS Prefix FROM lab.Product;

This is not a reliable classification key; different names can share a prefix.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.