SQL Server substring without upper bound

Question

What is the best way in SQL Server to do

SELECT 
    SUBSTRING('CATCH ME IF YOU CAN', 2, 10000)
    -- ATCH ME IF YOU CAN

with no upper bound?

Answer 1

Use STUFF instead (STUFF (Transact-SQL)):

SELECT STUFF('CATCH ME IF YOU CAN',1,1,'');

Here, STUFF replaces 1 character, from position 1 with the string ''. The 2nd parameter is the start position, and the 3rd is the number of characters (from that position) to replace. The 4th is the replacement string.

So, as a further example, you could do something like this:

SELECT STUFF('2019-07-09 11:38:00',11,1,'T');

This replaces 1 character from position 11 with the character 'T', which returns '2019-07-09T11:38:00', changing the above value to the ISO8601 format. As you can see, the length of the string to replace does not need to be the same length as the replacement string as well (in fact, the 3rd parameter can have a value of 0, meaning that no characters are replaced and the "replacement" string is simply injected into the existing value).