Transact-SQL string and datetime functions

String functions in MS SQL SERVER

The function RIGHT that complements LEFT returns a specified number of characters from the right of a character expression:

RIGHT(character_expression , integer_expression)

Here is, for example, the way to determine the names of the ships that start and end with the same letter:

Console

Execute

SELECT name 
FROM Ships
WHERE LEFT(name, 1) = RIGHT(name, 1)

SELECT name FROM Ships WHERE LEFT(name, 1) = RIGHT(name, 1)

    Scheme

The thing that we got an empty resulting set means that such ships are absent in our database. Let's take a combination - a class and a name of a ship.

The combining of two string values into one is called concatenation, and in the  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 sign "+" is used for this operation ("||" in standard). So,

Console

Execute

SELECT * 
FROM (SELECT class +' '+ name AS cn 
 FROM Ships
 ) x
WHERE LEFT(cn, 1) = RIGHT(cn, 1)

SELECT * FROM (SELECT class +' '+ name AS cn FROM Ships ) x WHERE LEFT(cn, 1) = RIGHT(cn, 1)

    Scheme

Here we separate by space the class and the name of a ship. Besides, in order not to repeat the whole construction in the function argument, we use a subquery. The result will look like this:

But what if a string expression will contain only one character? The query will output it. You can easily check it by

Console

Execute

SELECT * 
FROM (SELECT class +' '+ name AS cn 
 FROM Ships
 UNION ALL 
 SELECT 'a' AS nc
 ) x
WHERE LEFT(cn, 1) = RIGHT(cn, 1)

SELECT * FROM (SELECT class +' '+ name AS cn FROM Ships UNION ALL SELECT 'a' AS nc ) x WHERE LEFT(cn, 1) = RIGHT(cn, 1)

    Scheme

In order to exclude this case, one more useful function LEN can be used.

Suggested exercises: 35