Post
Ask Question

String Function In SQL

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,

 

  • ASCII()
  • CHAR()
  • CHARINDEX()
  • DATALENGTH()
  • LEFT()
  • LEN()
  • LOWER()
  • LTRIM()
  • REPLACE()
  • RIGHT()
  • RTRIM()
  • STUFF()
  • SUBSTRING()
  • UPPER ()
  • REVERSE()

 

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