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?
 
ottenmConnect With a Mentor Author 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.