String Functions in SQL Server part-II || SOUNDEX, DIFFERENCE, LEFT, LEN, LOWER, LTRIM in SQL

This video will provide you the details of each string functions in SQL Server 2014. I'll be walking through each and every kinds of build in function which are available in SQL Server in my next videos.

Previous video for more String Functions

*******************************************************************************
SOUNDEX (Transact-SQL)
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
The SOUNDEX() returns a four digit code for a varchar value passed to it. The same code is returned for all those names whose pronunciation is similar.

 Syntax
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

SOUNDEX ( character_expression )

 Arguments
character_expression
Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.

 Return Types
Varchar

*******************************************************************************

DIFFERENCE (Transact-SQL)
Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions.

 Syntax
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

DIFFERENCE ( character_expression , character_expression )

 Arguments
character_expression
Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.

 Return Types
int

*******************************************************************************

LEFT (Transact-SQL)
Returns the left part of a character string with the specified number of characters.

 Syntax
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

LEFT ( character_expression , integer_expression )

 Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convertcharacter_expression.
integer_expression
Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned. If integer_expression is type bigint and contains a large value, character_expression must be of a large data type such as varchar(max).

 Return Types
Returns varchar when character_expression is a non-Unicode character data type.
Returns nvarchar when character_expression is a Unicode character data type.

*******************************************************************************

LEN (Transact-SQL)
Returns the number of characters of the specified string expression, excluding trailing blanks.

 Syntax
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

LEN ( string_expression )

 Arguments
string_expression
Is the string expression to be evaluated. string_expression can be a constant, variable, or column of either character or binary data.

 Return Types
bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

*******************************************************************************

LOWER (Transact-SQL)
Returns a character expression after converting uppercase character data to lowercase.

 Syntax
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

LOWER ( character_expression )

 Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

 Return Types
varchar or nvarchar

*******************************************************************************

LTRIM (Transact-SQL)

Returns a character expression after it removes leading blanks.

 Syntax
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

LTRIM ( character_expression )

 Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type, except text, ntext, and image, that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

 Return Type
varchar or nvarchar