1

If I have a string such as:

one\two\three\four\five\six\
two\three\four\five\six\one\
three\four\five\six\one\two\
four
five\six

How do I trim it so I only end up with 

one
two
three
four
five

I have tried

LEFT(column_name_goes_here, CHARINDEX('\', column_name_goes_here) - 1)

Based on the answer from SQL Server replace, remove all after certain character

But it gives me the following error message:

Msg 537, Level 16, State 3, Procedure stored_procedure_one, Line 107
Invalid length parameter passed to the LEFT or SUBSTRING function.
oshirowanen
  • 15,297
  • 82
  • 198
  • 350
  • 4
    Your CHARINDEX is looking for a forward slash and your data has a backslash. – Sean Lange Sep 29 '17 at 15:46
  • CHARINDEX('\', column_name_goes_here) - 1) – Chetan_Vasudevan Sep 29 '17 at 15:47
  • @SeanLange, sorry, that was a typo, in the actual script, it's a backslash. I've corrected the text in the question. – oshirowanen Sep 29 '17 at 15:50
  • @ChetanVasudevan, sorry, that was a typo, in the actual script, it's a backslash. I've corrected the text in the question. – oshirowanen Sep 29 '17 at 15:51
  • And fixing that means your code works perfectly. – Sean Lange Sep 29 '17 at 15:51
  • @SeanLange No, I get the same error message, I have corrected the typo of a forward slash in the question, in the actual script, it was a backslash, and the code does not work. – oshirowanen Sep 29 '17 at 15:52
  • 2
    @SeanLange is correct. With your examples, your code works fine. **However**, if you have a string *without* a backslash, it will return this error. It will return a `CHARINDEX` of 0, and when you subtract `1`, you pass a `-1` to your `LEFT` function. – Tyler Roper Sep 29 '17 at 15:52
  • @Santi, that must be it, I have some columns which do not have backslashes at all. So how do I cater for the lack of backslashes in some records? – oshirowanen Sep 29 '17 at 15:53

1 Answers1

5

To avoid such errors, add a fail-safe. Notice the + '\'

EXAMPLE

LEFT(column_name_goes_here, CHARINDEX('\', column_name_goes_here + '\') - 1)
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66