SQL Server - substring from position to end of string

Question

I have to split a string, I need take the first 25 characters and then the others, this is my code

select  
    SUBSTRING(field, 1, 25),
    SUBSTRING(field, 26, (LEN(field)-25))
from table

but I'm getting this for the second substring:

Invalid length parameter passed to the left or substring function

What's wrong in that?

What if you don't have 25 characters? Then the SUBSTRING(field, 25, xxxx) doesn't really make sense - and if the 25 parameters is greater than the length of field, you get this error — marc_s
– marc_s, Commented Jan 21, 2021 at 16:38
LEN(column)-25 - why? Why not just substring (column, 26, 8000) or whatever the max of the column definition? Calculating the length is unnecessary. But still you need to make sure the string is at least 26 characters long and, if not, what to do in that case. — Aaron Bertrand
– Aaron Bertrand, Commented Jan 21, 2021 at 17:19

Gordon Linoff · Accepted Answer · 2021-01-21 16:38:43Z

9

You can use stuff():

select left(field, 25),
       stuff(field, 1, 25, '')

The problem is that substring() doesn't accept a negative length, which your code calculates.

answered Jan 21, 2021 at 16:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1