Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Unanswered: Datatype convert char to numeric

    Hi,

    I read the topic from JROdden and this case is similiar but...

    I got several varchar fields with
    values like
    1.2
    1.3
    ... these I can covert with
    select CONVERT(dec(5,2), fieldname) as fieldname

    In fact I also solved undefined- and NULL-values with.
    CONVERT(decimal(12, 2), CASE WHEN GESCHKOSTMAX IS NULL OR
    GESCHKOSTMAX < '0' THEN '0' ELSE GESCHKOSTMAX END) as GESCHKOSTMAX,

    But now there are values like
    1,4 and these ones neither CONVERT nor CAST will handle.

    I tried the
    SELECT DISTINCT KMPAUSCHALE
    FROM extr_INTFIRMA
    WHERE (isnumeric(KMPAUSCHALE) = 1)

    and get
    0,40
    0.25
    0.30 and so on...

    The error is:
    [Microsoft][ODBC SQL Driver][SQL Server]Error converting datatype varchar to decimal. (or float or numeric (whatever I tried))

    I think the easiest way would be to insist on higher data quality but
    I also would like to solve this interesting challenge.


    Thanks for any hints

    By the way, I followed rudys link to
    http://rudy.ca/afdb.html
    and now I know how I could protect myself !!!!

    There must be a voice in my head saying:
    Try the db-forum, try it and stay happy... ;-)

    best regards and have fun with new year eve.

    Michael

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you worried about < 0? And how do you now they will translate to 5,2?

    You should be more worried about other chars that don't translate...
    Code:
    SELECT CONVERT(float,ISNULL(REPLACE(GESCHKOSTMAX,',',','),0))
    FROM extr_INTFIRMA
    WHERE ISNUMERIC(REPLACE(GESCHKOSTMAX,',',','))=1
    My question to you is...what do you do with the data that doesn't fit this profile?

    You're exclusing an entire population of potentially valid data...

    Yes you should more tightly define the columns datatype...

    It's probably going to require data cleaning though...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: Datatype convert char to numeric

    Originally posted by Michael Kaiser
    By the way, I followed rudys link to
    http://rudy.ca/afdb.html
    and now I know how I could protect myself !!!!

  4. #4
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Re: Datatype convert char to numeric

    Originally posted by r937
    Hi Brett,

    thank you very much for that SQL-command.
    I tried
    SELECT CONVERT(decimal(15, 4), ISNULL(REPLACE(HERUMSATZWELT, ',', '.'), 0)) AS Expr1
    FROM extr_INTFIRMA
    WHERE (ISNUMERIC(REPLACE(HERUMSATZWELT, ',', '.')) = 1)

    (please note the point I replaced) and it worked well.

    Nevertheless there are some field which do not contain NULLS.
    The ASCII(fieldname) gives me NULL as result.
    So I guess this field is "not defined".

    This is the reason why I' m worried about < 0.
    All "undefined" went to "0".
    Not very elegant but it worked...

    best regards

    Michael

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Datatype convert char to numeric

    Originally posted by Michael Kaiser
    Nevertheless there are some field which do not contain NULLS.
    The ASCII(fieldname) gives me NULL as result.
    So I guess this field is "not defined".

    This is the reason why I' m worried about < 0.
    All "undefined" went to "0".
    Not very elegant but it worked...

    best regards

    Michael
    I don't understand...ISNULL() Will only give you a 0 if the value is NULL

    And what is ASCII(fieldname) for?

    And I don't understand "undefined"

    But hey, as long as it work

    Ihr Willkommen
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36
    Hi Brett,

    the data are coming from a web-frontend into an access-db.
    Obviosly the user don't have to fillout all questions and therefore
    some fields remain empty.
    Others change to "NULL".
    I don't know why.
    I imported the data to SQL server and
    checked them with select distinct
    I get two "empty" fields in the result.
    One contains "NULL" the other "nothing" ???
    I tried ASCII(fieldname) to search for "nonvisible" data but
    this ASCII-command results in NULL.

    When trying your command it fails because of this empty fields.

    I checked the SQL online help and found the
    NonEmpty function but that will not work within a sql-command
    (in my view)


    Whatsoever...
    now it is time to go to a new years eve party...

    I will try it again next year!!!

    By the way...

    your welcome = Ihr Willkommen
    is very, very strange - hihi -

    your welcome best fits in german - gern geschehen -

    But I get the idea.

    Thanks for your help and tons of fun the next hours....

    Michael

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    und einen guten rutsch ins neue jahr !!


    rudy

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey, that's what I get for a google translation...

    anyway...happy new year!

    And what you're describing is an empty string, which is not null

    SELECT COUNT(*)
    FROM extr_INTFIRMA
    WHERE GESCHKOSTMAX = ''

    Should show you how many


    Have a liter or 2 for me!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    Rudy - I tried selling my version of that device but no luck. I have been wearing mine for several years now - and I feel so much safer today - no aliens talking in my head anymore ... :-)

    Thanks for posting that - I have not seen that for a couple of years now and it always makes me laugh.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •