Trim a character on SQL-CodePudding

Hi everyone this is my first time that i asked something.

I have multiple codes on my db with numbers and '/', for example:

510325205
510325205/000/01
510565025-01
510565025-01/090/03
...

I need to trim the / - I need these results:

510325205
510325205
510565025-01
510565025-01
...

I already searched and tried this

left(code, charindex('/', code))

and it works for the codes with / in it, but the codes without / are excluded from the results.

Thanks for your help!

CodePudding user response：

Your try was very close. All you needed to add was -1.

As you can see the explanation for the left() function:

The LEFT() function extracts a number of characters from a string (starting from left): LEFT(string, number_of_chars)

With charindex() function you have told the left() function how many characters to take. By adding -1 to that you have removed the '/' sign because you have told the LEFT() function to take 10 characters and not 11(for example) because 11th character is '/'.

select left('510325205/000/01',charindex('/','510325205/000/01')-1)

or because you have column named code

select left(code,charindex('/',code)-1)

If you have values without / you can use this:

select case when charindex('/',code_c)-1  = -1
            then 
            code_c
            else
            left(code_c,charindex('/',code_c)-1) 
            end RESULT
from test

select left(code_c,iif(charindex('/',code_c)-1 = -1, len(code_c), charindex('/',code_c)-1)) 
from test

Here is DEMO