username validation

[rshivagami]

Hi, I need to check if the username contains only the following characters : all alphabets, all numbers, '-', '_', '@' and '/'. If it contains any characters other than the list above, it should be rejected. Here is the function that I have come up but I am pretty sure there are more gracious ways to doing this :

1. First using Replace function, replace all the valid characters with a NULL like : SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('xyz_ba/s-123@yahoo.com', 'a'), 'b'), 'c'), 'd'), 'e'), 'f') FROM dual

2. After replacing, if the length of the final trimmed output is still more than 0, then there is some invalid character, so throw an error.

But this is really tedious as I will have to do a separate replace for all 26 alphabets + 10 numbers + few special characters - any other better way of doing this ?

Thanks.

[slimdave]

Have a look at the Translate() function, which will allow you to get the length of the string with all of the valid characters removed very easily.

[slimdave]

Another thought ... just to make it more supportable you might like to create a varchar2 variable called "list_of_valid_characters" and assign to it the ... wel list of valid characters. Then when you reference that in the TRANSLATE() function it is a little more obvious what you are doing and why.

[rshivagami]

Thanks a lot Slimdave. That wil make things more simpler.

[stecal]

Take a look at utlpwdmg.sql in rdbms/admin. You can convert the arrays as needed and pass in the username instead of (or in addition to) the password.