REPLACE() : Replaces all occurrences of a specified string value with another string value. Syntax : ISNULL ( check_expression, replacement_value )Įx 2 : DECLARE varchar(100) = ‘Sudhanshu’ i.e. check if first argument is NULL then ISNULL() : Replaces NULL with the specified replacement value. SELECT = ‘you are the visitor of the website of my blog’ Syntax : PATINDEX ( ‘%pattern%’, expression ) Note : Difference between CHARINDEX and PATINDEX is that – For CHARINDEX, the pattern cannot include wildcard characters while for PATINDEX, it’s compulsory to use wildcard characters. PATINDEX() :Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. Note : Here 24 is the starting character position of the and parameter 10 is the start_location for searching, so first the is skipped Note : Here 9 is the starting character position of the SELECT = ‘you are the visitor of the website of my blog’ Syntax : CHARINDEX ( expressionToFind ,expressionToSearch ) Here Position counting starts from 1 i.e. Note:- Here value is implicitly converted from an int data type into varchar data typeĬHARINDEX() : Returns the starting position of the specified expression in a character string. REVERSE() : Returns the given string value in reverse orderĮx 2 : SELECT REVERSE(1234) AS Reversed Output : Five spaces are at the end of this string That means it removes blanks from the end (right) of a stringĮx : SELECT RTRIM(‘Five spaces are at the end of this string ‘) RTRIM() : Removes trailing spaces from a string. Output : Five spaces are at the beginning of this string. That means it removes blanks from the beginning (left) of a stringĮx : SELECT LTRIM(‘ Five spaces are at the beginning of this string.’) LTRIM() : Removes leading spaces from a string. So Let us start with a brief overview of these functions

Note: – Here we will use some string functions like LTRIM(), RTRIM(), REVERSE(), REPLACE(), CHARINDEX(), PAT INDEX(), …

There are different ways to replace multiple spaces with single space from a string in SQL Server

How to single space in word 2013