String Functions in SQL Server

    ASCII()

    CHAR()

    NCHAR()

    DIFFERENCE()

    LEFT()

    RIGHT()

    LTRIM()

    RTRIM()

    QUOTNAME()

    REPLACE()

    QUOTNAME()

    CHARINDEX

    PATIINDEX

    LEN

    STUFF

    SUBSTRING

    LOWER/UPPERUFF 




Char:
Converts an int ASCII code to a character.

Example :

DECLARE @position int, @string char(8)
-- Initialize the current position and the string variables.
SET @position = 1
SET @string = 'New Moon'
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
   SET @position = @position + 1
   END
GO

Here is the result set.
----------- -
78 N
----------- -
101 e
----------- -
119 w
----------- -
32
----------- -
77 M
----------- -
111 o
----------- -
111 o
----------- -
110 n
----------- -

LEFT
Returns the left part of a character string with the specified number of characters.

Example:
SELECT LEFT('abcdefg',2)
GO

Result
ab



Len
Returns the number of characters of the specified string expression, excluding trailing blanks.

Example:
USE AdventureWorks2012;
GO
SELECT LEN(FirstName) AS Length, FirstName, LastName
FROM Sales.vIndividualCustomer
WHERE CountryRegionName = 'Australia';
GO

Lower:
Returns a character expression after converting uppercase character data to lowercase.

Example:
USE AdventureWorks2012;
GO
SELECT LOWER(SUBSTRING(Name, 1, 20)) AS Lower,  UPPER(SUBSTRING(Name, 1, 20)) AS Upper,
   LOWER(UPPER(SUBSTRING(Name, 1, 20))) As LowerUpper FROM Production.Product
WHERE ListPrice between 11.00 and 20.00;
GO

Result:
Here is the result set.
Lower Upper LowerUpper
--------------------- --------------------- --------------------
minipump MINIPUMP minipump
taillights - battery TAILLIGHTS - BATTERY taillights - battery


LTrim
Returns a character expression after it removes leading blanks.

Example:-
DECLARE @string_to_trim varchar(60);
SET @string_to_trim = '     Five spaces are at the beginning of this
   string.';
SELECT 'Here is the string without the leading spaces: ' +
   LTRIM(@string_to_trim);
GO
Result
------------------------------------------------------------------------
Here is the string without the leading spaces: Five spaces are at the beginning of this string.            


Replace:
Replaces all occurrences of a specified string value with another string value.

Example :-

SELECT REPLACE('abcdefghicde','cde','xxx');
GO

Result:
abxxxfghixxx
(1 row(s) affected)

Reverse:
Returns the reverse order of a string value.


Example:
USE AdventureWorks2012;
GO
SELECT FirstName, REVERSE(FirstName) AS Reverse
FROM Person.Person
WHERE BusinessEntityID < 5
ORDER BY FirstName;
GO

Right:
Returns the right part of a character string with the specified number of characters.

Example:
USE AdventureWorks2012;
GO
SELECT RIGHT(FirstName, 5) AS 'First Name'
FROM Person.Person
WHERE BusinessEntityID < 5
ORDER BY FirstName;
GO

Rtrim:
Returns a character string after truncating all trailing blanks.

Example:
SELECT RTRIM('Removes trailing spaces.   ');

Here is the result set.
-------------------------------------------------------------------------


SubString:
Returns part of a character, binary, text, or image expression in SQL Server 2012.

Example:
USE AdventureWorks2012;
GO
SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial
FROM Person.Person
WHERE LastName like 'Barl%'
ORDER BY LastName;

Here is the result set.
LastName      Initial
--------      -------
Barley        R
Barlow        B
(2 row(s) affected)

SELECT x = SUBSTRING('abcdef', 2, 3);

Here is the result set.
x
----------
bcd

Upper:
Returns a character expression with lowercase character data converted to uppercase.

Example:
USE AdventureWorks2012;
GO
SELECT UPPER(RTRIM(LastName)) + ', ' + FirstName AS Name
FROM Person.Person
ORDER BY LastName;
GO