October 11, 2006 at 7:47 am
Experts,
Here is my requirement.I have been trying various things but i am not successfull till this point
Here is an example
LastName Fname Uname
Smith Jo Smith
Smith Jo SmithJ
Smith Jo Smithjo
smith Jo smitj
smith Jo smitjo
smith Jo smij
smith Jo smijo
Basically Username should not be greater than 7 and and can be just the lastname if that username doesn't exist and also should be less than or = 7,if greater than 7 just take first 7 chars
if lastname already exists we have take one char from firstname and then goes on as outlined in the example
Any suggestions would be greatly appreciated.
Thanks,
October 11, 2006 at 12:44 pm
Perhaps I do not understand the question. Are you looking for opinions to how to construct your usernames, or a way to, within SQL to create a unique userid by checking a list of "rules"?
-- Cory
October 11, 2006 at 2:17 pm
Cory,
Sorry about that..
You are correct.I am ttrying to build usernames using T-SQL and using last name,first name with some rules.
Above is just the example.
Thanks,
October 12, 2006 at 1:47 am
In the interest of saving some time, I tried out this idea in VB. Should be easily portable to T-SQL. The idea is that you want to always use as much of the last name as possible before resorting to using parts of the first name. For testing purposes, the UserExists function always returns true and just displays the current user name.
Thus for Jo Smith you get these possibilties:
Smith
SmithJ
SmithJo
Smit
SmitJ
SmitJo
Smi
SmiJ
SmiJo
Sm
SmJ
SmJo
S
SJ
SJo
You better not have too many of these guy come and join your company. The 16th Jo Smith will be without a unique user name.
Here is the code.
Public Function GenUser(strLn As String, strFn As String, intMaxLen As Integer) As String
Dim strNewLn As String
Dim strUser As String
Dim intL As Integer
Dim intF As Integer
strNewLn = strLn
If Len(strNewLn) > intMaxLen Then strNewLn = Left(strLn, intMaxLen)
strUser = strNewLn
Deb strFn & " " & strLn
intL = Len(strNewLn)
Do ' loops from length of strNewLn to 1
If Not UserExists(strUser) Then
GenUser = strUser
Exit Function
End If
intF = 1
Do ' loop from 1 to length of first name
strUser = strNewLn & Mid(strFn, 1, intF)
If Len(strUser) > intMaxLen Then Exit Do
If Not UserExists(strUser) Then
GenUser = strUser
Exit Function
End If
intF = intF + 1
If intF > Len(strFn) Then Exit Do
Loop
intL = intL - 1
If intL = 0 Then Exit Do
strNewLn = Mid(strNewLn, 1, intL)
strUser = strNewLn
Loop
End Function
October 12, 2006 at 7:27 am
Try this
DECLARE @LastName varchar(20)
DECLARE @FirstName varchar(20)
SET @LastName = 'Smith'
SET @FirstName = 'Jo'
CREATE TABLE #temp ([ID] int IDENTITY(1,1), Username varchar(40))
INSERT INTO #temp (UserName)
SELECT l.LastName+f.FirstName
FROM (SELECT n.Number,LEFT(@LastName,n.Number) AS [LastName]
FROM @Numbers n
WHERE n.Number > 0 AND n.Number <= LEN(@LastName)) l
CROSS JOIN
(SELECT n.Number,LEFT(@FirstName,n.Number) AS [FirstName]
FROM @Numbers n
WHERE n.Number <= LEN(@FirstName)) f
WHERE LEN(l.LastName+f.FirstName) < 8
ORDER BY l.Number DESC, f.Number ASC
SELECT * FROM #temp
SELECT Username
FROM #temp
WHERE [ID] = (SELECT MIN(t.[ID])
FROM #temp t
LEFT OUTER JOIN [Users] u
ON u.UserName = t.UserName
WHERE u.UserName IS NULL)
DROP TABLE #temp
You will have to create @Numbers table variable or temp table or permanent Numbers table whichever you wish
Far away is close at hand in the images of elsewhere.
Anon.
October 12, 2006 at 7:35 am
Thanks Guys..I will give it a try and let you guys know.
Once again Appreciate For Your Help!
October 12, 2006 at 9:35 am
There comes a time when I simply will not try to solve a problem using pure SQL. It becomes too convoluted and hard to understand and modify. Thus the bells starts ringing when you need to use cross joins and tables with sequential numbers etc.
Using procedural T-SQL is adequate here because the function would likely not get called that often (probably only when a new user is created) so performance is not an issue. You can then have an easier time to modify and tweak the algorithm.
October 12, 2006 at 11:09 am
Why the limit to 7 characters ???
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 12, 2006 at 11:17 am
Rudy,
That's kindaa requirement.
Thanks
October 12, 2006 at 11:18 am
David,
Your script does the trick..
Once again thanks For your expertise.
Thank,s
October 12, 2006 at 11:25 am
I understood that point in the original post. I was more curious as to why the limit in todays world ? Is it a limit imposed by another software package or another computer operating system (IBM Mainframe is an example) or is 'just the way we do things' ...what I am trying to ask is the "business reason".
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply