Introduction

In this post, we will discuss how to work with SQL string functions and explain the concepts with an example in a simple way. I hope this is very useful for beginners and intermediates to help them understand the basic concept.

Following are the list of SQL functions required to manipulate the string,

Concept explanation with example,

ASCII()

The ASCII function returns numeric values of the left-most character of a string.

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Developer'
  3. SELECT ASCII(@Name) AS NAME
Output

CHAR()

The char function returns the ASCII character based on the number.

Example

  1. DECLARE @CHAR AS INT
  2. SET @CHAR = 68
  3. SELECT CHAR(@CHAR) AS CHARACTER

Output

CHARINDEX()

The CHARINDEX function returns the location of a substring in a string

Example

  1. select CHARINDEX('@', '[email protected]') As CHARINDEX
Output

CONCAT()

The CONCAT function returns concatenates of two or more strings together.

Example

  1. DECLARE @Str1 varchar(30), @Str2 varchar(30)
  2. SET @Str1 = 'Developer'
  3. SET @Str2 = 'Software '
  4. SELECT CONCAT(@Str2, @Str1) AS Result

Output

DATALENGTH()

The DATALENGTH function returns the length of an expression (in bytes)

Example

  1. DECLARE @Str1 nvarchar(30), @Str2 varchar(30)
  2. SET @Str1 = 'Developer'
  3. SET @Str2 = 'Developer'
  4. SELECT DATALENGTH(@Str1) AS Result, DATALENGTH(@Str2) AS Result2

Output

LEFT()

The LEFT function extracts a substring from a string starting from the left with the specified number of characters.

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Jitendra'
  3. SELECT LEFT(@Name,3) AS LeftPart

Output

LEN()

The LEN function returns the length of the specified string

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Jitendra'
  3. SELECT LEN(@Name) AS Length

Output

LOWER()

The LOWER function returns converts a string to lower-case

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Developer'
  3. SELECT LOWER(@Name) AS LowerCase

Output

LTRIM()

The LTRIM function removes left leading spaces from a string

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = ' Developer'
  3. SELECT LTRIM(@Name) AS Result
Output

REPLACE()

Replaces a sequence of characters in a string with another set of characters or one string to another.

Example

  1. DECLARE @Str1 varchar(30), @Str2 varchar(30)
  2. SET @Str1 = 'Developer'
  3. SET @Str2 = 'Software Developer'
  4. SELECT REPLACE(@Str1, @Str1, @Str2) AS Result

Output

RIGHT()

The right function extracts a substring from a string starting from the right side.

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Software Developer'
  3. SELECT RIGHT(@Name,Len('Developer')+1) AS Result

Output

RTRIM()

The RTRIM function removes trailing spaces from a string.

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Developer '
  3. SELECT RTRIM(@Name) AS Result

Output

STUFF()

STUFF Deletes a sequence of characters from a string and then inserts another sequence of characters into the string, starting at a specified position

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Software Developer'
  3. SELECT STUFF(@Name, Len('Developer'),LEN('Software Developer')+1,' Engineer') AS Result

Output

SUBSTRING()

The SUBSTRING function returns extracts of a substring from a string, requires a three parameter string, a start position, and end position

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Software Developer'
  3. SELECT SUBSTRING(@Name,0,Len('Software')+1) AS Result

Output

UPPER ()

The UPPER function converts a string to upper-case.

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Developer'
  3. SELECT UPPER(@Name) AS NameInUpperCase

Output

REVERSE()

The REVERSE() function returns the string with the order of the characters reversed

Example

  1. DECLARE @Name varchar(30)
  2. SET @Name = 'Developer'
  3. SELECT REVERSE(@Name) AS NAME
Output