You are here
Home > Sql server > Sql server 2016 > SQL:String Functions

SQL:String Functions

String Functions

In last post we discuss about UPPER(), LOWER(), REVERSE(), FORMATE(), REPLACE() ,CHARINDEX() string functions. And today we discuss remaining string functions.
In this entire post for examples we use below table.
Table: persons

p_id

firstname lastname city
1 Amisha Patel

Anand

2

Bina Patel Baroda
3 Tina Doyle

v.v.nagar

4

Riya Patel Anand
5 Nikunj Patel

NULL

LEN():

  • The LEN() function return the length of input string in terms of number of characters excluding the trailing blank.
  • It returns the number of characters not bytes, whether it is regular character or Unicode character string.

Syntax:
LEN(expression)
Example:
select firstname, LEN(firstname) as fname_length from persons;
output:

Firstname

fname_length
Amisha

6

Bina

4
Tina

4

Riya

4

Nikunj

6

DATALENGTH():

  • The DATALENGTH() function returns the number of bytes to represent any expression.
  • The data length of NULL is NULL.
  • If the input is Unicode character string, it will count 2 bytes per character. The DATALENGTH() function not remove trailing space.

Syntax:
DATALENGTH(expression)
Example:
select DATALENGTH(N’Riya’) as datalength_ex;
output:

datalength_ex

8

REPLICATE():

  • The REPLICATE() function repeats an input string in to the number of times given as the second argument.

Syntax:
REPLICATE(expression, interger)
Example:
select REPLICATE(‘Riya’,2) as exreplicate;
output:

exreplicate

RiyaRiya

STUFF():

  • The STUFF() function insert a string into another string.
  • It deletes the specified length of characters int the first string at the start position and then inserts the second string in to first string at the start position.

Syntax:
STUFF(expression, start, length, replace_expression)
Example:
select STUFF(‘Riya Patel’,5,1,’M’) as ex_stuff;
output:

ex_stuff

RiyaMPatel

STRING_SPLIT():

  • The STRING_SPLIT() splits the character expression using specified separator.
  • When we use this function for output we have to use value otherwise output will not return.
  • This function supported only under compatibility level 130. We also change compatibility level of database using the below command.
  • ALTER Database database_name set COMPATIBILITY_LEVEL=130.

Syntax:
STRING_SPLIT(expression, separator)
Example:
DECLARE @name varchar(max)=N’Riya.M.Patel’;
select value from string_split(@name,’.’);
output:

value

Riya

M

Patel

You can also by SQL 70-761 book write by me fromĀ Amazon.
You can also by Kotlin Book write by me fromĀ Amazon.

Microsoft SQL 70-762 Practice Test.

Microsoft MCSA 70-761 Practice Test:

Leave a Reply

Top