I am using the following code to get the string before and after the pipe character |
The data looks like this:
AMOUNT
1000
28373|7000
72727
82773|1090
5000
However, if the character doesnt exist, the result is blank. So, the values 1000, 72727 and 5000 would not be picked up.
How can I pick up the left and right strings if they exist but also pick up the string if the | character is not there?
Any assistance here would be appreciated
SUBSTRING(AMOUNT,0, CHARINDEX('|',AMOUNT)) AS AMOUNT1
SUBSTRING(AMOUNT, CHARINDEX('|', AMOUNT) + 1, LEN(AMOUNT)) AS AMOUNT2