cast varchar to decimal

did u check this


SELECT * FROM urTable where ISNUMERIC(urFiled) = 0

Hi, I tried all of these and they work fine for me:

select cast('98.75' as decimal(19,2))
select cast('9803.00' as decimal(19,2))
select cast('98030.00' as decimal(19,2))
select cast('99.05' as decimal(19,2))
select cast('.71' as decimal(19,2))
select cast('.70' as decimal(19,2))
select cast('.00 ' as decimal(19,2))

Are you sure that you don't have any special characters in there?

aneesh, it's a varchar field, won't they all come back for that?  I just ran it, selecting distinct fieldname where isnumeric = 0, i get back 2269 records --- but they all appear numeric to me:  

22.77
88280.00
2100.00
9523.00
9517.50
14730.00
.00
9083.00


hi chap, i tried that just now, but not on the specific vals, i did this:
select cast(fieldname as decimal(19,2)) from table

same error converting data type varchar to numeric

lemme tell you what i think caused this, maybe that will help.  i have a job which writes out the files nightly like this:

SET @sql =
'bcp "select * from ....... where timeofsnapshot BETWEEN left(getdate()-1,11) AND left(getdate(),11) " queryout "\\server\\G$\MSSQL\ARCHIVE\filename_'+convert(varchar(12),getdate()-1,112)+'.txt" -T -c'
EXEC master..xp_cmdshell  @sql

very recently i had to restore all historical data, i'm trying to load them in, most went in just fine, but two months all fail on the last column.  error converting the datatype to numeric
i don't know what/why, but i assumed maybe a problem w/the end-of-row terminator or something (not sure...)

the only way i could load them was by changing that last column from decimal((18,2)
to varchar(10)

now i'm trying to get those two months into the table properly so this historical audit can occur

(and then i need to figure out what caused this)

OK...then it is for sure some funky value in there causing it.

but how do i find it, chap?  or at least get around it

try this:

create table #t(fldval varchar(10))

insert into #t
select '98.75'
union
select '98030.00'
union
select '.71'
union
select '98._75'

this does NOT work:
select cast(fldval as decimal(18,2))
from #t

this does work:
select cast(fldval as decimal(18,2))
from #t
where isnumeric(fldval) = 1

well, chap, that works on the #t table, those values, minus the underscored number..... but when i run the same thing on my table, i get nothing back

is there a means of updating or something?

oh hell.  

the dude looking for this just told me they won't need this field for the reports they're needing to run, so if i can load without, they won't mind.

good.

but i still would like to know how to resolve this, should it recurr in the future

also, do either of you have any idea if my bcp may have caused this?  it makes no sense, i pulled 100M records out of this table, daily files since 5/16/06, and only 1.5 months is doing this to me
i don't get it

another table, same darned scenario -- three fields, the last one is decimal(18,2)
these files are generated precisel the same as what i posted up there.
the data load keeps failing to convert datatype varchar to numeric on that last attribute  --- BUT only on SOME of the files.  
I load it into a temp table where field3 is varchar(10), and for the life of me, i cannot cast the darned thing to decimal  

are there any suggestions at all?

this is becoming terribly pressing.  any ideas at all?

WEll, this is kind of a hack way to do it, but you could use a cursor to loop through the values in that column and try to cast them one at a time....when you run into the error, you can inspect the data it errored on to see what the problem might be.

i'm not sure how to cursor through that, chap.  let me see what i can do

declare @var1 varchar(10), @var2 decimal(20,4)

DECLARE  CursorTemplate CURSOR
FAST_FORWARD FOR       
      SELECT yourvarcharvalue FROM Table1

OPEN CursorTemplate

FETCH NEXT FROM CursorTemplate
INTO      @VAR1

WHILE (@@FETCH_STATUS = 0)
BEGIN
begin try
select @var2 = cast(@var1 as decimal(20,4))

end try
begin catch
select 'Error at ' + @var1
end catch

      FETCH NEXT FROM CursorTemplate
      INTO      @Var1, @Var2, @Var3

END

CLOSE CursorTemplate
DEALLOCATE CursorTemplate

oh lord... table count = 815816.    isnumeric = 1 count = 8

a distinct count comes back with 44543, there are some negatives in this one... like this:   -.0350

these are the 8 that come back good:
283.0500
260.0000
455.1000
260.8500
130.0000
65.0000
65.0000
761.2200

so i go in just to look at the bad stuff, using a cpl of the good ones as boundaries
select field from table where field between 260.0000 and 260.8500 and isnumeric(field)=0

these things look like f**** numeric vals to me:
260.4360
260.4000
260.6300
260.8500

thats really odd...i just casted all of them to numeric and it worked fine.  

show me chap, please.  i can run this just fine:
   select cast('260.4360' as decimal(18,4))  

but this fails every single time w/the error:
  select cast(field as decimal(18,4)) from tablename

I can't...I don't have your data set.

select * from table where isnumeric(field)=0     815808 of them.... all looking like this:
(it's the 3rd field)

4897793   Clear      157.6260       
4897794   Clear      563.2630       
4897795   Clear      1304.7760

are you sure you're providing the correct fieldname to the function?

yessir

i just remembered this:
https://www.experts-exchange.com/questions/23222708/error-converting-varchar-to-numeric.html?sfQueryTermInfo=1+convert+data+decim+error+numer+type+varchar

and i just tried this:
select cast(fieldname + char(10) as money) from table

it works in the select

what do you think, chap?

and get this ..

select fieldname, cast(fieldname + char(10) as money) from table
157.6260    157.6260      
563.2630     563.2630      
1304.7760   1304.7760      
2321.2820    2321.2820      

the only thing notably different is the casted field has a bunch of space to the right of it.  but i believe i'm doing that with my char(10)

hmm...char(10) is a carraige return.  you could try to update your column and remove them all:

set field = replace(field, char(10), '')

i did that, but no difference

i tried this:  set field = convert(money,amount) where isnumeric(field)=0
it fails with this:

Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type money to data type varchar,

Use the CONVERT function to run this query.

is the last piece of that disallowed error.  

do you know the convert, chap?

sh*t.   i did this:
update table set field = cast(cast(field as money) as varchar) where isnumeric(field)=0
813191 records updated

now this works:
select cast(field as decimal(18,4)) from table

old value         new value
1304.7760       1304.78

i wonder if there's any way i can avoid rounding it like that

the cast to money did it...use decimal(18,4) instead.

ugh

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

did you say that this didn't work?

declare @x varchar(10)
set @x = '12.234' + char(10)

select isnumeric(replace(@x, char(10), ''))

i don't know what you're asking, chap.  
i do what you posted, i get back 1
i do that on my data, i get back a bunch of 0's, a few 1's, too.   ( i loaded some more files)
i've got 5222 1's and 2141131 0's

i cannot use the piece that 'worked' for me that i posted -- it rounds the values and creates dupes
table definition:
QHID      int
Type      varchar
Amount      decimal

the three combined cannot be duplicated
if i do this:
cast(cast(amount as money) as varchar) where isnumeric(amount)=0

i create dupes when the data is rounded

many profanities
i have got to get this done yesterday

that table def is for the one i am trying to write into.  not the one i had to create in order to load the data.... in that one, amount is varchar(10)

any thoughts?

Alter the table to create an additional Varchar(10) field
 and  use this function tto populate the value on that column

update urTable
set newColumn = dbo.fnRetNumeric (otherColumn )
and try to do a cast on that new column

CREATE FUNCTION dbo.fnRetNumeric (
@inString varchar(10)
) RETURNS varchar(10)
AS

BEGIN
    DECLARE @newstring varchar(10),@i int
    SELECT @i = 1
    SET @newstring = ''
    WHILE @i < len(@inString)+1
    BEGIN
        IF NOT ((SUBSTRING(@inString, @i, 1) >= '0' and SUBSTRING(@inString, @i, 1) <= '9') or SUBSTRING(@inString, @i, 1)  = '-' OR SUBSTRING(@inString, @i, 1)  = '.' )
            RETURN 0
        SET @newstring =@newstring +SUBSTRING(@inString, @i, 1)
        SET @i = @i + 1
    END
    RETURN (@newstring)
END
go

excellent, aneesh. thank you.  i haven't tried it yet, but i will.  i added the new column already, actually, thinking of a similar approach, but it just wasn't happening syntactically.  i will give this a go and let you know.

I must have done something in error, aneesh.  new column, all values are 0.  example --

qhid              type               amount       amount2
5395143      NFA      .0200       0
5395145      Clear      .5500       0
5395145      ExchFee      7.7000       0

that was after performing the update, using the function

yep, there are no alphas in there.  i will try this.

aneesh, that worked.  i am not fully understanding, though.  can you clarify for me, please?

i think there were some 'return' characters

just like space after the visible data?

in both cases that this happened (two tables), the problem attribute was the last in the table.  i'm wondering if my extract is doing this to me  --  do you think this is what may have caused this?

SET @sql =
'bcp "select * from ....... where timeofsnapshot BETWEEN left(getdate()-1,11) AND left(getdate(),11) " queryout "\\server\\G$\MSSQL\ARCHIVE\filename_'+convert(varchar(12),getdate()-1,112)+'.txt" -T -c'
EXEC master..xp_cmdshell  @sql

thank you both for all of the input on this.  i really do appreciate it.