SQL String Functions: W3Schools Guide and Examples

Formulas & functions · Updated November 22, 2026

SQL string functions work with text values: they can join strings, change letter case, extract part of a value, find text, or replace it. W3Schools’ SQL function material is useful for learning these operations, but many examples use MySQL syntax, so check your database system before copying a function into a query.

What W3Schools Means by SQL String Functions

A string is text stored in a character column or supplied as a text literal. Common examples include a person’s name, a product description, an email address, and a postal code. String functions are SQL expressions that return a value derived from text. They can be used in a query’s SELECT list, in filtering conditions, and in other expressions.

W3Schools provides SQL tutorials and function references, including functions associated with working on strings. The phrase “SQL string functions” does not identify one perfectly uniform set of functions, however. SQL is implemented by database products such as MySQL, SQL Server, PostgreSQL, and SQLite, and their function names and argument rules differ. A function in a W3Schools example may be a good illustration of a task without being portable to every database.

For example, MySQL uses LENGTH to count a string’s bytes and CHAR_LENGTH to count its characters. SQL Server commonly uses LEN for a character count, while PostgreSQL has LENGTH for its character count. Always identify the database engine and version behind a tutorial example before relying on it.

How String Functions Work in a Query

Most string functions take one or more inputs and return a value for each row being processed. In the following MySQL example, UPPER changes the displayed name to uppercase, while the stored value in the table remains unchanged:

SELECT UPPER(Name) AS UppercaseName FROM Customers;

The function is an expression, not an update. To permanently change data, a query must use an appropriate data-changing statement, such as UPDATE, and that should be done only when the change is intended. A function can also be applied to a literal, another function’s result, or a combination of columns and text.

For example, MySQL’s CONCAT can combine a first name and last name for display:

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

The alias after AS gives the returned column a readable heading. The precise behavior when an input is NULL can depend on the function and database, so test expressions against realistic data rather than assuming every system handles missing values identically.

Common String Function Categories

It is easier to choose a function by the job you need to do than by memorizing a long reference list. Common categories include:

  • Joining text: CONCAT combines values. Some systems also provide CONCAT_WS, which joins values using a separator.
  • Changing case: UPPER and LOWER convert letters to uppercase or lowercase. W3Schools’ MySQL references may also show the aliases UCASE and LCASE.
  • Extracting text: SUBSTRING or SUBSTR returns part of a string. LEFT and RIGHT return characters from the beginning or end in systems that support them.
  • Trimming spaces: TRIM removes spaces or specified characters from the ends of a value. LTRIM and RTRIM target the left or right end in many systems.
  • Replacing text: REPLACE returns a string with matching text substituted. It is not the same as editing the stored column by itself.
  • Finding text: Functions such as LOCATE, INSTR, POSITION, and CHARINDEX search for a substring, with names and argument order depending on the database.
  • Measuring text: LENGTH, CHAR_LENGTH, and LEN report a length, but the meaning of “length” and treatment of trailing spaces can vary.

These categories cover everyday text work, but a reference may include many less common functions too. Read the individual function’s documentation for its exact arguments, return value, and edge-case behavior.

Practical MySQL Examples

The following examples use MySQL-style syntax, as commonly seen in MySQL-oriented learning material. Assume a table named Products has a column called ProductName. To return a trimmed version alongside the original, use:

SELECT ProductName, TRIM(ProductName) AS CleanName FROM Products;

This is useful for spotting unwanted spaces at either end. It does not remove spaces between words. To return the first five characters of each name, MySQL accepts:

SELECT SUBSTRING(ProductName, 1, 5) AS ShortName FROM Products;

MySQL counts the starting position from 1 in this form. A request for five characters beginning at position 1 returns at most the first five characters; shorter strings remain shorter. Other databases may offer SUBSTRING with different syntax or equivalent functions, so do not assume every engine accepts this exact expression.

To replace one phrase with another in query results, MySQL can use:

SELECT REPLACE(ProductName, 'Old', 'New') AS UpdatedName FROM Products;

This expression substitutes occurrences in the returned value. It does not save the replacement in Products. To search for rows containing a fragment, a WHERE condition with LIKE is often simpler than a search function:

SELECT ProductName FROM Products WHERE ProductName LIKE '%phone%';

The percent signs are wildcards for any sequence of characters. Case sensitivity and pattern behavior can depend on the database’s collation and configuration.

