Post reply

converting text to int

  • September 17, 2008 at 4:02 pm

    #198290

    I have a table that contains text data that is imported nightly from another system. One of the columns is text that is a dollar amount that needs to be summed. The text data is in the format $x,xxx.xx now.

    What is the best way to convert this during a query so that I can sum the amounts?

    TIA

  • September 17, 2008 at 4:14 pm

    #873298

    Have you tried casting the data type as MONEY and then SUM?

    [font="Courier New"]SELECT SUM(CAST(column1 AS MONEY)) FROM Table1[/font]

  • September 17, 2008 at 4:18 pm

    #873299

    I did try that first. It gives the error:

    Server: Msg 235, Level 16, State 1, Line 1

    Cannot convert a char value to money. The char value has incorrect syntax.

    ISNUMERIC does say it sees the integer so I just need to get the rest.

    Jeff

  • September 17, 2008 at 4:53 pm

    #873305

    How about taking an approach of removing characters that aren't digits or a decimal, then converting and summing?

    [font="Courier New"]SELECT SUM(CAST(dbo.ufn_digits(column1) AS MONEY)) FROM Table1[/font]

    CREATE FUNCTION dbo.ufn_digits(@x AS VARCHAR(MAX))

    RETURNS VARCHAR(MAX)

    AS

    BEGIN

    WHILE PATINDEX('%[^0-9.]%', @val) > 0

    SET @x= REPLACE(@x, SUBSTRING(@x, PATINDEX('%[^0-9.]%',@x),1),'')

    RETURN @x

    END

  • September 17, 2008 at 4:57 pm

    #873306

    Thanks Todd. It's starting to look like that may be how I have to do this. I was hoping for another solution as I am running this query and then outputting the results to a web app and that is one more thing that will take time.

    Jeff

  • September 17, 2008 at 6:53 pm

    #873323

    Jeff (9/17/2008)

    I did try that first. It gives the error:

    Server: Msg 235, Level 16, State 1, Line 1

    Cannot convert a char value to money. The char value has incorrect syntax.

    ISNUMERIC does say it sees the integer so I just need to get the rest.

    Jeff

    Heh... it's not the function that's incorrect... here's the proof...

    --===== Create and populate a 1,000,000 row test table.

    -- Jeff Moden

    SELECT TOP 1000000

    RowNum = IDENTITY(INT,1,1),

    SomeMoney = CAST('$1,234.56' AS CHAR(10))

    INTO dbo.JBMTest

    FROM Master.dbo.SysColumns t1,

    Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

    --===== A table is not properly formed unless a Primary Key has been assigned

    -- Takes about 1 second to execute.

    ALTER TABLE dbo.JBMTest

    ADD PRIMARY KEY CLUSTERED (RowNum)

    --===== Now, show that CAST to Money and SUM works as expected.

    SELECT SUM(CAST(SomeMoney AS MONEY)) AS GrandTotal

    FROM dbo.JBMTest

    The problem is somewhere in the data. Somewhere, you have 1 or more values that aren't convertable to the Money datatype... like maybe having a comma in the wrong spot... or maybe having a value like '2d3'. Using ISNUMERIC isn't gonna find the problem because, guess what?... '2d3' IS a numeric value where "d" is a decimal place. '2e3" IS also a numeric value in scientific notation.

    The out of place comma type of error will be the bugger to find if it exists.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 17, 2008 at 7:41 pm

    #873340

    Thanks Jeff. You were correct there was bad data.

    Jeff

  • September 17, 2008 at 7:48 pm

    #873342

    You're welcome. How did you find the bad data?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 17, 2008 at 8:03 pm

    #873346

    Luckily the bad data was not seen as numeric:

    select isnumeric([sale price]) as num from dbo.CURRENT_DATA order by num

    So it wasn't hard to find.

    Jeff

  • September 17, 2008 at 9:51 pm

    #873369

    Thanks!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 1 through 9 (of 9 total)

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