Link to home
Create AccountLog in
Avatar of CeleritasPrime

asked on

split 1 column into 2 columns

Good Morning Experts,

I have a field that looks like this


but I want it to look like this

column1                      Column2
6.50                                2.50
5.00                                2.00

I have tried the following query

LEFT(copay,CHARINDEX('/'(copay)-1) as column 1
RIGHT((copay,LEN((copay)-CHARINDEX('/', (copay)) as column 2

but I get the following error:

Conversion failed when converting the varchar value '$2.60/$6.50' to data type int.
Avatar of Cluskitt
Flag of Portugal image

You're missing a ) in your LEFT function.
LEFT(copay,CHARINDEX('/'(copay)-1)) as column 1
RIGHT((copay,LEN((copay)-CHARINDEX('/', (copay)) as column 2
Correct syntax would be:
SELECT LEFT(copay,CHARINDEX('/'(copay)-1)) as 'column 1',
RIGHT((copay,LEN((copay)-CHARINDEX('/', (copay)) as 'column 2'
FROM Table

You need to use '' if your column name has a space in it.
Avatar of CeleritasPrime


That was just I typo, I actually fixed that in the query and then I got the posted error after I fixed the syntax errors sorry about that.  but is there  a different way to split the rows if the field is a number? I'm getting a conversion error.
The problem, which I failed to notice at first as well, is that you're subtracting -1 from (copay) instead of the CHARINDEX. In fact, there were lots of problems with yout (). Try:
SELECT LEFT(copay,CHARINDEX('/',copay)-1) as 'column 1',
RIGHT(copay,LEN(copay)-CHARINDEX('/',copay)) as 'column 2'
I am still getting the error
Avatar of CeleritasPrime

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
If you're using only those columns, I don't see how you can be getting that error. You can only be getting that error if you're trying to perform some implicit conversion elsewhere in your query. Please post your full query here, and I'll be happy to correct it.
To debug this, use just that select, nothing else. No joins, no WHERE, nothing. Just the SELECT FROM.
my co-worker  resolved my problem.