How to Choose the Right Function

Start with the desired result and then look up the function for your specific database. Ask whether you need to display transformed text, filter rows, or permanently change data; these are different operations. Also determine whether positions count from one or zero, whether a length means bytes or characters, and what happens when an input is NULL or shorter than the requested substring.

A practical selection checklist is:

  1. Identify the database product, such as MySQL or SQL Server, and its version.
  2. Decide whether the task is joining, searching, extracting, trimming, measuring, or replacing.
  3. Check the function signature, including argument order and optional parameters.
  4. Try the expression on values that include empty strings, NULLs, spaces, punctuation, and non-English characters when those occur in your data.
  5. Use a clear column alias so the result is understandable to people reading the query.

If the operation is part of a data-cleaning workflow, make sure the transformation matches the intended rule. Removing spaces from an account identifier, for instance, could incorrectly merge distinct values if spaces are meaningful in that system.

Differences Between SQL Dialects

SQL examples are not always interchangeable. MySQL’s CONCAT joins strings, while SQL Server also supports CONCAT but has its own rules for related expressions and types. SQL Server uses LEN for string length and CHARINDEX for finding a substring; MySQL commonly uses CHAR_LENGTH or LENGTH and LOCATE or INSTR for related tasks. PostgreSQL supports functions such as POSITION and LENGTH, while SQLite has its own built-in string-function set.

Even functions with similar names can differ in details. MySQL’s LENGTH counts bytes, which can be greater than the number of characters when text uses a multibyte character set. CHAR_LENGTH counts characters. SQL Server’s LEN does not count trailing spaces, an important distinction when comparing lengths. Indexing rules for substring functions and handling of NULL arguments also deserve a quick check in the relevant documentation.

W3Schools examples can still teach the underlying idea: for instance, use a trim operation to remove unwanted boundary spaces. But translate that idea into the syntax and behavior of your actual database rather than treating a tutorial’s dialect as universal SQL.

Common Problems and Misconceptions

One common mistake is expecting a SELECT expression to alter the underlying table. A query such as SELECT TRIM(Name) displays a trimmed result; it does not clean the saved Name values. Another is treating LENGTH as a universal character counter. In some systems it counts bytes, and in others trailing spaces are treated differently.

NULL is another frequent source of surprises. NULL means the value is unknown or absent, not an empty string. A function may return NULL when given NULL, or a concatenation may behave differently between systems. If missing values should display as a substitute, use the database’s documented NULL-handling expression deliberately rather than silently assuming a string function will fill them in.

Finally, applying a function to a column in a WHERE condition can make it harder for a database to use an ordinary index efficiently. For a large table, a condition such as filtering on LOWER(Email) may require extra work unless an appropriate functional index or other database-specific solution is available. Check the query plan and database guidance when performance matters.

Alternatives and Good Learning Habits

For straightforward text matching, LIKE may be more readable than a position-search function. For transforming text, functions can be appropriate in SELECT statements, views, or data-cleaning queries. If the same normalization rule must be applied consistently across an application, consider where that rule belongs and document it so different queries do not implement subtly different versions.

Use W3Schools as an accessible introduction, then confirm production syntax in the official documentation for your database. A helpful learning exercise is to run small examples against a few known values, including NULL, an empty string, a one-character string, and text with spaces. That reveals assumptions about positions, character counts, and missing values before those assumptions affect a larger query.

Frequently Asked Questions

What are SQL string functions?
They are functions that operate on text and return results such as joined, changed, searched, extracted, or measured strings. Their names and exact behavior vary across database systems.

Does W3Schools teach MySQL string functions?
W3Schools’ SQL learning material includes MySQL-oriented examples and function references. Treat the syntax as dialect-specific and verify it against the database you use.

What is the difference between LENGTH and CHAR_LENGTH?
In MySQL, LENGTH counts bytes and CHAR_LENGTH counts characters. This matters for multibyte text, where one character can occupy more than one byte. Other database systems use different names or definitions.

Does TRIM remove spaces inside a string?
No. TRIM normally removes spaces or specified characters from the beginning and end of a value. It does not collapse or delete spaces between words.

Do string functions change the data in a table?
Not when used in a SELECT expression: they change the value returned by that query, not the saved row. Persisting a change requires a data-modifying operation such as UPDATE.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.