Post reply

replace function

  • January 22, 2009 at 5:14 am

    #130319

    im trying to convert a column to a number but i've found a few values that contain a ',' instead of a '.' so obviously when trying to convert its throwing an error.

    how can i use the replace function to replace all ',' to '.'

    thanks

  • January 22, 2009 at 5:17 am

    #931023

    yes you can

    SELECT REPLACE('YourString',',','.')

  • January 22, 2009 at 5:22 am

    #931027

    thanks

  • January 22, 2009 at 5:25 am

    #931029

    What you're trying to do is very dangerous, because what would you do with the following numbers:

    1: 1,234.45

    2: 1.234,45

    3: 1234,45

    4: 1234.45

    The same value in different formats, based what country settings you use. If you replace all comma's to points, you implicitly decide all the numbers are in the formats 3 or 4. If any of those numbers are in format 1 or 2, a replace function won't solve your problem and you might end up with values multiplied by 1000 or 1000000.

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply