Substring without the first n characters

Question

I'm developing a SQL Server 2016 stored procedure and I want to get the last characters of a varchar(38) column.

I know there will always be at least 18 characters and I don't know the exact length of the column, because it is variable.

I think I can get the length of the column and do a subtract to use SUBSTRING, but I can't do that because I'm doing this:

set @externalCodes = (
    select Serial, AggregationLevel
      from ExternalCode where ProductionOrderId = @productionOrderId
    for json path

I'm generating a JSON and I don't know how to get the length of each Serial column inside a select.

My question is: how can I get a substring from a string without the first 18 characters without knowing its length?

One solution could be:

SUBSTRING(Serial, 18, 38)

And it always return the substring from 18 to the end of the string even if the string doesn't have a length of 38.

Answer 1

I would use:

RIGHT(RTRIM(Serial),LEN(Serial)-18) 

This gets the length of the field and subtracts 18 from it, leaving you with everything after the 18th character.

Answer 2

Your SUBSTRING solution seems good enough, I am not sure why you would need to ask for anything more. I would only like to note that if you want to skip the first 18 characters, then you should specify 19 as the second argument for SUBSTRING, because in SQL the character positions in a string start from 1. So this makes perfect sense and should work well for you:

SUBSTRING(Serial, 19, 38)

If you specify 18, then you will be skipping 17 characters.

The solution using RIGHT and LEN, suggested by George.Palacios will work as well but the results of these two solutions might not be the same depending on whether Serial can have trailing spaces. The reason for the possible difference is that the LEN function ignores trailing spaces, while RIGHT does not, which means that for a string with trailing spaces the result will most likely not be what you would expect.

Let me demonstrate that using a simple example with a shorter string. Suppose the maximum length is 10, the number of characters to skip is 4, and the specific string is 'abcde  '. (The quotation marks here are just string delimiters, for you to be able to see the trailing spaces.) If you do

SUBSTRING('abcde  ', 5, 10)

you will get 'e ' as the result, because SUBSTRING will simply start cutting the string at the 5th character and include all the characters up to the end of the string.

In contrast, the RIGHT/LEN option

RIGHT('abcde  ', LEN('abcde  ') - 4)

will yield ' '. The LEN function will ignore the two trailing spaces and return 5. Subtracting 4 from 5 gives you 1, thus RIGHT will return just one rightmost character of the string, which is a space.

As I said, though, if Serial can never have trailing spaces, either option will do. For completeness, let me suggest one more, which uses the STUFF function:

STUFF(Serial, 1, 18, '')

The STUFF function allows you to delete a substring from and/or insert another one to a given string at a given position. So, in order to skip 18 characters using this function, you specify starting position 1, 18 characters to delete, and an empty string ('') to replace them with. Like SUBSTRING, this solution will correctly work with trailing spaces, should you have the need to take them into account.