Charindex () counts white characters at the end, len () doesn't work in T-SQL

Question

I want to find the index of the last character /, but the problem is that it LEFT(target, LEN(target) - CHARINDEX('/', REVERSE(target)))
doesn’t work, because the row in the target column has many space characters at the end, and the function charindexincludes spaces, but it lendoesn’t work.

Is there any other function to replace one of them?

Answer 1

RTRIM truncates trailing spaces.

LEFT(target,
     LEN(target) - CHARINDEX('/', REVERSE(RTRIM(target))))

Answer 2

Yes, LEN () does not take into account trailing spaces. Use DATALENGTH instead, but keep in mind that it counts bytes, not characters, so if you use it by the values ​​of NVARCHAR (), you will have to divide it by 2.