Oracle 11g-找出新的独特价值 [英] Oracle 11g - find out a new distinct value

问题描述

我有一张用户表，其中一列是User_ID(命名策略 =名字的第一个字母，其余的是姓氏.如果存在名称，则加1 ).

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).

例如: 约翰·杜(John Doe)-> user_id = jdoe

For example: John Doe -> user_id=jdoe

如果jdoe已经存在，则user_id = jdoe1

If jdoe already exists then user_id = jdoe1

查询的输入是基本的user_id(例如jdoe) 我想扫描表，并查询查询的输出:新的user_id值.

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.

如果表具有以下值:

jdoe
jdoe1
jdoe2
jdoe3

输出应为jdoe4.

任何帮助将不胜感激.

谢谢

PS

不允许更改表格.

查询中的值正在与另一个系统(活动目录)一起使用

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

推荐答案

编辑:此问题已根据

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与[^0-9]一起删除所有非数字字符.

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

REGEXP_LIKE和[0-9]仅显示具有以下数字的匹配项. ^表示开头.

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

:newName是绑定变量.

这篇关于Oracle 11g-找出新的独特价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！