Oracle 11g - find out a new distinct value

Question

I've a table of users and one of the columns is User_ID (naming policy = first letter of first name and rest is the last name. If a name is exists, then increment by 1).

For example: John Doe -> user_id=jdoe

If jdoe already exists then user_id = jdoe1

The input for the query is a basic user_id (e.g. jdoe) I would like to scan the table and for the output of the query to be: a new distinct value of user_id.

If the table has the following values:

jdoe
jdoe1
jdoe2
jdoe3

The output should be jdoe4.

Any help will be appreciated.

Thanks

P.S

Changes to the table are not allowed.

The value from the query is being used with another system (active directory)

If you `count` any deletes would allow dups, select the max of the int from regexp_substr — Alex K., Apr 20 '15 at 12:25

score 0 · Answer 1 · edited May 23 '17 at 11:43

EDIT: This is corrected based on a comment from Alex Poole. Assuming that base names/initials doesn't contain numbers.

WITH tab
     AS (SELECT 'jdoe' name FROM DUAL
         UNION ALL
         SELECT 'jdoe1' FROM DUAL
         UNION ALL
         SELECT 'jdoe2' FROM DUAL
         UNION ALL
         SELECT 'jdoe3' FROM DUAL
         UNION ALL
         SELECT 'jdoes7' FROM DUAL
         UNION ALL
         SELECT 'jjdoe66' FROM DUAL)

SELECT :newName || TO_CHAR (MAX (id) + 1)
  FROM (SELECT NVL (REGEXP_REPLACE (name, '[^0-9]'), 0) id
          FROM tab
         WHERE REGEXP_LIKE (name, '^' || :newName || '[0-9]'));

REGEXP_REPLACE with [^0-9] removes all non numerical characters.

REGEXP_LIKE with [0-9] only shows matches with following numbers. ^ denotes at the beginning of.

:newName is a bind variable.

This will match longer names - so if you had, say, `jdoes6`, this would return `jdoe7` instead of `jdoe4`. You could avoid that with `WHERE regexp_like(name, '^' || :newName || '[^[:alpha:]]*$'))`. But it also won't give you the appended 1 for the first duplicate; if you only have `jdoe` existing, it'll return `jdoe` again instead of `jdoe1`. — Alex Poole, Apr 20 '15 at 17:44

score 0 · Answer 2 · answered Apr 20 '15 at 18:02

This uses an inline view to get the user_id and numeric suffix for any thing matching your base ID value, and then decides whether it can use the unadorned base value (if max(user_id) is null then there was nothing similar already), and if not adds an incremented suffix.

That suffix is based on the highest existing suffix, but if you only have the base name so far (jdoe with no number) then max(suffix) is null, so the nvl() turns that into a zero before adding one.

select decode(max(user_id), null, :base, :base || (nvl(max(suffix), 0) + 1))
  as new_user_id
from (
  select user_id, regexp_replace(user_id, '^[[:alpha:]]*', null) as suffix
  from users
  where regexp_like(user_id, '^' || :base || '[^[:alpha:]]*$')
);

With a starting users table that contains:

jdoe   
jdoe1  
jdoe2  
jdoe3  
jdoes6 
adoe

... changing the bind variable to supply the basic string gives you:

jdoe -> jdoe4
jdoes -> jdoes7
adoe -> adoe1
adoes -> adoes

Oracle 11g - find out a new distinct value

2 Answers2

Linked