LEFT vs RIGHT vs SUBSTRING

Today I want to post something pretty simple, but a Jr. DBA asked me about it so I wanted to put out some quick basic logic. He was confused by how the RIGHT function worked, so I tried to explain it to him, but having a visual proof query helped him out. So, lets review before we look at the code. LEFT([string],n) will display the first n characters of the string starting on the left side of the string RIGHT([string],n) will display the first n characters of the string starting on the right side of the string (or to write this a different way, it will display the last n characters in the string) SUBSTRING([string],s,n) will display n characters of the string starting at position s So lets see this in action, here is my query:

DECLARE @Alpha VARCHAR(26) 
SET @Alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT @Alpha AS Alpha                    -- Display All characters
  , LEFT(@Alpha,5) AS [LEFT(Alpha,5)]               -- Display the first 5 characters from the Left
  , RIGHT(@Alpha, 5) AS [RIGHT(Alpha,5)]              -- Display the first 5 characters from the Right
  , SUBSTRING(@Alpha, 15,5) AS [SUBSTRING(Alpha,15,5)]          -- Display 5 characters staring at postition 15
  , SUBSTRING(@Alpha, CHARINDEX('L',@Alpha),5) AS [SUBSTRING(Alpha, CHARINDEX('L',Alpha),5)]  -- Display 5 characters Starting at "L"
  , RIGHT(@Alpha, CHARINDEX('R',REVERSE(@Alpha))) AS [RIGHT(Alpha, CHARINDEX('R',REVERSE(Alpha)))]       -- Display all Characters starting at the last "R" in the string

I added two additional results to this; the first uses SUBSTRING to return 5 characters, but rather than starting at a position, it uses CHARINDEX() to start at the first occurrence of a given string ("L" in the example above). The second extra result uses the REVERSE() function to start on the right of the string and display all characters back to the first (or last depending on how you look at it) occurrence of a string or character ("R" in my example above). This would be beneficial if you had a windows path to something and you wanted to strip out the file name...say you had the path "c:\Folder1\Folder2\Folder3\Folder4\Folder5\File.ext" All you want is File.ext, so you could write: RIGHT(@Path, CHARINDEX('\',REVERSE(@Path))). Hope that helps someone out...if you found this useful, please post a comment below, and feel free to subscribe or follow me on twitter: @EricZierdt