CODEWITHSUNDEEP
sqlsql-serversplitssms
sqldan
sqldan

Reputation: 1

Splitting a column using character delimiter

Can I have some help with this SQL and splitting a column string?

The output I got at the moment

ID_Reference | Balance
  203587         -902
  203948         -111

The output I need it:

ID_Reference | Balance
  203587         902
  203948         111

The code I am using is below:

select AccountReference,CurrentBalance from SB_RentAccountBalances_V where CurrentBalance like '-%'

Thanks,

Upvotes: 0

Views: 127

Answers (4)

user7715598
user7715598

Reputation: 

SELECT ID_Reference,
       SUBSTRING(Balance,CHARINDEX('-',Balance)+1,Len(Balance))AS Balance 
 FROM SB_RentAccountBalances_V
 WHERE CHARINDEX('-',Balance)>0

Upvotes: 0

MJoy
MJoy

Reputation: 1369

You just need to replace - with blank for the desired output. Try the below code

select AccountReference,
       replace(CurrentBalance,'-','') as 'CurrentBalance'
from SB_RentAccountBalances_V where CurrentBalance like '-%'

Upvotes: 0

Ven
Ven

Reputation: 2014

I hope you want this out put

select AccountReference,abs(CurrentBalance) CurrentBalance from 
SB_RentAccountBalances_V where CurrentBalance like '-%'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You would seem to want the abs() function:

select AccountReference, abs(CurrentBalance) as Balance
from SB_RentAccountBalances_V
where CurrentBalance < 0;

Admittedly, I'm assuming that a column called CurrentBalance is actually a number and not a string.

If it is a string, then you can remove the leading -. In SQL Server, you would use:

select AccountReference, stuff(CurrentBalance, 1, 1, '') as Balance
from SB_RentAccountBalances_V
where CurrentBalance like '-%';

Upvotes: 4

Related Questions