I need to convert a varchar string to decimal type. I thought i could use to_decimal function to do this but it requires the decimal & thousandth-place delimiters and I don’t know what to enter there.
For example:
Source string (varchar) = ‘0001234568’
Desired target (decimal(8,2) = 12345678.00
I tried => to_decimal(
Can anyone offer some suggestions? It’s probably an obvious answer but I can’t think right now. 
Thanks for any advice!
bluesky 
(BOB member since 2006-03-02)
Hi
You need to put the comma and full stop within quotes, like below:
to_decimal(
I think this was one of the things I kept doing when I first started!! 
gooian 
(BOB member since 2006-01-13)
for such simple cases you can just assign the varchar value to a decimal column. Remove the to_decimal() function and you are done.
Werner Daehn 
(BOB member since 2004-12-17)
Ah, thanks, wdaehn…I actually tried that before but overlooked the error (dirty data) and thought it wouldn’t let me map varchar to decimal directly. 
Thanks also, gooian…sorry, bad me! that was a typo on this post. I had used the wizard so it would have been correct there. Like I said, just couldn’t think properly last night!
I really appreciate this!
bluesky 
(BOB member since 2006-03-02)
to_decimal(‘QT_SortBy_OpportunitintyName.“Booking Value”’,’.’,’,’,3)
never works, and throws “9752 15216 RUN-050304 19/10/2016 20:06:57 Function call <to_decimal ( QT_SortBy_OpportunitintyName.“Booking Value”, ., , 3 ) > failed, due to error <50802>: <Cannot
convert data <QT_SortBy_OpportunitintyName.“Booking Value”> into type . Context: Column <Project Column Booking Value”
Neither Cast funtion is working in my case.
Values coming in char are as follows:
1,14,83,300
14,83,300
Please reply as fast, thanks.
Parijatam (BOB member since 2016-09-08)