ottenm
asked on
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:
https://www.experts-exchange.com/questions/23659182/HT-manage-numeric-keys-in-a-varchar-field-MS-SQL-2000.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
The current query (below) comes from angelll's solution here:
https://www.experts-exchange.com/questions/23659182/HT-manage-numeric-keys-in-a-varchar-field-MS-SQL-2000.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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select replace( yourCol, 's', '' ) from yourTable
select
replace( n, 's', '' )
from(
select n = 's134'
) src