converting text to int

Question

Post reply

converting text to int

Jeff-132889

Ten Centuries

Points: 1112
More actions
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

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

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

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

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

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

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 2

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

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 3

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

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

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)

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 6

Thanks Jeff. You were correct there was bad data.

Jeff

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

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)

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

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)