String functions in MSSQL

Part 2

1: LEFT/RIGHT (character expression ,integer expression )

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

SELECT LEFT('Hello SQL world',7);

Result will be 'Hello S'

SELECT RIGHT('Hello SQL world',7);

Result will be 'L world'

2: LOWER/UPER (character expression) Returns a character expression after converting uppercase character data to lowercase.

SELECT UPPER('Hello sql world');

Result will be 'HELLO SQL WORLD'

SELECT LOWER('Hello SQL world');

Result will be 'hello sql world'

3: REPLACE (string expression , string pattern ,string replacement) Replaces all occurrences of a specified string value with another string value.

SELECT REPLACE('Hello Google','Google','Microsoft');

Result will be 'Hello Microsoft'

Change all Bill into Goerge:)

USE AdventureWorks2008R2 ;

SELECT FirstName , REPLACE(FirstName,'Bill','Goerge') FROM Person.Person

4 : REVERSE ( string expression ) Returns the reverse of a string value.

SELECT REVERSE('Hello SQl World');

Result Will be 'dlroW lQS olleH'.

Reverse all Firstnames in person Table.

USE AdventureWorks2008R2 ;

SELECT FirstName , REVERSE(FirstName) FROM Person.Person

5 : SUBSTRING (expression,start,length ) Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types.

SELECT SUBSTRING('Hello SQL World', 2, 3);

Result will be 'ell'

6 : RTRIM/LTRIM (character expression) Returns a character string after truncating all trailing blanks

SELECT RTRIM(' Hello SQL ');


SELECT LTRIM(' Hello SQL ');