U-SQL get last 3 letters of a string

Question

How do I get the last 3 letters of a string using U-SQL? Normally, in SQL I use RIGHT(MyText, 3). In U-SQL that doesn't work.

Answer 1

You need to use the C# equivalents, which don't actually include an analogue for a SQL right. Consequently you need to use Substring, which naively looks like this:

MyText.Substring(MyText.Length-3)

This starts the substring at the number of characters in the first argument and, if no second argument is provided, continues to the end of the string.

Being C# however, everything is a little less user friendly than SQL and will error out if your string is less than 3 characters long, as a string that is only 2 characters long will result in a start index of 2 - 3 = -1, which is not allowed. So a slightly more robust solution would be:

MyText.Length < 3
  ? MyText
  : MyText.Substring(MyText.Length-3)

That returns the entire string when it is shorter than 3 characters. This too will have problems though, if your strings can hold null values. So to be even more robust we can add a ?? check, which is the equivalent of a SQL isnull():

(MyText ?? String.Empty).Length < 3
  ? MyText
  : MyText.Substring(MyText.Length-3)