Author |
Topic |
Sean_B
Posting Yak Master
111 Posts |
Posted - 2007-08-28 : 11:06:10
|
Hi,I'm casting a varchar field to a decimal field using the formatCASE ISNUMERIC(GrossMktCapGbp)WHEN 1 THEN CONVERT(DECIMAL(18,6),GrossMktCapGbp)ELSE NULLendThinking this would ensure that any spurious rows got set to null.However I had a problem with some values that were set to '.', it seems that isnumeric thinks these are numbers but casting them to decimal produces an error.SELECT ISNUMERIC('.')SELECT CAST('.' AS DECIMAL(18,6))Should I have been doing something different in my check possibly.Sean |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-28 : 11:11:43
|
isNumeric is a bit odd...use like like this simple examplewhere GrossMktCapGbp not like '%[a-z]%'_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-28 : 11:12:16
|
Do you any negative number?If not, just add a ZERO before.SELECT ISNUMERIC('0' + '.')SELECT ISNUMERIC('0' + '12.23')SELECT ISNUMERIC('0' + '.23')SELECT ISNUMERIC('0' + '0.23')SELECT ISNUMERIC('0' + '234') E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-28 : 11:14:28
|
[code]CASE WHEN GrossMktCapGbp = '.' THEN <some default value for dot only here> WHEN ISNUMERIC(GrossMktCapGbp) = 1 THEN CONVERT(DECIMAL(18,6), GrossMktCapGbp) ELSE <some default value for non-numeric value here>END[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
Sean_B
Posting Yak Master
111 Posts |
Posted - 2007-08-28 : 11:20:07
|
Thanks guys.Sean |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-28 : 12:17:30
|
New Style CASE ISNUMERIC(GrossMktCapGbp+'d0')WHEN 1 THEN CONVERT(DECIMAL(18,6),GrossMktCapGbp)ELSE NULLend MadhivananFailing to plan is Planning to fail |
|
|
Kristen
Test
22859 Posts |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 08:51:12
|
"Welcome back Kristen"Been doing some gardening ... changing the shape of the pond and widening the drive ... |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-29 : 09:00:14
|
quote: Originally posted by Kristen "Welcome back Kristen"Been doing some gardening ... changing the shape of the pond and widening the drive ...
I hope you TESTed it well MadhivananFailing to plan is Planning to fail |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 09:04:37
|
Not all that well .. went through the Water, Electric and Phones . However I now know where they all are |
|
|
|