Saturday, April 29, 2017

LEFT() in SQL Server


SQL LEFT() function is used to extract a substring from a given string, starting from the left most position. LEFT() takes two parameters i.e. string and no of characters as input and returns the substring based on the parameters. LEFT() does not works on NULL Values, NULL values are not considered. When negative length is specified as parameter, LEFT() returns an error.


Syntax:


        SELECT LEFT(expression, number_of_characters)


expression is the text whose substring from left is to be extracted.

number_of_characters is a positive string which specifies how many characters of given string will be returned.



Example 1:
LEFT() on numeric column.

SELECT * FROM TempEmployeePayHistory



SELECT Rate, LEFT(Rate,3) FROM TempEmployeePayHistory



Example 2:
LEFT() on column with NULL values.

          SELECT * FROM TempEmployeePayHistory


SELECT Rate, LEFT(Rate,3) FROM TempEmployeePayHistory



Example 3:
LEFT() on text column.

          SELECT * FROM DatabaseLog
  

          SELECT DISTINCT [Event], LEFT([Event], 10) FROM DatabaseLog



Example 4:
LEFT() with negative value as no of characters.

          SELECT DISTINCT [Event], LEFT([Event], -10) FROM DatabaseLog



Example 5:
LEFT() with no of characters greater than expression length.

          SELECT DISTINCT [Event], LEFT([Event], 20) FROM DatabaseLog



Share This
Previous Post
Next Post

TekGrabs, established in 2017, is in the business of sharing Information. It provides news, articles and photos about the different technologies, inventions in different domains. Provides information on programming and databases.

0 comments: