Searching strings by only their nummerical values

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)

Searching strings by only their nummerical values

Author

Topic

thomzon
Starting Member

2 Posts

Posted - 2005-04-11 : 09:00:33

I have a id-field where the IDs are build up as follows: ab14020, ab14021...,doa20032 etc.
What I need to be able to do is to search for f.ex ab-type that are greater than f.ex 14000.
This means somehow splitting up the string into the string part and search for all that start with f.ex ab - and the numeric part and search for all that have a numeric value higher than 14000.
I am a bit bumped out about how to solve this actually as it seems pretty tricky. However I cannot change the ID-type to integer only as it is a running system.
All help would be appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-11 : 09:19:47

If the last 5 digits are always numeric and with fixed length of Prefix, then

Select column from table where right(columm,5)>14000

Madhivanan

Failing to plan is Planning to fail

thomzon
Starting Member

2 Posts

Posted - 2005-04-11 : 10:00:13

Both the alphanumeric prefix and the numeric "postfix" are variable in length. So it would probably take that the sql-query could somehow tell the difference between alphanumeric and numeric charecters.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 10:05:22

This should work for variable length prefixes and suffixes:


set nocount on
declare @tb table (ID varchar(10))
insert @tb 
Select 'a12345' union all
select 'ab12345' union all
select 'abc12' union all
select 'abcde1' union all
select '123' union all
select null union all
select 'abcd' union all
select 'abc231a' union all
select ''

select	convert(int, case 
		when patix > 0 then right([ID], patix)
		when patix = -1 and length > 0 then [ID]
		else null
		end)
From	(
	select	[ID]
		,patindex('%[^0-9]%', reverse([ID]))-1 patix
		,length = len([ID])
	from	@tb
	) a

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-11 : 10:16:40

woops, to make it a search rather than a select:


select	[ID]
From	(
	select	[ID]
		,patindex('%[^0-9]%', reverse([ID]))-1 patix
		,length = len([ID])
	from	@tb
	) a
Where	convert(int, case 
		when patix > 0 then right([ID], patix)
		when patix = -1 and length > 0 then [ID]
		else null
		end) > 10000

This search will always result in a table scan makeing searches quite slow. You may want to store this value either by way of a calculated column or maintained by a trigger or seperate process. Then index the column

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources