SQL - String Functions in SQL

String functions are used to manipulate and work with text data in SQL. They help in formatting, extracting, and combining text for queries and reports.

Common String Functions

Function

Description

Example

CONCAT(str1, str2, …)

Combines multiple strings

CONCAT(first_name,' ', last_name)

SUBSTRING(str, start, length)

Extracts part of a string

SUBSTRING(name, 1, 3)

LENGTH(str)

Returns length of string

LENGTH(name)

REPLACE(str, old, new)

Replaces part of string

REPLACE(name, 'a', 'A')

UPPER(str)

Converts to uppercase

UPPER(name)

LOWER(str)

Converts to lowercase

LOWER(name)

TRIM(str)

Removes leading/trailing spaces

TRIM(name)

Example Table

CREATE TABLE users (

  id INT,

  first_name VARCHAR(50),

  last_name VARCHAR(50)

);

 

INSERT INTO users VALUES

(1, 'Ashika', 'Ashi'),

(2, 'Rohan', 'Kumar');

Example Queries

1. Combine first and last name:

SELECT CONCAT(first_name, ' ', last_name) AS full_name

FROM users;

Result:

full_name

----------

Ashika Ashi

Rohan Kumar

2. Extract first 3 letters of first name:

SELECT SUBSTRING(first_name, 1, 3) AS short_name

FROM users;

Result:

short_name

-----------

Ash

Roh

3. Replace letter in name:

SELECT REPLACE(first_name, 'a', 'A') AS new_name

FROM users;

Result:

new_name

---------

AshikA

RohAn

Key Points

  • String functions can be nested:
  • UPPER(CONCAT(first_name, ' ', last_name))
  • Useful in reporting, formatting, and data cleaning
  • Works with VARCHAR, CHAR, and TEXT data types