Oracle SQL - Generate Unique sAMAccountName
I would like to use Oracle SQL to generate unique sAMAccountName for users. The logic to generate the unique name is the First letter of first name + entire Last Name.
If the combination already exists, append 1 at the end of the unique name.
If the second combination already exists, increment till uniqueness is achieved.
For example, 5 users named "Tom Kelly" and 3 users named "Tom Ke" will have the following unique names:
- TKelly
- TKelly1
- TKelly2
- TKelly3
- TKelly4
- TKe
- TKe1
- TKe2
I have written a procedure for this but it's not working completely. I think the logic needs to be updated in this query of the procedure:
SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter
FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM
P_USERS WHERE Custom_Unique_Name= item.tempid2 AND
user_id <> item.user_id ORDER BY Custom_Unique_Name
DESC) WHERE ROWNUM<=1);
The read-only table P_USERS
contains all the users with a primary key as "user_id"
and the unique name is stored in the attribute "Custom_Unique_Name"
After calculating the unique name, the attribute "PRIMARY_UNIQUE_NAME"
in the master user table "T_MASTER_USERS"
needs to be updated. This table's primary key is also "user_id"
which holds the same value as in the read-only table "P_USERS"
DECLARE
lastletter varchar2(10);
anyexists varchar2(10);
lastfive varchar2(10);
CURSOR c_length IS SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2,LENGTH(last_name) as lengthln, user_id, Custom_Unique_Name
FROM P_USERS;
status varchar2(10);
BEGIN
FOR item in c_length
LOOP
EXIT WHEN c_length%notfound;
lastletter:=0;
SELECT NVL(Custom_Unique_Name,0) INTO status FROM P_USERS
WHERE USER_ID=item.user_id;
IF status <> '0' THEN /* Checks if the person already has a
unique id */
NULL; /* If yes, then do nothing and exit the program */
ELSE
SELECT COUNT(*) INTO anyexists FROM (SELECT
Custom_Unique_Name FROM P_USERS
WHERE Custom_Unique_Name = item.tempid2 AND user_id <>
item.user_id);
IF anyexists=0 THEN /* Check if unique id exists for
the user. If not, then assign unique_id to the user */
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2 WHERE
user_id=item.user_id;
ELSE
SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter
FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM
P_USERS WHERE Custom_Unique_Name= item.tempid2 AND
user_id <> item.user_id ORDER BY Custom_Unique_Name
DESC) WHERE ROWNUM<=1);
IF LENGTH(TRIM(TRANSLATE(lastletter, '
+-.0123456789',' '))) > 0 THEN /* Checks if the count
of equivalent unique names if a number. If not, append
1*/
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2||'1' WHERE
user_id=item.user_id;
ELSE
lastletter:=lastletter+1;
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2||lastletter WHERE
user_id=item.user_id;
END IF;
END IF;
END IF;
END LOOP;
END;
sql oracle
add a comment |
I would like to use Oracle SQL to generate unique sAMAccountName for users. The logic to generate the unique name is the First letter of first name + entire Last Name.
If the combination already exists, append 1 at the end of the unique name.
If the second combination already exists, increment till uniqueness is achieved.
For example, 5 users named "Tom Kelly" and 3 users named "Tom Ke" will have the following unique names:
- TKelly
- TKelly1
- TKelly2
- TKelly3
- TKelly4
- TKe
- TKe1
- TKe2
I have written a procedure for this but it's not working completely. I think the logic needs to be updated in this query of the procedure:
SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter
FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM
P_USERS WHERE Custom_Unique_Name= item.tempid2 AND
user_id <> item.user_id ORDER BY Custom_Unique_Name
DESC) WHERE ROWNUM<=1);
The read-only table P_USERS
contains all the users with a primary key as "user_id"
and the unique name is stored in the attribute "Custom_Unique_Name"
After calculating the unique name, the attribute "PRIMARY_UNIQUE_NAME"
in the master user table "T_MASTER_USERS"
needs to be updated. This table's primary key is also "user_id"
which holds the same value as in the read-only table "P_USERS"
DECLARE
lastletter varchar2(10);
anyexists varchar2(10);
lastfive varchar2(10);
CURSOR c_length IS SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2,LENGTH(last_name) as lengthln, user_id, Custom_Unique_Name
FROM P_USERS;
status varchar2(10);
BEGIN
FOR item in c_length
LOOP
EXIT WHEN c_length%notfound;
lastletter:=0;
SELECT NVL(Custom_Unique_Name,0) INTO status FROM P_USERS
WHERE USER_ID=item.user_id;
IF status <> '0' THEN /* Checks if the person already has a
unique id */
NULL; /* If yes, then do nothing and exit the program */
ELSE
SELECT COUNT(*) INTO anyexists FROM (SELECT
Custom_Unique_Name FROM P_USERS
WHERE Custom_Unique_Name = item.tempid2 AND user_id <>
item.user_id);
IF anyexists=0 THEN /* Check if unique id exists for
the user. If not, then assign unique_id to the user */
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2 WHERE
user_id=item.user_id;
ELSE
SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter
FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM
P_USERS WHERE Custom_Unique_Name= item.tempid2 AND
user_id <> item.user_id ORDER BY Custom_Unique_Name
DESC) WHERE ROWNUM<=1);
IF LENGTH(TRIM(TRANSLATE(lastletter, '
+-.0123456789',' '))) > 0 THEN /* Checks if the count
of equivalent unique names if a number. If not, append
1*/
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2||'1' WHERE
user_id=item.user_id;
ELSE
lastletter:=lastletter+1;
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2||lastletter WHERE
user_id=item.user_id;
END IF;
END IF;
END IF;
END LOOP;
END;
sql oracle
I built a system like this once before, I found it much easier to handle this in client code for whatever app I was building... generate a string there, and try an insert against a column with a UNIQUE constraint, and keep iterating on failure until it succeeds. More recently, I've wanted system to be sure there is either a manual human review, a choice is offered, or the username is a valid e-mail address from an outside system before creating the name.
– Joel Coehoorn
Nov 13 '18 at 19:57
add a comment |
I would like to use Oracle SQL to generate unique sAMAccountName for users. The logic to generate the unique name is the First letter of first name + entire Last Name.
If the combination already exists, append 1 at the end of the unique name.
If the second combination already exists, increment till uniqueness is achieved.
For example, 5 users named "Tom Kelly" and 3 users named "Tom Ke" will have the following unique names:
- TKelly
- TKelly1
- TKelly2
- TKelly3
- TKelly4
- TKe
- TKe1
- TKe2
I have written a procedure for this but it's not working completely. I think the logic needs to be updated in this query of the procedure:
SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter
FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM
P_USERS WHERE Custom_Unique_Name= item.tempid2 AND
user_id <> item.user_id ORDER BY Custom_Unique_Name
DESC) WHERE ROWNUM<=1);
The read-only table P_USERS
contains all the users with a primary key as "user_id"
and the unique name is stored in the attribute "Custom_Unique_Name"
After calculating the unique name, the attribute "PRIMARY_UNIQUE_NAME"
in the master user table "T_MASTER_USERS"
needs to be updated. This table's primary key is also "user_id"
which holds the same value as in the read-only table "P_USERS"
DECLARE
lastletter varchar2(10);
anyexists varchar2(10);
lastfive varchar2(10);
CURSOR c_length IS SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2,LENGTH(last_name) as lengthln, user_id, Custom_Unique_Name
FROM P_USERS;
status varchar2(10);
BEGIN
FOR item in c_length
LOOP
EXIT WHEN c_length%notfound;
lastletter:=0;
SELECT NVL(Custom_Unique_Name,0) INTO status FROM P_USERS
WHERE USER_ID=item.user_id;
IF status <> '0' THEN /* Checks if the person already has a
unique id */
NULL; /* If yes, then do nothing and exit the program */
ELSE
SELECT COUNT(*) INTO anyexists FROM (SELECT
Custom_Unique_Name FROM P_USERS
WHERE Custom_Unique_Name = item.tempid2 AND user_id <>
item.user_id);
IF anyexists=0 THEN /* Check if unique id exists for
the user. If not, then assign unique_id to the user */
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2 WHERE
user_id=item.user_id;
ELSE
SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter
FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM
P_USERS WHERE Custom_Unique_Name= item.tempid2 AND
user_id <> item.user_id ORDER BY Custom_Unique_Name
DESC) WHERE ROWNUM<=1);
IF LENGTH(TRIM(TRANSLATE(lastletter, '
+-.0123456789',' '))) > 0 THEN /* Checks if the count
of equivalent unique names if a number. If not, append
1*/
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2||'1' WHERE
user_id=item.user_id;
ELSE
lastletter:=lastletter+1;
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2||lastletter WHERE
user_id=item.user_id;
END IF;
END IF;
END IF;
END LOOP;
END;
sql oracle
I would like to use Oracle SQL to generate unique sAMAccountName for users. The logic to generate the unique name is the First letter of first name + entire Last Name.
If the combination already exists, append 1 at the end of the unique name.
If the second combination already exists, increment till uniqueness is achieved.
For example, 5 users named "Tom Kelly" and 3 users named "Tom Ke" will have the following unique names:
- TKelly
- TKelly1
- TKelly2
- TKelly3
- TKelly4
- TKe
- TKe1
- TKe2
I have written a procedure for this but it's not working completely. I think the logic needs to be updated in this query of the procedure:
SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter
FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM
P_USERS WHERE Custom_Unique_Name= item.tempid2 AND
user_id <> item.user_id ORDER BY Custom_Unique_Name
DESC) WHERE ROWNUM<=1);
The read-only table P_USERS
contains all the users with a primary key as "user_id"
and the unique name is stored in the attribute "Custom_Unique_Name"
After calculating the unique name, the attribute "PRIMARY_UNIQUE_NAME"
in the master user table "T_MASTER_USERS"
needs to be updated. This table's primary key is also "user_id"
which holds the same value as in the read-only table "P_USERS"
DECLARE
lastletter varchar2(10);
anyexists varchar2(10);
lastfive varchar2(10);
CURSOR c_length IS SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2,LENGTH(last_name) as lengthln, user_id, Custom_Unique_Name
FROM P_USERS;
status varchar2(10);
BEGIN
FOR item in c_length
LOOP
EXIT WHEN c_length%notfound;
lastletter:=0;
SELECT NVL(Custom_Unique_Name,0) INTO status FROM P_USERS
WHERE USER_ID=item.user_id;
IF status <> '0' THEN /* Checks if the person already has a
unique id */
NULL; /* If yes, then do nothing and exit the program */
ELSE
SELECT COUNT(*) INTO anyexists FROM (SELECT
Custom_Unique_Name FROM P_USERS
WHERE Custom_Unique_Name = item.tempid2 AND user_id <>
item.user_id);
IF anyexists=0 THEN /* Check if unique id exists for
the user. If not, then assign unique_id to the user */
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2 WHERE
user_id=item.user_id;
ELSE
SELECT SUBSTR(Custom_Unique_Name,-1,1) INTO lastletter
FROM (SELECT * FROM (SELECT Custom_Unique_Name FROM
P_USERS WHERE Custom_Unique_Name= item.tempid2 AND
user_id <> item.user_id ORDER BY Custom_Unique_Name
DESC) WHERE ROWNUM<=1);
IF LENGTH(TRIM(TRANSLATE(lastletter, '
+-.0123456789',' '))) > 0 THEN /* Checks if the count
of equivalent unique names if a number. If not, append
1*/
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2||'1' WHERE
user_id=item.user_id;
ELSE
lastletter:=lastletter+1;
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=item.tempid2||lastletter WHERE
user_id=item.user_id;
END IF;
END IF;
END IF;
END LOOP;
END;
sql oracle
sql oracle
asked Nov 13 '18 at 19:04
Gaurav KabraGaurav Kabra
5419
5419
I built a system like this once before, I found it much easier to handle this in client code for whatever app I was building... generate a string there, and try an insert against a column with a UNIQUE constraint, and keep iterating on failure until it succeeds. More recently, I've wanted system to be sure there is either a manual human review, a choice is offered, or the username is a valid e-mail address from an outside system before creating the name.
– Joel Coehoorn
Nov 13 '18 at 19:57
add a comment |
I built a system like this once before, I found it much easier to handle this in client code for whatever app I was building... generate a string there, and try an insert against a column with a UNIQUE constraint, and keep iterating on failure until it succeeds. More recently, I've wanted system to be sure there is either a manual human review, a choice is offered, or the username is a valid e-mail address from an outside system before creating the name.
– Joel Coehoorn
Nov 13 '18 at 19:57
I built a system like this once before, I found it much easier to handle this in client code for whatever app I was building... generate a string there, and try an insert against a column with a UNIQUE constraint, and keep iterating on failure until it succeeds. More recently, I've wanted system to be sure there is either a manual human review, a choice is offered, or the username is a valid e-mail address from an outside system before creating the name.
– Joel Coehoorn
Nov 13 '18 at 19:57
I built a system like this once before, I found it much easier to handle this in client code for whatever app I was building... generate a string there, and try an insert against a column with a UNIQUE constraint, and keep iterating on failure until it succeeds. More recently, I've wanted system to be sure there is either a manual human review, a choice is offered, or the username is a valid e-mail address from an outside system before creating the name.
– Joel Coehoorn
Nov 13 '18 at 19:57
add a comment |
3 Answers
3
active
oldest
votes
How about something like this?
SQL> create table users
2 (username varchar2(20) primary key);
Table created.
SQL> create or replace procedure p_un (par_first_name in varchar2,
2 par_last_name in varchar2)
3 is
4 l_username users.username%type;
5 l_letter users.username%type;
6 l_digit users.username%type;
7 retval users.username%type;
8 begin
9 l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
10
11 select max(regexp_substr(u.username, '^w+')) l_letter,
12 max(regexp_substr(u.username, 'd+$')) l_digit
13 into l_letter,
14 l_digit
15 from users u
16 where u.username like l_username ||'%'
17 and ( substr(u.username, length(l_username) + 1, 1) between '1' and '9'
18 or ( regexp_substr(u.username, 'd$') is null
19 and substr(u.username, length(l_username) + 1, 1) is null
20 )
21 )
22 and ( to_number(regexp_substr(username, 'd$')) =
23 (select max(to_number(regexp_substr(u1.username, 'd$')))
24 from users u1
25 where u1.username like l_username ||'%'
26 and ( substr(u1.username, length(l_username) + 1, 1) between '1' and '9'
27 or ( regexp_substr(u1.username, 'd$') is null
28 and substr(u1.username, length(l_username) + 1, 1) is null
29 )
30 )
31 )
32 or regexp_substr(u.username, 'd$') is null
33 );
34
35 if l_letter is null then
36 retval := upper(l_username);
37 else
38 retval := upper(l_username || to_char(to_number(nvl(l_digit, 0)) + 1));
39 end if;
40
41 dbms_output.put_line('l_username = ' || l_username||', letter = '|| l_letter||
42 ', digit = '||l_digit ||', new username = ' || retval);
43 insert into users (username) values (retval);
44 end;
45 /
Procedure created.
What does it do?
SELECT
(at line 11) searches for existing usernames that
- look like a new username (which is composed of the first letter of the first name and the whole last name) (line 16)
- contains a number at the end (line 17)
- or there's no number at all (lines 18, 19)
- but, if there is a number there, select MAX number (line 23)
- if such a username doesn't exist, return its default value (line 36)
- if it exists, add
1
to the MAX number and attach it to the default username value (line 38)
Testing:
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = , digit = , new username = TKELLY
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY, digit = , new username = TKELLY1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY1, digit = 1, new username = TKELLY2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = , digit = , new username = TKE
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE, digit = , new username = TKE1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY2, digit = 2, new username = TKELLY3
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE1, digit = 1, new username = TKE2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE2, digit = 2, new username = TKE3
PL/SQL procedure successfully completed.
Result:
SQL> select * From users;
USERNAME
--------------------
TKE
TKE1
TKE2
TKE3
TKELLY
TKELLY1
TKELLY2
TKELLY3
8 rows selected.
add a comment |
Try below Anonymous block for your scenario:
create table P_USERS
(first_name varchar2(20),
last_name varchar2(20),
user_id number);
insert into P_USERS values ('Tom','Kelly',1);
insert into P_USERS values ('Tom','Kelly',2);
insert into P_USERS values ('Tom','Kelly',3);
insert into P_USERS values ('Tom','Kelly',4);
insert into P_USERS values ('Tom','Kelly',5);
insert into P_USERS values ('Tom','Ke',6);
insert into P_USERS values ('Tom','Ke',7);
insert into P_USERS values ('Tom','Ke',8);
commit;
create table T_MASTER_USERS
( user_id number,
PRIMARY_UNIQUE_NAME varchar2(20));
insert into T_MASTER_USERS values (1,NULL);
insert into T_MASTER_USERS values (2,NULL);
insert into T_MASTER_USERS values (3,NULL);
insert into T_MASTER_USERS values (4,NULL);
insert into T_MASTER_USERS values (5,NULL);
insert into T_MASTER_USERS values (6,NULL);
insert into T_MASTER_USERS values (7,NULL);
insert into T_MASTER_USERS values (8,NULL);
commit;
DECLARE
cursor uniq_grp is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2 ,count(*) as CNT
FROM P_USERS
group by SUBSTR(first_name,1,1)||(last_name);
cursor concat_names is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2, user_id
FROM P_USERS;
V_CNT number;
BEGIN
FOR C1 in uniq_grp
LOOP
FOR C2 IN concat_names
LOOP
IF C2.tempid2=C1.tempid2 THEN
V_CNT := c1.CNT -1;
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=c2.tempid2||V_CNT WHERE
user_id=C2.user_id;
c1.cnt := V_CNT;
END IF;
END LOOP;
COMMIT;
END LOOP;
END;
add a comment |
create or replace procedure generate_unique(par_first_name in varchar2,par_last_name in varchar2)
(par_first_name in varchar2,par_last_name in varchar2)
is
l_username users.username%type;
l_letter users.username%type;
begin
l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
dbms_output.put_line('l_username = ' || l_username );
SELECT CASE WHEN MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', '')) IS NULL
THEN l_username
ELSE l_username
||
(MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', ''))+1)
END into l_letter FROM USERS WHERE REGEXP_LIKE(UPPER(USERNAME),l_username||'[0-9]*$');
dbms_output.put_line('unique userid is ' || l_letter);
end;
Please include an explanation of your code, and fix your code block formatting.
– mypetlion
Nov 20 '18 at 17:44
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53287887%2foracle-sql-generate-unique-samaccountname%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
How about something like this?
SQL> create table users
2 (username varchar2(20) primary key);
Table created.
SQL> create or replace procedure p_un (par_first_name in varchar2,
2 par_last_name in varchar2)
3 is
4 l_username users.username%type;
5 l_letter users.username%type;
6 l_digit users.username%type;
7 retval users.username%type;
8 begin
9 l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
10
11 select max(regexp_substr(u.username, '^w+')) l_letter,
12 max(regexp_substr(u.username, 'd+$')) l_digit
13 into l_letter,
14 l_digit
15 from users u
16 where u.username like l_username ||'%'
17 and ( substr(u.username, length(l_username) + 1, 1) between '1' and '9'
18 or ( regexp_substr(u.username, 'd$') is null
19 and substr(u.username, length(l_username) + 1, 1) is null
20 )
21 )
22 and ( to_number(regexp_substr(username, 'd$')) =
23 (select max(to_number(regexp_substr(u1.username, 'd$')))
24 from users u1
25 where u1.username like l_username ||'%'
26 and ( substr(u1.username, length(l_username) + 1, 1) between '1' and '9'
27 or ( regexp_substr(u1.username, 'd$') is null
28 and substr(u1.username, length(l_username) + 1, 1) is null
29 )
30 )
31 )
32 or regexp_substr(u.username, 'd$') is null
33 );
34
35 if l_letter is null then
36 retval := upper(l_username);
37 else
38 retval := upper(l_username || to_char(to_number(nvl(l_digit, 0)) + 1));
39 end if;
40
41 dbms_output.put_line('l_username = ' || l_username||', letter = '|| l_letter||
42 ', digit = '||l_digit ||', new username = ' || retval);
43 insert into users (username) values (retval);
44 end;
45 /
Procedure created.
What does it do?
SELECT
(at line 11) searches for existing usernames that
- look like a new username (which is composed of the first letter of the first name and the whole last name) (line 16)
- contains a number at the end (line 17)
- or there's no number at all (lines 18, 19)
- but, if there is a number there, select MAX number (line 23)
- if such a username doesn't exist, return its default value (line 36)
- if it exists, add
1
to the MAX number and attach it to the default username value (line 38)
Testing:
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = , digit = , new username = TKELLY
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY, digit = , new username = TKELLY1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY1, digit = 1, new username = TKELLY2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = , digit = , new username = TKE
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE, digit = , new username = TKE1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY2, digit = 2, new username = TKELLY3
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE1, digit = 1, new username = TKE2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE2, digit = 2, new username = TKE3
PL/SQL procedure successfully completed.
Result:
SQL> select * From users;
USERNAME
--------------------
TKE
TKE1
TKE2
TKE3
TKELLY
TKELLY1
TKELLY2
TKELLY3
8 rows selected.
add a comment |
How about something like this?
SQL> create table users
2 (username varchar2(20) primary key);
Table created.
SQL> create or replace procedure p_un (par_first_name in varchar2,
2 par_last_name in varchar2)
3 is
4 l_username users.username%type;
5 l_letter users.username%type;
6 l_digit users.username%type;
7 retval users.username%type;
8 begin
9 l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
10
11 select max(regexp_substr(u.username, '^w+')) l_letter,
12 max(regexp_substr(u.username, 'd+$')) l_digit
13 into l_letter,
14 l_digit
15 from users u
16 where u.username like l_username ||'%'
17 and ( substr(u.username, length(l_username) + 1, 1) between '1' and '9'
18 or ( regexp_substr(u.username, 'd$') is null
19 and substr(u.username, length(l_username) + 1, 1) is null
20 )
21 )
22 and ( to_number(regexp_substr(username, 'd$')) =
23 (select max(to_number(regexp_substr(u1.username, 'd$')))
24 from users u1
25 where u1.username like l_username ||'%'
26 and ( substr(u1.username, length(l_username) + 1, 1) between '1' and '9'
27 or ( regexp_substr(u1.username, 'd$') is null
28 and substr(u1.username, length(l_username) + 1, 1) is null
29 )
30 )
31 )
32 or regexp_substr(u.username, 'd$') is null
33 );
34
35 if l_letter is null then
36 retval := upper(l_username);
37 else
38 retval := upper(l_username || to_char(to_number(nvl(l_digit, 0)) + 1));
39 end if;
40
41 dbms_output.put_line('l_username = ' || l_username||', letter = '|| l_letter||
42 ', digit = '||l_digit ||', new username = ' || retval);
43 insert into users (username) values (retval);
44 end;
45 /
Procedure created.
What does it do?
SELECT
(at line 11) searches for existing usernames that
- look like a new username (which is composed of the first letter of the first name and the whole last name) (line 16)
- contains a number at the end (line 17)
- or there's no number at all (lines 18, 19)
- but, if there is a number there, select MAX number (line 23)
- if such a username doesn't exist, return its default value (line 36)
- if it exists, add
1
to the MAX number and attach it to the default username value (line 38)
Testing:
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = , digit = , new username = TKELLY
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY, digit = , new username = TKELLY1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY1, digit = 1, new username = TKELLY2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = , digit = , new username = TKE
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE, digit = , new username = TKE1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY2, digit = 2, new username = TKELLY3
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE1, digit = 1, new username = TKE2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE2, digit = 2, new username = TKE3
PL/SQL procedure successfully completed.
Result:
SQL> select * From users;
USERNAME
--------------------
TKE
TKE1
TKE2
TKE3
TKELLY
TKELLY1
TKELLY2
TKELLY3
8 rows selected.
add a comment |
How about something like this?
SQL> create table users
2 (username varchar2(20) primary key);
Table created.
SQL> create or replace procedure p_un (par_first_name in varchar2,
2 par_last_name in varchar2)
3 is
4 l_username users.username%type;
5 l_letter users.username%type;
6 l_digit users.username%type;
7 retval users.username%type;
8 begin
9 l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
10
11 select max(regexp_substr(u.username, '^w+')) l_letter,
12 max(regexp_substr(u.username, 'd+$')) l_digit
13 into l_letter,
14 l_digit
15 from users u
16 where u.username like l_username ||'%'
17 and ( substr(u.username, length(l_username) + 1, 1) between '1' and '9'
18 or ( regexp_substr(u.username, 'd$') is null
19 and substr(u.username, length(l_username) + 1, 1) is null
20 )
21 )
22 and ( to_number(regexp_substr(username, 'd$')) =
23 (select max(to_number(regexp_substr(u1.username, 'd$')))
24 from users u1
25 where u1.username like l_username ||'%'
26 and ( substr(u1.username, length(l_username) + 1, 1) between '1' and '9'
27 or ( regexp_substr(u1.username, 'd$') is null
28 and substr(u1.username, length(l_username) + 1, 1) is null
29 )
30 )
31 )
32 or regexp_substr(u.username, 'd$') is null
33 );
34
35 if l_letter is null then
36 retval := upper(l_username);
37 else
38 retval := upper(l_username || to_char(to_number(nvl(l_digit, 0)) + 1));
39 end if;
40
41 dbms_output.put_line('l_username = ' || l_username||', letter = '|| l_letter||
42 ', digit = '||l_digit ||', new username = ' || retval);
43 insert into users (username) values (retval);
44 end;
45 /
Procedure created.
What does it do?
SELECT
(at line 11) searches for existing usernames that
- look like a new username (which is composed of the first letter of the first name and the whole last name) (line 16)
- contains a number at the end (line 17)
- or there's no number at all (lines 18, 19)
- but, if there is a number there, select MAX number (line 23)
- if such a username doesn't exist, return its default value (line 36)
- if it exists, add
1
to the MAX number and attach it to the default username value (line 38)
Testing:
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = , digit = , new username = TKELLY
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY, digit = , new username = TKELLY1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY1, digit = 1, new username = TKELLY2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = , digit = , new username = TKE
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE, digit = , new username = TKE1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY2, digit = 2, new username = TKELLY3
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE1, digit = 1, new username = TKE2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE2, digit = 2, new username = TKE3
PL/SQL procedure successfully completed.
Result:
SQL> select * From users;
USERNAME
--------------------
TKE
TKE1
TKE2
TKE3
TKELLY
TKELLY1
TKELLY2
TKELLY3
8 rows selected.
How about something like this?
SQL> create table users
2 (username varchar2(20) primary key);
Table created.
SQL> create or replace procedure p_un (par_first_name in varchar2,
2 par_last_name in varchar2)
3 is
4 l_username users.username%type;
5 l_letter users.username%type;
6 l_digit users.username%type;
7 retval users.username%type;
8 begin
9 l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
10
11 select max(regexp_substr(u.username, '^w+')) l_letter,
12 max(regexp_substr(u.username, 'd+$')) l_digit
13 into l_letter,
14 l_digit
15 from users u
16 where u.username like l_username ||'%'
17 and ( substr(u.username, length(l_username) + 1, 1) between '1' and '9'
18 or ( regexp_substr(u.username, 'd$') is null
19 and substr(u.username, length(l_username) + 1, 1) is null
20 )
21 )
22 and ( to_number(regexp_substr(username, 'd$')) =
23 (select max(to_number(regexp_substr(u1.username, 'd$')))
24 from users u1
25 where u1.username like l_username ||'%'
26 and ( substr(u1.username, length(l_username) + 1, 1) between '1' and '9'
27 or ( regexp_substr(u1.username, 'd$') is null
28 and substr(u1.username, length(l_username) + 1, 1) is null
29 )
30 )
31 )
32 or regexp_substr(u.username, 'd$') is null
33 );
34
35 if l_letter is null then
36 retval := upper(l_username);
37 else
38 retval := upper(l_username || to_char(to_number(nvl(l_digit, 0)) + 1));
39 end if;
40
41 dbms_output.put_line('l_username = ' || l_username||', letter = '|| l_letter||
42 ', digit = '||l_digit ||', new username = ' || retval);
43 insert into users (username) values (retval);
44 end;
45 /
Procedure created.
What does it do?
SELECT
(at line 11) searches for existing usernames that
- look like a new username (which is composed of the first letter of the first name and the whole last name) (line 16)
- contains a number at the end (line 17)
- or there's no number at all (lines 18, 19)
- but, if there is a number there, select MAX number (line 23)
- if such a username doesn't exist, return its default value (line 36)
- if it exists, add
1
to the MAX number and attach it to the default username value (line 38)
Testing:
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = , digit = , new username = TKELLY
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY, digit = , new username = TKELLY1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY1, digit = 1, new username = TKELLY2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = , digit = , new username = TKE
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE, digit = , new username = TKE1
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Kelly');
l_username = TKELLY, letter = TKELLY2, digit = 2, new username = TKELLY3
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE1, digit = 1, new username = TKE2
PL/SQL procedure successfully completed.
SQL> exec p_un('Tom', 'Ke');
l_username = TKE, letter = TKE2, digit = 2, new username = TKE3
PL/SQL procedure successfully completed.
Result:
SQL> select * From users;
USERNAME
--------------------
TKE
TKE1
TKE2
TKE3
TKELLY
TKELLY1
TKELLY2
TKELLY3
8 rows selected.
edited Nov 13 '18 at 20:14
answered Nov 13 '18 at 20:08
LittlefootLittlefoot
20.8k71433
20.8k71433
add a comment |
add a comment |
Try below Anonymous block for your scenario:
create table P_USERS
(first_name varchar2(20),
last_name varchar2(20),
user_id number);
insert into P_USERS values ('Tom','Kelly',1);
insert into P_USERS values ('Tom','Kelly',2);
insert into P_USERS values ('Tom','Kelly',3);
insert into P_USERS values ('Tom','Kelly',4);
insert into P_USERS values ('Tom','Kelly',5);
insert into P_USERS values ('Tom','Ke',6);
insert into P_USERS values ('Tom','Ke',7);
insert into P_USERS values ('Tom','Ke',8);
commit;
create table T_MASTER_USERS
( user_id number,
PRIMARY_UNIQUE_NAME varchar2(20));
insert into T_MASTER_USERS values (1,NULL);
insert into T_MASTER_USERS values (2,NULL);
insert into T_MASTER_USERS values (3,NULL);
insert into T_MASTER_USERS values (4,NULL);
insert into T_MASTER_USERS values (5,NULL);
insert into T_MASTER_USERS values (6,NULL);
insert into T_MASTER_USERS values (7,NULL);
insert into T_MASTER_USERS values (8,NULL);
commit;
DECLARE
cursor uniq_grp is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2 ,count(*) as CNT
FROM P_USERS
group by SUBSTR(first_name,1,1)||(last_name);
cursor concat_names is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2, user_id
FROM P_USERS;
V_CNT number;
BEGIN
FOR C1 in uniq_grp
LOOP
FOR C2 IN concat_names
LOOP
IF C2.tempid2=C1.tempid2 THEN
V_CNT := c1.CNT -1;
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=c2.tempid2||V_CNT WHERE
user_id=C2.user_id;
c1.cnt := V_CNT;
END IF;
END LOOP;
COMMIT;
END LOOP;
END;
add a comment |
Try below Anonymous block for your scenario:
create table P_USERS
(first_name varchar2(20),
last_name varchar2(20),
user_id number);
insert into P_USERS values ('Tom','Kelly',1);
insert into P_USERS values ('Tom','Kelly',2);
insert into P_USERS values ('Tom','Kelly',3);
insert into P_USERS values ('Tom','Kelly',4);
insert into P_USERS values ('Tom','Kelly',5);
insert into P_USERS values ('Tom','Ke',6);
insert into P_USERS values ('Tom','Ke',7);
insert into P_USERS values ('Tom','Ke',8);
commit;
create table T_MASTER_USERS
( user_id number,
PRIMARY_UNIQUE_NAME varchar2(20));
insert into T_MASTER_USERS values (1,NULL);
insert into T_MASTER_USERS values (2,NULL);
insert into T_MASTER_USERS values (3,NULL);
insert into T_MASTER_USERS values (4,NULL);
insert into T_MASTER_USERS values (5,NULL);
insert into T_MASTER_USERS values (6,NULL);
insert into T_MASTER_USERS values (7,NULL);
insert into T_MASTER_USERS values (8,NULL);
commit;
DECLARE
cursor uniq_grp is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2 ,count(*) as CNT
FROM P_USERS
group by SUBSTR(first_name,1,1)||(last_name);
cursor concat_names is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2, user_id
FROM P_USERS;
V_CNT number;
BEGIN
FOR C1 in uniq_grp
LOOP
FOR C2 IN concat_names
LOOP
IF C2.tempid2=C1.tempid2 THEN
V_CNT := c1.CNT -1;
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=c2.tempid2||V_CNT WHERE
user_id=C2.user_id;
c1.cnt := V_CNT;
END IF;
END LOOP;
COMMIT;
END LOOP;
END;
add a comment |
Try below Anonymous block for your scenario:
create table P_USERS
(first_name varchar2(20),
last_name varchar2(20),
user_id number);
insert into P_USERS values ('Tom','Kelly',1);
insert into P_USERS values ('Tom','Kelly',2);
insert into P_USERS values ('Tom','Kelly',3);
insert into P_USERS values ('Tom','Kelly',4);
insert into P_USERS values ('Tom','Kelly',5);
insert into P_USERS values ('Tom','Ke',6);
insert into P_USERS values ('Tom','Ke',7);
insert into P_USERS values ('Tom','Ke',8);
commit;
create table T_MASTER_USERS
( user_id number,
PRIMARY_UNIQUE_NAME varchar2(20));
insert into T_MASTER_USERS values (1,NULL);
insert into T_MASTER_USERS values (2,NULL);
insert into T_MASTER_USERS values (3,NULL);
insert into T_MASTER_USERS values (4,NULL);
insert into T_MASTER_USERS values (5,NULL);
insert into T_MASTER_USERS values (6,NULL);
insert into T_MASTER_USERS values (7,NULL);
insert into T_MASTER_USERS values (8,NULL);
commit;
DECLARE
cursor uniq_grp is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2 ,count(*) as CNT
FROM P_USERS
group by SUBSTR(first_name,1,1)||(last_name);
cursor concat_names is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2, user_id
FROM P_USERS;
V_CNT number;
BEGIN
FOR C1 in uniq_grp
LOOP
FOR C2 IN concat_names
LOOP
IF C2.tempid2=C1.tempid2 THEN
V_CNT := c1.CNT -1;
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=c2.tempid2||V_CNT WHERE
user_id=C2.user_id;
c1.cnt := V_CNT;
END IF;
END LOOP;
COMMIT;
END LOOP;
END;
Try below Anonymous block for your scenario:
create table P_USERS
(first_name varchar2(20),
last_name varchar2(20),
user_id number);
insert into P_USERS values ('Tom','Kelly',1);
insert into P_USERS values ('Tom','Kelly',2);
insert into P_USERS values ('Tom','Kelly',3);
insert into P_USERS values ('Tom','Kelly',4);
insert into P_USERS values ('Tom','Kelly',5);
insert into P_USERS values ('Tom','Ke',6);
insert into P_USERS values ('Tom','Ke',7);
insert into P_USERS values ('Tom','Ke',8);
commit;
create table T_MASTER_USERS
( user_id number,
PRIMARY_UNIQUE_NAME varchar2(20));
insert into T_MASTER_USERS values (1,NULL);
insert into T_MASTER_USERS values (2,NULL);
insert into T_MASTER_USERS values (3,NULL);
insert into T_MASTER_USERS values (4,NULL);
insert into T_MASTER_USERS values (5,NULL);
insert into T_MASTER_USERS values (6,NULL);
insert into T_MASTER_USERS values (7,NULL);
insert into T_MASTER_USERS values (8,NULL);
commit;
DECLARE
cursor uniq_grp is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2 ,count(*) as CNT
FROM P_USERS
group by SUBSTR(first_name,1,1)||(last_name);
cursor concat_names is SELECT SUBSTR(first_name,1,1)||(last_name) as
tempid2, user_id
FROM P_USERS;
V_CNT number;
BEGIN
FOR C1 in uniq_grp
LOOP
FOR C2 IN concat_names
LOOP
IF C2.tempid2=C1.tempid2 THEN
V_CNT := c1.CNT -1;
UPDATE T_MASTER_USERS SET
PRIMARY_UNIQUE_NAME=c2.tempid2||V_CNT WHERE
user_id=C2.user_id;
c1.cnt := V_CNT;
END IF;
END LOOP;
COMMIT;
END LOOP;
END;
answered Nov 13 '18 at 21:08
kanagarajkanagaraj
31717
31717
add a comment |
add a comment |
create or replace procedure generate_unique(par_first_name in varchar2,par_last_name in varchar2)
(par_first_name in varchar2,par_last_name in varchar2)
is
l_username users.username%type;
l_letter users.username%type;
begin
l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
dbms_output.put_line('l_username = ' || l_username );
SELECT CASE WHEN MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', '')) IS NULL
THEN l_username
ELSE l_username
||
(MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', ''))+1)
END into l_letter FROM USERS WHERE REGEXP_LIKE(UPPER(USERNAME),l_username||'[0-9]*$');
dbms_output.put_line('unique userid is ' || l_letter);
end;
Please include an explanation of your code, and fix your code block formatting.
– mypetlion
Nov 20 '18 at 17:44
add a comment |
create or replace procedure generate_unique(par_first_name in varchar2,par_last_name in varchar2)
(par_first_name in varchar2,par_last_name in varchar2)
is
l_username users.username%type;
l_letter users.username%type;
begin
l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
dbms_output.put_line('l_username = ' || l_username );
SELECT CASE WHEN MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', '')) IS NULL
THEN l_username
ELSE l_username
||
(MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', ''))+1)
END into l_letter FROM USERS WHERE REGEXP_LIKE(UPPER(USERNAME),l_username||'[0-9]*$');
dbms_output.put_line('unique userid is ' || l_letter);
end;
Please include an explanation of your code, and fix your code block formatting.
– mypetlion
Nov 20 '18 at 17:44
add a comment |
create or replace procedure generate_unique(par_first_name in varchar2,par_last_name in varchar2)
(par_first_name in varchar2,par_last_name in varchar2)
is
l_username users.username%type;
l_letter users.username%type;
begin
l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
dbms_output.put_line('l_username = ' || l_username );
SELECT CASE WHEN MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', '')) IS NULL
THEN l_username
ELSE l_username
||
(MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', ''))+1)
END into l_letter FROM USERS WHERE REGEXP_LIKE(UPPER(USERNAME),l_username||'[0-9]*$');
dbms_output.put_line('unique userid is ' || l_letter);
end;
create or replace procedure generate_unique(par_first_name in varchar2,par_last_name in varchar2)
(par_first_name in varchar2,par_last_name in varchar2)
is
l_username users.username%type;
l_letter users.username%type;
begin
l_username := upper(substr(par_first_name, 1, 1) || par_last_name);
dbms_output.put_line('l_username = ' || l_username );
SELECT CASE WHEN MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', '')) IS NULL
THEN l_username
ELSE l_username
||
(MAX(REGEXP_REPLACE(UPPER(USERNAME),'[^0-9]', ''))+1)
END into l_letter FROM USERS WHERE REGEXP_LIKE(UPPER(USERNAME),l_username||'[0-9]*$');
dbms_output.put_line('unique userid is ' || l_letter);
end;
answered Nov 20 '18 at 17:20
user10681585user10681585
1
1
Please include an explanation of your code, and fix your code block formatting.
– mypetlion
Nov 20 '18 at 17:44
add a comment |
Please include an explanation of your code, and fix your code block formatting.
– mypetlion
Nov 20 '18 at 17:44
Please include an explanation of your code, and fix your code block formatting.
– mypetlion
Nov 20 '18 at 17:44
Please include an explanation of your code, and fix your code block formatting.
– mypetlion
Nov 20 '18 at 17:44
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53287887%2foracle-sql-generate-unique-samaccountname%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I built a system like this once before, I found it much easier to handle this in client code for whatever app I was building... generate a string there, and try an insert against a column with a UNIQUE constraint, and keep iterating on failure until it succeeds. More recently, I've wanted system to be sure there is either a manual human review, a choice is offered, or the username is a valid e-mail address from an outside system before creating the name.
– Joel Coehoorn
Nov 13 '18 at 19:57