String functions in MSSQL

Part 1

In this article I will try to write some examples of string functions in MSSQL

1 : ASCII (expression) - If you want to find numeric value of a character .

SELECT ASCII('b') as LOWER_CASE_B

Result will be 98

SELECT ASCII('B') as UPPER_CASE_B

Result will be 66

2 : LEN(expression) To get the length of a string

DECLARE @test VARCHAR(50);

SET @test = 'this is test example';

SELECT LEN(@test);

Result will be 20

3 :DIFFERENCE(expression,expression) Function is commonly used to search with names.

select DIFFERENCE('SQL','sql1');

Result will be 4

Select all firstname and lastname from person table where

firstname is Similar "Roberto"

USE AdventureWorks2008R2;

SELECT FirstName, LastName FROM Person.Person

WHERE DIFFERENCE(Person.Person.FirstName , 'Roberto') =4;




4 : CHARINDEX (expression1 ,expression2, start location ) - Searches expression2 for expression1 and returns its starting position if found. The search starts at start location.




SELECT CHARINDEX('SQL','Hello SQL world');

Result will be 7

SELECT CHARINDEX('SQL','Hello SQL world',8);

Result will be 0