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', ' ');
Opmerkingen