Validating a numeric within SUM on a VarChar field

Question

Post reply

Validating a numeric within SUM on a VarChar field

SteveHall

SSChasing Mays

Points: 633
More actions
May 12, 2006 at 9:46 am

#152969

For reasons I'd rather not bore people with I'm attempting to SUM a column that is defined as Varchar within the database. In theory this column should always contain a valid number (actually a money amount) but it is possible that it may not.
What I'm attempting to do is, within a SELECT statement, validate the contents of this column before using the SUM to either add the value, or 0:
  SUM(CASE ISNUMERIC(EDI_H_Cost)
      WHEN 1
   CAST(EDI_H_Cost AS Money)
      ELSE
   0
      END)
but it isn't happy about this.
How do I get around this, bearing in mind that I can't change the database in any way?

Regards,
Steve

Viewing 8 posts - 1 through 7 (of 7 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

you want this...

sum(CASE ISNUMERIC(EDI_H_Cost)

WHEN 0

THEN 0

ELSE EDI_H_Cost END)

To help us help you read this[/url]For better help with performance problems please read this[/url]

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 2

CREATE TABLE #t (V varchar(5))

INSERT INTO #t VALUES ('10')

INSERT INTO #t VALUES ('20')

INSERT INTO #t VALUES ('30')

INSERT INTO #t VALUES ('A10')

INSERT INTO #t VALUES ('0D830')

SELECT V, ISNUMERIC(V) IsItNumeric FROM #t

-- SELECT SUM(CASE ISNUMERIC(V) WHEN 1 THEN CAST(V AS Money) ELSE 0 END) FROM #t

DROP TABLE #t

Remove the comment line and rerun the script and you would get the error.

Probably this link would help

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=243646

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 3

Steven,

See the post that "SQL Junky" directed you to... the bottom line is ISNUMERIC is not an ISALLDIGITS function... ISNUMERIC allows all sorts of "garbage" in...

Your CASE statement should be something like (no pun intended)...

sum(CASE WHEN EDI_H_Cost NOT LIKE '%[^0-9]%'

THEN EDI_H_Cost

ELSE 0 END)

The "^" acts as another NOT...

--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)

SteveHall SSChasing Mays Points: 633 More actions · Answer 4

Thanks for the prompt assistance everyone.

The SQL from Luke L suits my purpose, although the behaviour detailed in the thread pointed to by SQL Junkie is worth noting. For the purposes of my procedure this isn't a problem.

Many thanks to everyone.

Steve.

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Hi all,

FYI - The 'IsReallyNumeric' function in this link does what the (useless) 'IsNumeric' function should!

http://aspfaq.com/show.asp?id=2390

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

David McFarland SSChampion Points: 11815 More actions · Answer 6

If memory serves, there are some things that even get by the quite good IsReallyNumeric function. I believe it was numbers with multiple decimal places in them, such as 12.56.12.

I usually end up using the IsReallyNumeric function as a template, then adding code as needed based on what data passes the function, but still fails on the cast or convert in the particular set of data I'm working with.

RyanRandall SSChampion Points: 13623 More actions · Answer 7

That's good to know, David. Thanks for the info.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.