REVERSE function

This function reverses a string, just like reading it from the right to left. That is the result of the query

SELECT REVERSE('abcdef')

will be ‘fedcba’. In case of the absence of the RIGHT function in the language the query

SELECT RIGHT('abcdef', 3)

can be equally replaced with the query

SELECT REVERSE(LEFT(REVERSE('abcdef'), 3))

I see the profit of this function in the following. Let we need to determine a position not the first, but the last occurrence of any character (or a sequence of characters) in a character string. Let’s remind an example, where we were determining the position of the first character “a” in the name of the ship “California”:

SELECT CHARINDEX('a', name) first_a
FROM Ships
WHERE name = 'California'

Now let’s determine the position of the last occurrence of the character “a” in this name. The function CHARINDEX(‘a’, REVERSE(name)) will allow us to find this position, but from the right. In order to get the position of this character, but from the left, it’s enough to write

SELECT LEN(name) + 1 - CHARINDEX('a', REVERSE(name)) first_a
FROM Ships
WHERE name = 'California'