loading..
Русский    English
23:27

LEN function

LEN(string_expression) returns the number of characters in a string. Let's bound by the case, when the number of characters is greater than one:

Console
Execute
  1. SELECT *
  2. FROM (SELECT class +' '+ name AS cn
  3. FROM Ships
  4. UNION ALL
  5. SELECT 'a' AS nc
  6. ) x
  7. WHERE LEFT(cn, 1) = RIGHT(cn, 1) AND
  8. LEN(cn) > 1

Realization of this function in MS  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server has a feature, namely, LEN() function does not take into account the trailing spaces.

Really, let's run the following code:

  1. DECLARE @chr AS CHAR(12), @vchr AS VARCHAR(12)
  2. SELECT @chr = 'abcde' + REPLICATE(' ', 5), @vchr = 'abcde'+REPLICATE(' ', 5)
  3. SELECT LEN(@chr), LEN(@vchr)
  4. SELECT DATALENGTH(@chr), DATALENGTH(@vchr)

5 5
12 10

REPLICATE function adds five blanks to the right from the constant 'abcde', which are not taken into account by LEN function, - in both cases we obtain 5.

DATALENGTH function returns number of bytes in a string representation and demonstrates us difference between CHAR and VARCHAR data types. DATALENGTH returns 12 for CHAR data type, and 10 - for VARCHAR.

As one would expect, DATALENGTH for a variable of VARCHAR data type has returned actual length of a variable. But why for a variable of CHAR data type the result became equal 12? The matter is that CHAR is a data type of the fixed length. If value of a variable is less than its length, and we have declared length as CHAR(12), value of a variable will be "leveled" till the declared length by the addition of trailing blanks.

There are tasks on the SQL-EX.RU website that require to normalize (find maximum etc.) in a numerical order a values represented in a string format, for example, a seat number in a plane ("2d") or a speed of CD ("24x"). The problem lies in a fact that the text sorts like this (ascending)

11a
1a
2a

Indeed,

Console
Execute
  1. SELECT '1a' AS place
  2. UNION ALL
  3. SELECT '2a'
  4. UNION ALL
  5. SELECT '11a'
  6. ORDER BY 1

If the sorting of places in order of rows increase is required, the order must be like this:

1a
2a
11a

In order to get such an order, the sorting by a numeric values in text is needed. I offer the following algorith:

  1. Extract a number from the string.
  2. Convert it to a numeric format.
  3. Perform a sorting by a converted value.

As we know that there's only one letter in the string, and to extract a number we can use the following construction that doesn't depend on a number of numerals in a seat number:

  1. LEFT(place, LEN(place) - 1)

If we just restrict themselves by this, we'll get

place
1a
11a
2a

Converting to a numeric format may look like the following:

  1. CAST (LEFT(place, LEN(place) - 1) AS INT)

Now we should just perform a sorting

Console
Execute
  1. SELECT *
  2. FROM (SELECT '1a' AS place
  3. UNION ALL
  4. SELECT '2a'
  5. UNION ALL
  6. SELECT '11a'
  7. ) x
  8. ORDER BY CAST(LEFT(place, LEN(place) - 1) AS INT)

As required to proof.

Previously we used the LEFT function to extract a number from a string expression, because we knew a priori how many characters should be removed from the right (one). And what if we should extract a string from a substring not by the known position of a character, but by a character itself? For example, extract all the characters before the first letter "x" (a value of CD speed).

In this case we can also use the previously considered CHARINDEX function that allows us to determine the unknown position of the character:

Console
Execute
  1. SELECT model, LEFT(cd, CHARINDEX('x', cd) -1)
  2. FROM PC


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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100