SQL - Change from VARCHAR to NUMERIC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL - Change from VARCHAR to NUMERIC

Author

Topic

Indyyan
Starting Member

31 Posts

Posted - 2004-07-26 : 06:31:01

Hello!

I want to use the Analysis Server and therfore i need to convert a few fields of my table from varchar to numeric

The problem is that i get a error message when try to convert!

I used a query to look which data in field Utb_S_ is numeric:


SELECT Utb_S_ FROM TV_Order WHERE isnumeric(Utb_S_) = 0

the numeric values look like this:

96,41379310344820000
86,25705601389490000
94,51111111111110000
93,12235235484670000
99,45454545454540000

but I also have values in my tables which are not numeric


SELECT Utb_S_ FROM TV_Order WHERE isnumeric(Utb_S_) = 1

Here are the non-numeric values

0,0
74,90909090909
85,13200498132
77,53086419753
187
70,21052631579
78

Is there a way to convert the non-numeric values???

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 07:18:10

Is there some sort of padding on the end which is upsetting SQL server?

Kristen

Indyyan
Starting Member

31 Posts

Posted - 2004-07-26 : 07:34:15

Sorry, I don´t get your point. Do you mean that the non numeric data has some sort of upsetting padding which i didn´t mentioned in the non-numeric list above???

I don´t think so, the data looks like the list above.

But maybe i misunderstand your posting??

the whole data is VARCHAR(255)

I imported the data from textfiles, the first are form 94 and ends 2004
The old data looks like the non-numeric values, the system which runs with 4D got changed 2000 and all the new data is numeric.

Indyyan
Starting Member

31 Posts

Posted - 2004-07-26 : 07:46:58

by the way...

The only difference i can see, between numeric and non-numeric is the number of decimal places.

Is this a problem for SQL??

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 09:07:00

No, it will be happy with any number of decimal places.

I was seeing that difference too and thinking "there must be something on the end of the data which is a) not numeric and b) I cannot see!"

You could try

SELECT TOP 20 DATALENGTH(Utb_S_), Utb_S_ FROM TV_Order WHERE isnumeric(Utb_S_) = 1

to see how "wide" SQL thinks the data is, which might indicate some sort of invisible padding.

(I presume your system is set to use COMMA as the decimal separator?)

Kristen

Indyyan
Starting Member

31 Posts

Posted - 2004-07-26 : 09:23:53

Thanks for the help!!!

I tried your Query and get this result

1 0
14 99,95918367347
2 16
4 99,5
2 16
2 40

If i use the same query with the numeric values, i get 20 for every value!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-07-26 : 09:32:55

Wait a minute, Indyyan! Looking at your posts, it seems you've got the workings on the IsNumeric function the other way around. Just so that we are all talking at the same wavelength - the function will return 1 for a numeric value, and 0 for a non-numeric value. Is that how you think it works?

OS

Indyyan
Starting Member

31 Posts

Posted - 2004-07-26 : 09:40:27

Hmmm, i didn´t consider that....

If i use 1 for the numeric value i get all those which i expect to be non-numeric:

-14
96,125
97,9754601227
85
78

if i use 0 as numeric i get those results which all have the same amount of decimial places
I assume that those are the numeric values i want

?????

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 10:05:08

Looks like its the comma, but for the life of me I can't tell you why. The red ones fail here:


SELECT '1', IsNumeric('1')
UNION ALL SELECT 'XXXX', IsNumeric('XXXX')
UNION ALL SELECT '96.41379310344820000', IsNumeric('96.41379310344820000')
UNION ALL SELECT '74.90909090909', IsNumeric('74.90909090909')
UNION ALL SELECT '96,41379310344820000', IsNumeric('96,41379310344820000')
UNION ALL SELECT '74,90909090909', IsNumeric('74,90909090909')
UNION ALL SELECT '1,2', IsNumeric('1,2')
UNION ALL SELECT '1,23', IsNumeric('1,23')
UNION ALL SELECT '1,234', IsNumeric('1,234')
UNION ALL SELECT '1,2345', IsNumeric('1,2345')
UNION ALL SELECT '1,23456', IsNumeric('1,23456')
UNION ALL SELECT '1,234567', IsNumeric('1,234567')
UNION ALL SELECT '1,2345678', IsNumeric('1,2345678')
UNION ALL SELECT '1,23456789', IsNumeric('1,23456789')
UNION ALL SELECT '1,234567890', IsNumeric('1,234567890')
UNION ALL SELECT '1,2345678901', IsNumeric('1,2345678901')
UNION ALL SELECT '1,23456789012', IsNumeric('1,23456789012')
UNION ALL SELECT '1,234567890123', IsNumeric('1,234567890123')
UNION ALL SELECT '1,2345678901234', IsNumeric('1,2345678901234')
UNION ALL SELECT '1,23456789012345', IsNumeric('1,23456789012345')
UNION ALL SELECT '1,234567890123456', IsNumeric('1,234567890123456')
UNION ALL SELECT '1,2345678901234567', IsNumeric('1,2345678901234567')

