add new constraint to existing query

In SQL 2000 I need to add an additional constraint to a query I use that finds unused numeric PK's in a varchar field.  

The current query (below) comes from angelll's solution here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23659182.html

The constraint I need to add is: return only those values that don't already exist in et_objects.object_id (another table/column).  Unfortunately, in that table the values have an 's' prepended in front of them.  

For example, if the first unused integer in customers.customer_code is 123 (returned by the current query) but et_objects.object_id contains 's123' and 's124' then I need to return 125 instead.

Any help greatly appreciated.

Here is the current query that returns the smallest unused integer in customers.customer_code:

declare @t table ( v int )
insert into @t (v) select CAST( customer_code AS INT)
  FROM customers
 WHERE customer_code NOT LIKE '%[^0-9]%'
 
select min(t.v) + 1
  from @t t
  left join @t n
    on n.v = t.v + 1
 where n.v is null

ottenmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nicolasdiogoCommented:
on your column you can do

select replace( yourCol, 's', '' ) from yourTable


select
replace( n, 's', '' )

from(

select n = 's134'

) src
0
ottenmAuthor Commented:
Thank you nicolasdiogo.  I can do 'replace', or just concatenate.  What I need help with is getting the query to avoid values that conflict with the other table.
0
ottenmAuthor Commented:
I think I got it!  Id' been trying to add it to the insert, but came up with the following by adding it to the select.  Just need to test it out....

declare @t table ( v int )
insert into @t (v) select CAST( customer_code AS INT)
  FROM customers
 WHERE customer_code NOT LIKE '%[^0-9]%'

select min(t.v) + 1
  from @t t
  left join @t n
    on n.v = t.v + 1
  left join et_objects o
    on o.object_id = 's' + cast(t.v + 1 as varchar)
 where n.v is null and o.object_id is null
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.