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

SQL: String Functions

String Functions

In last post we discuss about CONCATE(), LEFT(), RIGHT() 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

UPPER():

  • The UPPER() string function returns an expression, with all letters in uppercase.

Syntax:
UPPER(expression)
Example:
select UPPER(firstname)as uppercase_name from persons;
output:

uppercase_name

AMISHA

BINA

TINA

RIYA

NIKUNJ

LOWER():

  • The LOWER() string function returns an expression, with all letters in lowercase.

Syntax:
LOWER(expression)
Example:
select LOWER(firstname)as lowercase_name from persons;
Output:

lowercase_name

amisha

bina

tina

riya

nikunj

REVERSE():

  • The REVERSE() function reverse the order of a string.

Syntax:
REVERSE(expression)
Example:
select REVERSE(firstname)as reverse_name from persons;
output:

reverse_name

ahsimA

aniB

aniT

ayiR

jnukiN

FORMAT():

  • The FORMATE() function allow us to format an input value to a character string based on a .NET format string, with an optional culture parameter.
  • It returns a value formatted with the specified format and optional culture in SQL.

Syntax:
FORMAT(value, format, [culture])

  • Format argument have ‘C’ or ‘D’. C used with currency, D used with Datatime.

Example:
DECLARE @m money=120.90;
select @m as unformated_value, FORMAT(@m,’C’,’zh-cn’) as zh_cn_curr, FORMAT(@m,’C’,’en-us’) as en_us_curr,
FORMAT(@m,’C’,’de-cn’) as de_cn_curr;
Output:

unformated_value

zh_cn_curr

en_us_curr

de_cn_curr

120.90

¥120.90

$120.90

120,90 ¥

REPLACE():

  • Using REPLACE() function we can replace an input string provided as the first argument with all occurrence of the string provided as the second argument, with the string provided as the third argument.

Syntax:
REPLACE(expression, pattern, string_replace)
Example:
select firstname,REPLACE(firstname,’i’,’i_replace’) as i_replace from persons;
output:

firstname

i_replace

Amisha

Ami_replacesha

Bina

Bi_replacena

Tina

Ti_replacena

Riya

Ri_replaceya

Nikunj

Ni_replacekunj

CHARINDEX()

  • The CHARINDEX() function returns the position of the first occurrence of the string provided as the first argument within the string provided as the second argument.
  • We can also provide the position to start looking for expression.

Syntax:
CHARINDEX(exp_to_find, exp_to_search, start_position)
Example:
select CHARINDEX(‘ ‘,’Riya Patel’) as ex_charindex;
output:

ex_charindex

5

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