• Ekta Aggarwal

String functions in SQL

There are various string manipulations which can be done in SQL. In this tutorial we will be working on several string functions on Microsoft SQL server.


Some rules for strings:


  • CASE matters in SQL when something is written in quotes. i.e. 'Hello' is not same as 'HELLO'

  • Spaces matter i.e. ' Hello ' is not same as 'Hello'

  • Blank string is not NULL i.e. ' ' is not same as NULL.

  • Order by precedence of letters: ' ' < 'A' < 'B' < 'a' < 'b'


LOWER: Converts the text to lower case


UPPER: Converts the text to upper case

SELECT LOWER('APPlemanGO') as lower_func,
UPPER('APPlemanGO') as upper_func;

LEN: Returns the number of characters in a string.

Task: Our string APPlemanGO has 10 characters, thus LEN function will return 10.

SELECT LEN('APPlemanGO');

TRIM: Removes spaces from start and end of the string. LTRIM: Removes spaces from start (left side) of the string.

RTRIM: Removes spaces from end (right side) of the string.

SELECT TRIM(' APPlema nGO ') as trim_func,
LTRIM(' APPlema nGO ') as ltrim_func,
RTRIM(' APPlema nGO ') as rtrim_func;

TRIM: You can also specify which characters need to be removed from start and end of the string by writing: Syntax: SELECT TRIM(pattern FROM string) ; Task: We want to remove pattern 'P' from start and end of our string 'PPPAPPlemanGOPPPP':

SELECT TRIM ( 'P' FROM  'PPPAPPlemanGOPPPP')as trim_func;

REPLACE: replaces a particular pattern from the string:

Syntax:

REPLACE(string,pattern to be replaced in original string,new pattern)

Task: We want to replace pattern 'PP' by '__' from string 'APPlemanGO'

SELECT REPLACE('APPlemanGO','PP','__');

REVERSE: Returns the reversed string

SELECT REVERSE('APPlemanGO');

LEFT: Extracts first X (left side) characters from a string. RIGHT: Extracts last Y (right side) characters from a string. SUBSTRING: Extracts X characters from the string, starting from Yth character.

Syntax:

SUBSTRING(string, start position (i.e. Y). number of characters to be extracted (i.e. X))

SELECT LEFT('APPlemanGO',3) as left_func,
RIGHT('APPlemanGO',3) as right_func,
SUBSTRING('APPlemanGO',2,3) as substring_func;

CONCAT: Combines multiple strings to form a single string. For concat we need to provide the seperator ourselves. In the following example our seperator is '_'

SELECT concat('Apple','_','Mango','_','Cherry');

CONCAT_WS: Combines multiple strings to form a single string with a pre-defined seperator. Syntax: CONCAT_WS(seperator,string1,string2,...,string N)

SELECT concat_ws('_','Apple','Mango','Cherry');

STRING_SPLIT: splits a single string on the basis of a seperator Syntax: SELECT value FROM STRING_SPLIT(string, seperator);

SELECT value FROM STRING_SPLIT('Apple Mango Cherry Litchi', ' ');

Tags: