The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

Text functions: construct, locate and normalize strings

Texts usually arrive with different capital letters, additional spaces or fragments that we need to locate. SQL Server includes functions to combine, extract, find, replace, and normalize strings without exiting the query.

Each function solves a small operation. The important part is to distinguish a presentation transformation from a permanent data correction.

Text operations

CONCAT: combine values into one string

CONCAT accepts two or more arguments and combines them from left to right. An argument is each value passed to the function: it can be text, a column, a number, or the result of another expression.

The function converts its arguments to text and treats each NULL value as an empty string. This lets us build a description without allowing one missing value to make the entire result null.

CONCAT(Expresion1, Expresion2, ...)

TRIM: remove spaces at both ends

TRIM returns a string without the spaces before its first character or after its last character. It does not remove spaces inside the text or change the value stored in the table.

TRIM(Expresion)

UPPER and LOWER: change letter case

UPPER converts the letters in an expression to uppercase; LOWER converts them to lowercase. Both functions change the representation returned by the query, but they do not correct content differences or validate that the text follows an expected format.

UPPER(Expresion)
LOWER(Expresion)

CHARINDEX: locate a string

CHARINDEX searches for one string inside another and returns the position where the first match begins. Positions start at 1; when the text is not found, the function returns 0.

CHARINDEX(TextoBuscado, Expresion)

SUBSTRING: extract text from a position

SUBSTRING returns part of an expression. The starting position identifies the first character to keep, and the length specifies how many characters to request from that point.

SUBSTRING(Expresion, PosicionInicial, Longitud)

REPLACE: replace matching fragments

REPLACE searches an expression for a fragment and replaces every match with new text. The result is another string; the function does not update the source column.

REPLACE(Expresion, TextoBuscado, TextoNuevo)

LEN and DATALENGTH: measure characters or bytes

LEN returns the number of characters in an expression without counting trailing spaces. DATALENGTH measures the bytes used to store it. A byte is a unit of storage, so the two functions can return different values for the same text.

LEN(Expresion)
DATALENGTH(Expresion)

Example: build a description

CONVERT(VARCHAR(20), p.Precio) transforms the number into text before adding it to the description. The first argument sets the type and length of the result; the second contains the value that is converted.

SELECT
    p.IdProducto,
    CONCAT(
        p.Nombre,
        N' - $',
        CONVERT(VARCHAR(20), p.Precio)
    ) AS Descripcion
FROM dbo.Producto AS p;

The conversion lets us combine the name and price into one string. For numeric formats intended for the end user, it is usually better for the application to handle currency and culture.

Clean spaces and unify visual comparison

SELECT
    c.IdCliente,
    TRIM(c.TelefonoMovil) AS TelefonoLimpio,
    LOWER(TRIM(c.Correo)) AS CorreoNormalizado
FROM dbo.Cliente AS c;

The query transforms the result; It does not modify the stored values. To correct the table we need a deliberate UPDATE and prior validation.

Locate and extract a part

We can separate the domain from an email by first searching for the character @:

SELECT
    c.Correo,
    CASE
        WHEN CHARINDEX('@', c.Correo) > 0 THEN
            SUBSTRING(
                c.Correo,
                CHARINDEX('@', c.Correo) + 1,
                LEN(c.Correo)
            )
        ELSE NULL
    END AS Dominio
FROM dbo.Cliente AS c;

The condition avoids assuming that all values ​​contain the separator. This example helps explore data, but is not a substitute for thorough email address validation.

Replace fragments

SELECT REPLACE(N'Pedido pendiente', N'pendiente', N'confirmado') AS TextoNuevo;

REPLACE replaces every match recognized by the selected collation. A collation is the set of rules SQL Server uses to compare and sort text; it determines sensitivity to case and accents.

Unicode text and lengths

The N prefix creates Unicode literals. Unicode is an encoding that can represent characters from many writing systems; for example, N'México'. Use it when the destination is NVARCHAR and the text may contain characters outside the VARCHAR code page, the limited character set associated with that encoding.

Comparing LEN with DATALENGTH helps us distinguish visible length from storage space. The difference is useful when investigating storage and apparently invisible spaces.

What should be checked

Applying UPPER, LOWER, TRIM, or another function directly to a filtered column can make an index harder to use. An index is a structure that organizes values to make rows easier to access. If normalized searching is frequent, review collation, data quality, or an appropriate storage strategy.

We must also know the type and the resulting length when concatenating. Very long results may be truncated if no expression promotes calculation to a maximum length type.