loading..
Русский    English
01:55
go

LEFT function

LEFT function has the following syntax:

  1. LEFT (character_expression , integer_expression )
and returns the part of a character string starting at a specified number of characters from the left. So,

Console
Execute
  1. SELECT DISTINCT LEFT(name, 1) 
  2. FROM Ships 
  3. ORDER BY 1

And this is the way to get the table of codes of all alphabetical characters:

Console
Execute
  1. SELECT CHAR(ASCII('a')+ num-1) letter, ASCII('a')+ num - 1 [code]
  2. FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num 
  3.  FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 
  4.  UNION ALL SELECT 4 UNION ALL SELECT 5
  5.  ) x CROSS JOIN 
  6.  (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 
  7.  UNION ALL SELECT 4 UNION ALL SELECT 5
  8.  ) y CROSS JOIN 
  9.  (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 
  10.  UNION ALL SELECT 4 UNION ALL SELECT 5
  11.  ) z 
  12.  ) x
  13. WHERE ASCII('a')+ num -1 BETWEEN ASCII('a') AND ASCII('z')

Here we use an algorithm of generating of numerical sequence, which is referred to Chapter 8.

It is known that code values of lowercase letters and capital letters differ. That's why in order to get the whole set without rewriting above query; it will be enough to write up the identical code to aforesaid:

  1. UNION
  2. SELECT CHAR(ASCII('A')+ num-1) letter, ASCII('A')+ num - 1 [code]
  3. FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num 
  4.  FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 
  5.  UNION ALL SELECT 4 UNION ALL SELECT 5
  6.  ) x CROSS JOIN 
  7.  (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 
  8.  UNION ALL SELECT 4 UNION ALL SELECT 5
  9.  ) y CROSS JOIN 
  10.  (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 
  11.  UNION ALL SELECT 4 UNION ALL SELECT 5
  12.  ) z 
  13.  ) x
  14. WHERE ASCII('A')+ num -1 BETWEEN ASCII('A') AND ASCII('Z')

Now let's consider the task of searching a substring in a string expression. The two functions can be used for this: CHARINDEX and PATINDEX. They both return a starting position (a position of the first character of a substring) of a substring in a character string.

Bookmark and Share
The book was updated
yesterday
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100