loading..
Русский    English
20:32
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
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.