UNION ALL SELECT '1.2', IsNumeric('1.2')
UNION ALL SELECT '1.23', IsNumeric('1.23')
UNION ALL SELECT '1.234', IsNumeric('1.234')
UNION ALL SELECT '1.2345', IsNumeric('1.2345')
UNION ALL SELECT '1.23456', IsNumeric('1.23456')
UNION ALL SELECT '1.234567', IsNumeric('1.234567')
UNION ALL SELECT '1.2345678', IsNumeric('1.2345678')
UNION ALL SELECT '1.23456789', IsNumeric('1.23456789')
UNION ALL SELECT '1.234567890', IsNumeric('1.234567890')
UNION ALL SELECT '1.2345678901', IsNumeric('1.2345678901')
UNION ALL SELECT '1.23456789012', IsNumeric('1.23456789012')
UNION ALL SELECT '1.234567890123', IsNumeric('1.234567890123')
UNION ALL SELECT '1.2345678901234', IsNumeric('1.2345678901234')
UNION ALL SELECT '1.23456789012345', IsNumeric('1.23456789012345')
UNION ALL SELECT '1.234567890123456', IsNumeric('1.234567890123456')
UNION ALL SELECT '1.2345678901234567', IsNumeric('1.2345678901234567')

EDIT: Perhaps its the number of significant figures
EDIT2: Blue ones are all OK
Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 10:11:21

Because it's malformed?

SELECT '7,490,909,090,909', IsNumeric('7,490,909,090,909')

Brett

8-)

Indyyan
Starting Member

31 Posts

Posted - 2004-07-26 : 10:16:20

Thanks for the help, that´s really crazy...

Ok but what shall i do if it´s malformed??

How can i convert the data or is it impossible?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-07-26 : 10:38:24

I don't think it's malformed, Brett. This is decided by your locale settings. Many parts of Europe use the comma in numbers where we would use a period and vice versa. Try this (in Windows 2000): Go to the Regional options in the Control Panel, and change your locale to Italian. Now take a look at the examples in the Numbers tab. You'll see the number 123,456,789.00 change to 123.456.789,00 . So maybe 74,90909090909 is a very valid number of datatype numeric (13, 10). Only problem here is that the IsNumeric function seems have some wierd rules for determining what is numeric and what is not

Indyyan
Starting Member

31 Posts

Posted - 2004-07-26 : 10:51:27

Thanks mohdowais!!

Did you consider that my local setting are Swedish and therfore the difference looks not like you example.

In Sweden are numbers displayed like this 123 456 789,00
and if i change to italian options: 123.456.789,00 it looks like this.

I think I have to change the non-numeric values not the settings!
???

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 11:55:36

I don't think IsNumeric() is looking at the locale because I am using UK format which is 1,234,567.89 and IsNumeric('1,23') is OK (but a long number is NOT - UNLESS it uses a DOT for the decimal point). I have no idea why commas should be permitted in this way, looks like a bug to me.

I modified my post, above, to add numbers with large numbers of decimcal digits AND use DOT for the decimal place and they are all fine.

Kristen

Indyyan
Starting Member

31 Posts

Posted - 2004-07-26 : 12:03:22

Thanks for all your help Kristen...

is there a way to change just the comma to a dot without changing the value???

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 12:16:35

SELECT REPLACE(Utb_S_, ',', '.') ...

Indyyan
Starting Member

31 Posts

Posted - 2004-07-26 : 12:31:48

Thanks once again, but how can i select more fields then just the Utb_S_ and how should i act to change just Utb_B_, Pris_F and Pris_M

and all other fields should keep their comma sign. There are some with text which would be irritating if there is a dot for every comma

If this work, i would export to a csv file and import the whole table again as a new TV_Order. Or can i just transform the comma to dot sign in the existing table??

Thanks again...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources