Hi! Until now I have a varchar field containing decimal numbers. I want
too convert the field to decimal(10,5). This will be no problem because
all the numbers in the varchar field is now true decimal numbers. The
problem is that the customers using the application writes for example
120,0. And they actually expect that the number will come out as 120,0.
When testing with the decimal, numeric and float datatypes the decimal
dissapears because it is a whole number. Any way too keep the zero
decimal?

Thanx

Henning :-)

*** Sent via Developersdex http://www.developersdex.com ***

A number doesn't have a format. SQL Server does not remember anything like that. In fact, you cannot
use comma as decimal separator for numbers in SQL Server for input. I.e., you need to store user
preferences somewhere and do this presentation in the client application if you want to store this
as some numerical datatype.

Show quoteHide quote

Thanks!

Forgot to say that the SQL Server is in Norway. Here in Norway we
actually use comma as a decimal separator. It was kind of a luxary
problem. Thanks again for the quick reply!

Henning :-)

*** Sent via Developersdex http://www.developersdex.com ***

Hej Henning,

We use comma in Sweden as well ;-). So I know all about your situation. Don't mix data with
presentation of data. Do formatting in the client app, and the app can optionally follow the
preferences in regional settings of the client machine.

Show quoteHide quote

Hei Tibor!

Thanks for the answer. I have one other question for you. I have now
prepared for converting the whole column from varchar(50) to decimal
(18,10). But what ever I do, I just get a message telling me that there
is an error converting from varchar to numeric data type. The varchar
column should now only have "legal" decimal numbers stored (comma as
separator), and all empty string rows has been updated to be NULL values
in case that was the problem. I have also tried to create another row,
copy the data over. replace all the comma separators with a period(.)
Then I tried to do the conversion. No better luck there. I also tried
making a new row of decimal type and then copy the data from the varchar
column. I have tried to have a look at the data in the varchar column to
see if something is wrong, but it seems OK. Any chance of helping me
with this one?

Henning :-)

*** Sent via Developersdex http://www.developersdex.com ***

On Tue, 07 Jun 2005 08:36:47 -0700, kongsballa wrote:

Show quoteHide quoteHi Henning,

Try the following (monstrous - I know!) query. I think that it will
catch all the values that can't be converted to decimal(18,10). This
assumes that MyColumn is a varchar column, and that all leading and
trailing spaces are trimmed. If they are not, replace each occurence of
MyColumn below with "LTRIM(RTRIM(MyColumn))", to make the query yet more
monstruous. If you also want to eliminate the false positives caused by
things such as "-   1.2", then good luck! <beg>

SELECT *
FROM   MyTable
WHERE (
-- Positive numbers:
       MyColumn NOT LIKE '-%'
AND NOT(
   ( -- Case 1: without decimal point
       MyColumn NOT LIKE '%.%'
   AND LEN(MyColumn) BETWEEN 1 AND 8
   AND MyColumn NOT LIKE '%[^0-9]%')
OR ( -- Case 2: starting with decimal point
       MyColumn LIKE '.%'
   AND LEN(MyColumn) BETWEEN 2 AND 11
   AND MyColumn NOT LIKE '.%[^0-9]%')
OR ( -- Case 3: ending in decimal point
       MyColumn LIKE '%.'
   AND LEN(MyColumn) BETWEEN 2 AND 9
   AND MyColumn NOT LIKE '%[^0-9]%.')
OR ( -- Case 4: with decimal point embedded
       MyColumn LIKE '%_._%'
   AND LEN(SUBSTRING(MyColumn, 1, CHARINDEX('.', MyColumn) - 1)) BETWEEN
0 AND 8
   AND LEN(MyColumn) = LEN(REPLACE(MyColumn, '.', '')) + 1
   AND LEN(MyColumn) - LEN(SUBSTRING(MyColumn, 1, CHARINDEX('.',
MyColumn))) BETWEEN 1 AND 10
   AND REPLACE(MyColumn, '.', '') NOT LIKE '%[^0-9]%')))
-- Negative numbers
OR  (  MyColumn LIKE '-%'
AND NOT(
   ( -- Case 1: without decimal point
       MyColumn NOT LIKE '-%.%'
   AND LEN(MyColumn) BETWEEN 2 AND 9
   AND MyColumn NOT LIKE '-%[^0-9]%')
OR ( -- Case 2: starting with decimal point
       MyColumn LIKE '-.%'
   AND LEN(MyColumn) BETWEEN 3 AND 12
   AND MyColumn NOT LIKE '-.%[^0-9]%')
OR ( -- Case 3: ending in decimal point
       MyColumn LIKE '-%.'
   AND LEN(MyColumn) BETWEEN 3 AND 10
   AND MyColumn NOT LIKE '-%[^0-9]%.')
OR ( -- Case 4: with decimal point embedded
       MyColumn LIKE '-%_._%'
   AND LEN(SUBSTRING(MyColumn, 1, CHARINDEX('.', MyColumn) - 1)) BETWEEN
1 AND 9
   AND LEN(MyColumn) = LEN(REPLACE(MyColumn, '.', '')) + 1
   AND LEN(MyColumn) - LEN(SUBSTRING(MyColumn, 1, CHARINDEX('.',
MyColumn))) BETWEEN 1 AND 10
   AND REPLACE(MyColumn, '.', '') NOT LIKE '-%[^0-9]%')))

Best, Hugo

Hi Hugo!

Your query worked. I found one row that contained a character that
prevented the conversion! Thanks a lot!!

Henning :-)

*** Sent via Developersdex http://www.developersdex.com ***

Mirrored Records?
Primary Key and Table Design Question
Update Table
the in clause against an integer field.
UPDATE table FROM same table
Problem with WHERE clause
sql question
sql qusetion 3
sql thingy
testking question