Combine Substring & replace in sql server?

I am extracting the first part of column in sql server using substring to get an order number, however some of the data has a comma after it which i would also like to remove.

This is what i’m using to extract the order number:

substring ([Column], 1, CHARINDEX(' ', [Column])) AS OrderNbr

A couple of the values are being extracted as ‘01234,’ or ‘02348,’

Is there a way i can remove the comma from the data. I tried using replace but wasnt able to combine it with substring.

Replace([Column],',',' ') as  test

>Solution :

I think you actually can combine substring and replace like this :

    REPLACE(SUBSTRING([Column], 1, CHARINDEX(' ', [Column])), ',', '') AS OrderNbr

Leave a Reply