charindex() counts whiteshars in the end, len() doesn't in T-SQL

Question

I want to find an index of the last '/' character, but the problem is that
LEFT(target, LEN(target) - CHARINDEX('/', REVERSE(target)))
doesn't work because the string in target column has a lot of space characters in the end and the charindex function includes the spaces, but len doesn't. Is there any other function to replace one of them?

Answer 1

The RTRIM function trims the trailing whitespace.

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

Answer 2

Yes, LEN() does not count trailing whitespace. Use DATALENGTH instead, but be aware that it counts bytes, not characters, so if you use it on NVARCHAR() values, you'll have to divide it by 2.