Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kingroon
Starting Member
29 Posts |
 Posted - 2006-04-05 : 22:11:22
|
| Yo..Righto, I'm having an issue with a record. I'm, pulling some numeric data from a table, but one of the [very old] records [possibly more down the track] does not contain numeric data [so a CSng function type mismatches in my ASP], instead it contains a decimal point.I don't wanna go changing data. Let's say it's simply not an option.What I would like to do, is check the value of that particular column in my SELECT statment. If it was a Null, I could simply use IsNull(Column1, 0.00), but it ain't, it's a '.' IsNumeric returns a boolean right? So IsNumeric(Column1) would return False. Great. But I am no further to forcing a value of 0.00 if Column1 contains non-numeric data.Any help would be great..Rock on..KingRoonChaotician Man,Slice the lines of virgin pathways.Harmony Hero.DogFightClothing. No dogs. No fighting.http://www.dogfightclothing.com |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2006-04-05 : 22:19:44
|
| CASE WHEN IsNumeric(Column1) = false THEN 0 ELSE Column1 END?Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-05 : 22:26:48
|
| [code]select case when isnumeric( MyBadDataColumn ) = 1 then MyBadDataColumn else 0.00 endfrom MyBadDataTable[/code]CODO ERGO SUM |
|
|
|
kingroon
Starting Member
29 Posts |
Posted - 2006-04-05 : 22:30:45
|
| Thanks for the replies!MVJ, the pseudo table name provided a much welcomed comic relief thanks ;o)Chaotician Man,Slice the lines of virgin pathways.Harmony Hero.DogFightClothing. No dogs. No fighting.http://www.dogfightclothing.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-06 : 01:47:36
|
| Beware that IsNumeric() will let through some values that won't convert to numeric. So you routine may still fall over if you happen to have rogue values that IsNumeric() accepts as valid.Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
IsNull.. IsNumeric.. IsNotRight..