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

SQL Server Development (2000)

isnumeric

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-09-23 : 10:29:15

hi,
How do I get the records in a field that start with a six digit number pls?
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 10:30:59

Select columns from yourTable where left(Col,6) like '[0-9]'

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-09-23 : 10:35:00

Hi,
Thanks for the reply.
BUT that returns no records.
Anyway I have worked something out which works.
Thank you

select substring(DepartmentName, 1, 6) from UM_TB_DEPARTMENT
where isnumeric(substring(DepartmentName, 1, 6)) = 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 10:38:36

Well
Use this query
your query will not make use of index

select DepartmentName from UM_TB_DEPARTMENT
where DepartmentName like '[0-9][0-9][0-9][0-9][0-9][0-9]%'

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-23 : 10:38:53

this is better:
Select columns from yourTable where left(Col,6) like '[0-9][0-9][0-9][0-9][0-9][0-9]'

isnumeric returns 1 for 1.4e55...

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-23 : 10:44:18

I think left function is not necessary which will prevent from using index

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-23 : 10:58:27

true.

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

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 11:16:09

If there are very few rows that satisfy the query then

where DepartmentName like '[0-9][0-9][0-9][0-9][0-9][0-9]%'

will be much faster than

where LEFT(DepartmentName, 6) like '[0-9][0-9][0-9][0-9][0-9][0-9]'

whereas if its going to select most of the rows then there won't be much in it

Assuming that there is an index on DepartmentName - if not then they will both take the same time (coz its gonna do a Table Scan, or Clustered PK scan, of some sort

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-09-23 : 11:47:18

Dunno about 'much' faster -- it will only be able to seek on the first character:
SEEK(DepartmentName >= '0' AND DepartmentName < 'A')
for sensible collations; bizarrely:
SEEK(DepartmentName >= '/ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ' AND DepartmentName < ':')
or something for _bin collations!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-23 : 12:07:19

You may want to try it this way if you have an index on the column to allow for an index range scan.


select
	cols
from
	MyTable
where
	-- Specify range to allow query optimizer 
	-- to use an index if it exists.
	MyCol >= '000000'	and
	Mycol <= '999999'	and
	-- Check for non-digit characters.
	-- Case retruns 1 if any character is not a numeric digit.
	case
	when left(MyCol,6) like '%[^0123456789]%'
	then 1
	else 0
	end <> 1

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2005-09-23 : 14:04:23

"Dunno about 'much' faster"

On the tests I've made, if the matched set is small, we are talking of a few logical IO operations to get the data [from a "determinable" subset of the index], compared to hundreds or thousands of logical IO for the "Use the whole index" - to yet another order of magnitude for "Scan the whole table"

MVJ I don't reckon there is any performance difference between


WHERE	    MyCol >= '000000'
	AND Mycol <= '999999'
	AND Mycol LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]%'

and

WHERE Mycol LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]%'

I reckon SQL converts that to effectively do a BETWEEN '000000' AND '999999ÿ' first.

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-09-24 : 03:54:46

quote:
Originally posted by Kristen
On the tests I've made, if the matched set is small, we are talking of a few logical IO operations to get the data [from a "determinable" subset of the index], compared to hundreds or thousands of logical IO for the "Use the whole index"

If all your rows start with a digit, and only a few start with six digits, it won't make an appreciable difference whether or not the column is indexed (beyond the width of the index versus the width of the table). As you said, the SEEK:() predicate of the Index Seek operation will only contain one range.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources