Solved

cast varchar to decimal

Posted on 2008-06-26
41
5,654 Views
Last Modified: 2008-06-30
one field - varchar(10) - contains a whole bunch of this:
98.75
9803.00
98030.00
99.05
.71
.70
.00

i MUST cast as decimal asap.  i tried this:

cast(field as decimal(18,2))

it fails:  error converting data type varchar to numeric

i've checked the min/max/avg column lengths.  i have this:

min        max        avg
4             9            4

there are no NULLs, no alpha characters, no negative values
can anyone help?
is anyone able to advise?
0
Comment
Question by:dbaSQL
  • 25
  • 12
  • 4
41 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21874149
did u check this


SELECT * FROM urTable where ISNUMERIC(urFiled) = 0
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21874159
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?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21874202
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21874241
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)

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21874243
OK...then it is for sure some funky value in there causing it.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21874255
but how do i find it, chap?  or at least get around it
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21874286
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

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21874337
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21876440
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?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21876535
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.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21876558
i'm not sure how to cursor through that, chap.  let me see what i can do
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21876592
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
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 100 total points
ID: 21876599
whoops:

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
END

CLOSE CursorTemplate
DEALLOCATE CursorTemplate
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21876656
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



0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21876708
thats really odd...i just casted all of them to numeric and it worked fine.  
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21876743
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
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21876775
I can't...I don't have your data set.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21876797
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
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21876817
are you sure you're providing the correct fieldname to the function?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21876827
yessir

i just remembered this:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23222708.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?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 17

Author Comment

by:dbaSQL
ID: 21876848
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)
0
 
LVL 60

Expert Comment

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

set field = replace(field, char(10), '')
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21876954
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,
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21877003
Use the CONVERT function to run this query.

is the last piece of that disallowed error.  

do you know the convert, chap?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21877125
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
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21877145
the cast to money did it...use decimal(18,4) instead.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21877207
ugh

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

Expert Comment

by:chapmandew
ID: 21877256
did you say that this didn't work?

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

select isnumeric(replace(@x, char(10), ''))
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21877380
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21877387
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)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21878231
any thoughts?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21878353
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21879526
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.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21883285
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21883291
that was after performing the update, using the function
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 21883449
If you are sure that the columns have no alphabets , update the new column using this


ALTER 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)  = '.' )
           SET @i = @i + 1
        ELSE
        BEGIN    
                  SET @newstring =@newstring +SUBSTRING(@inString, @i, 1)
                  SET @i = @i + 1
            END
    END
    RETURN (@newstring)
END
go
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21883490
yep, there are no alphas in there.  i will try this.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21885765
aneesh, that worked.  i am not fully understanding, though.  can you clarify for me, please?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21885906
i think there were some 'return' characters
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21886145
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 21898703
thank you both for all of the input on this.  i really do appreciate it.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now