4

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?

iamdave
  • 12,023
  • 3
  • 24
  • 53
agnieszka
  • 14,897
  • 30
  • 95
  • 113
  • 1
    what version of sql server? Doesn't it suck that SQL Server doesn't have anything straightforward to do such a thing? OR am I being ignorant? – shahkalpesh Aug 10 '09 at 07:07
  • its funny that it shows a related question that you asked yesterday. Here - http://stackoverflow.com/questions/1248512/t-sql-getting-almost-last-substring-from-delimited-string – shahkalpesh Aug 10 '09 at 07:16

2 Answers2

4

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
2

The RTRIM function trims the trailing whitespace.

LEFT(target,
     LEN(target) - CHARINDEX('/', REVERSE(RTRIM(target))))
Harold L
  • 5,166
  • 28
  • 28
  • If you are interested in counting the white space this solution doesn't help. As RBarryYoung [suggests](http://stackoverflow.com/a/1253423/692942) use `DATALENGTH()` instead but always remember to divide it by 2 if working with unicode data as it counts bytes not characters. – user692942 Apr 20 '15 at 09:37