Re: checking a list of words against a column

Bjorn Borud wrote:
>
> I am trying to create a function that, based on the real name of a
> person generates a unique user-name that is based on the users real
> name and is no longer than 8 characters.
>
> given that I have created an algorithm for creating a list of
> candidate usernames I wonder how I should go about checking this list
> against the database. the table containing the usernames is very
> large.
>
> for now I have written a routine that generates a list of
> space-separated candidate usernames and returns this list as a
> varchar2 string.
>
> also; any hints on how I should ensure that I don't check the same
> candidate username twice? (my current algorithm in some cases yields
> several identical candidate usernames).

There are many ways to do this. If your username creation routine is written in PL/SQL, or any of the PRO*xxx languages, you could have it test each candidate username before adding it to the list. This would be the best place for it -- at the lowest level possible to catch duplicates. If that is not possible, you must test the list when it is returned from the routine. This is not difficult, just run through the list one at a time, test each one against the table column. But, as you say, it could be time consuming. You could just catch it when the user selects one of the usernames from the list presented to him (I assume this is what you do). But after getting a "Sorry, this username is already take." message, he is liable to wonder why invalid choices are presented to him in the first place.

Of course, make sure the username column has an index. That, at least, will speed up the inevitable search.

-- 
Tomm Carr
---- ----
Hunting for a job is like hunting for an elephant.
If you're not *very* careful, you might find one!