isnumeric - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

isnumeric

Author

Topic

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-28 : 10:06:33

Why does the isnumeric function return true for '4' + char(0) but the value can't be converted to a numeric value?

We had some garbage data coming through in one of our loads ie 1 record had a ascii 0 at the end of a numeric value.

How would one test for this so as to reject the record and not load it as numeric data? as isnumeric clearly does not work in this case.

any clever ideas

[CODE]
create table #test(val varchar(16));

insert into #test
select '1' union
select '2' union
select '3' union
select '4' + char(0)

select val, isnumeric(val) from #test

select cast(val as decimal(18, 2))
from #test

[/CODE]

Duane.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-28 : 10:10:46

This will tell you which ones are bad.... but I didn't try to fix it...


create table #test(val varchar(16))

insert into #test
select '1' union
select '2' union
select '3.2' union
select '4.1' + char(0)

select 
	val, 
	isNumeric1 = isnumeric(val),
	isNumeric2 = case when val like '%[^0-9.]%' then 0 else 1 end
from #test

while exists(Select * From #test Where 0=(case when val like '%[^0-9.]%' then 0 else 1 end))
Begin
	Update #test
	set 
		val = stuff(val,patindex('%[^0-9.]%',val),1,'')
	from #test
	Where 0=(case when val like '%[^0-9.]%' then 0 else 1 end)
End

select cast(val as decimal(18, 2))
from #test

Drop Table #test

EDIT: I did fix it actually...

Corey

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-28 : 10:20:34

I have already identified them and fixed them after it fell over, but we have a rejects table that I would like to apply some kind of Businessrule on and write records like this one away automatically and not have to run manual fixes on.

Oh and the update thing won't work - its a table with about 10 million rows on (no indexes as it is in a staging area - just after being loaded from a txt file).

Duane.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-28 : 10:43:30

Oh well... its the thought that counts right??

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-28 : 11:23:25

will this help:
http://www.nigelrivett.net/RemoveNonNumericCharacters.html

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-28 : 12:52:46

I think I like the STUFF better, it seems simpler.

Corey

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 01:40:14

Thanks spirit1 - usefull script.

The only thing is - I don't want to fix it - this data is supposed to be numeric so if it is non-numeric I need to reject it - If I was to fix it programmaticaly I would never know if I am fixing it correctly.

What i want is a set based solution to identify the non-numeric data as I described in the beginning - bearing in mind that these funny characters may not neccessarily only appear at the end of the field.

Any more ideas?

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 02:37:50

Hey - I've got it.

[CODE]
declare @PatStr1 varchar(255)
declare @PatStr2 varchar(255)

--*** allow "-", "." and "0" to "9"
set @PatStr1 = '[' + char(45) + char(46) + ']'
set @PatStr2 = '[' + char(48) + '-' + char(57) + ']'

select cast(val as decimal(18, 2))
from #test
where patindex(@PatStr1, val) > 0 or patindex(@PatStr2, val) > 0

select val from #test
where val not like @PatStr1
and val not like @PatStr2

[/CODE]

Thanks for the help anyway.

Duane.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 08:46:41

Why do you need 2 strings??

my first example is almost the same...just leave off the fixer part.


create table #test(val varchar(16))

insert into #test
select '1' union
select '-2' union
select '3.2' union
select '4.1' + char(0)

select 
	val,
	cast(val as decimal(18, 2))
from #test
Where val not like '%[^0-9.-]%'

select 
	val
from #test
Where val like '%[^0-9.-]%'

Drop Table #test

Corey

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 08:52:26

You are right.

You must remember that mine was the beta version.

I was only illustrating the concept.

Duane.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 09:02:31

Cool.

You know 10 million records seems to be a lot to me... at least in a staging area. Do you mind if I ask what kind of info?

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-29 : 09:05:45

If the situation is truly just limited to 1 char(0) character at the end of some values, you can always do something like:

select isnumeric(case when right(val,1) = char(0) then left(val, len(val)-1) else val end) from #test

I once worked with a proprietary database* that never returned Nulls trhough ODBC -- it returned Char(0)'s. Of course, this was undocumented. We were so confused for a while -- "Is Null" comparisons failed, ="" failed, nothing worked. What a pain. And we were warehousing data from that system into SQL, so everywhere there might be a char(0), since we wanted NULL in SQL Server, we had to use the old NULLIF(val, char(0)) function around the column.

* This database driver also returned errors if you tried to query a table with a TEXT column through ODBC when that TEXT column that was exactly 201 characters. Not more than 200, only exactly 201 characters. Just wouldn't work! You'd have to open up their system, find the field causing the error, just add a space or an "X" at the end of the field, and re-query and it would work.

- Jeff

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 09:13:57

Thanks for that Jeff.

I'd rather go with catching any kind of non-numerics though - we have had funny data from this system in the past (A unix system in our stores - which is soon to be replaced). I don't enjoy being called out @ 04h00 am.

Corey - The data is the selling prices from all of our stores (which we load daily) - I am contracted to the biggest retailer in Africa and it is in the same kind of environment to what Jeff was mentioning. A massive DataWarehouse. If you have any BI experience you would realise that this is actually not that large.

Duane.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 09:17:42

quote:
... if you have any BI experience ...

What does BI stand for? [ignore]jokes about sexual orientation[/ignore]

Corey

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 09:23:29

Business Intelligence.

Have I spelt it correctly? - BI (The acronym Corey) is just so much easier to spell :)

Duane.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 09:43:23

Well I've never heard that phrase, but maybe its just becuase I have spend all my time in 1 company so far. And they often don't demonstrate any 'BI'.

Does BI refer to data tracking on all types of data? In your case that would be product traffic to stores, speed of sales, sales prices, etc?

I mainly deal with payroll type info and benefits type info. So the retail arena is kinda foreign to me

Corey

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-09-29 : 10:07:42

BI does not just pertain to retail.
BI - is basicaly Data Warehousing - every corporate company should have one.

It's all to do with trends, analysis etc.

Check this out:

http://businessintelligence.ittoolbox.com/

Duane.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-29 : 10:10:34

thanx!

I'll read up

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources