Removing Hyphens and Apostrophes in UserName

I wrote a sql script that will creat username using First Name intital, Last
name, and 4 digit birth date year. The problem I ran into is some last
names have apostrophes (O'Donnell) and some last names have hypens
(Jones-Smith). Testing these logons cause the application to choke on the
above name issues. I use a view to pull all the data together, run an
update script to populate weblogins, and encrypt passwords. I tried to
pull out the ( ' ) and (- ) but sql sees these as functions not text.
Any suggestions on how to clean up weblogins?
Below is copy of Create View and Update Query to give idea of what i'm
doing.
thanks in advance,
Rob
VIEW:
/* Create View for Mbrs that will get Weblogon based on criteria */
CREATE VIEW dbo.vw_Mbrs_need_WebLogin
AS
SELECT dbo.Name.ID, dbo.Name.MEMBER_TYPE, dbo.Name.STATUS,
dbo.Name.MAJOR_KEY, dbo.Name.FULL_NAME, dbo.Name.FIRST_NAME,
dbo.Name.MIDDLE_NAME, dbo.Name.LAST_NAME,
dbo.Name.MEMBER_RECORD, dbo.Name.COMPANY_RECORD,
dbo.Name_Security.WEB_LOGIN,
dbo.Name_Security.PASSWORD,
dbo.Name_Security_Groups.SECURITY_GROUP, dbo.Name.BIRTH_DATE,
dbo.Name_Security.EXPIRATION_DATE
FROM dbo.Name INNER JOIN
dbo.Name_Security ON dbo.Name.ID =
dbo.Name_Security.ID INNER JOIN
dbo.Name_Security_Groups ON dbo.Name.ID =
dbo.Name_Security_Groups.ID
WHERE (dbo.Name.MEMBER_RECORD = 1) AND (dbo.Name.COMPANY_RECORD = 0) AND
(dbo.Name.STATUS = 'A')
UPDATE Script:
/* Create WebLogins-First Intital, LastName, 4 Digit Birth Year Script */
Update dbo.vw_Mbrs_need_WebLogin
SET WEB_LOGIN=(UPPER((Left(FIRST_NAME,1))+ LAST_NAME +
(CAST(YEAR(BIRTH_DATE)AS Char(4))))),EXPIRATION_DATE = '12/31/2004'
WHERE (MEMBER_RECORD = 1) AND (COMPANY_RECORD = 0)
AND (STATUS = 'A') AND BIRTH_DATE <> ''Below is a function you can use to clean unwanted characters from a string.
This seems like an awful lot of trouble just to generate a user name. Why
not take the user's email address as a user name? The email address has
advantages for this purposes: the user is more likely to remember it; it's
certain to be unique; and it's easy to identify who it belongs to. Also,
some people might object to the use of personal info such as birthdate in a
user name on the grounds that the user name may be more likely to be exposed
to a third party. Keep the birth date in the database so it's only used
under the privacy policy that the user has agreed to.
Finally, don't encrypt the password in the database. Don't store the
password at all. Store a secure hash of the password instead.
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^'
+ @.validchars +']%',@.str), 1) ,'')
RETURN @.str
END
GO
SELECT dbo.CleanChars('O''Donnell','A-Z')
David Portas
SQL Server MVP
--|||Thanks for the tips, I suggested email but client was against using it. I
never thought of the privacy issues (and I'm sure my client never thought of
it) I will use the secure hash for the password... that makes more sense.
Thanks again for the help.
rob
"David Portas"
> Below is a function you can use to clean unwanted characters from a
string.
> This seems like an awful lot of trouble just to generate a user name. Why
> not take the user's email address as a user name? The email address has
> advantages for this purposes: the user is more likely to remember it; it's
> certain to be unique; and it's easy to identify who it belongs to. Also,
> some people might object to the use of personal info such as birthdate in
a
> user name on the grounds that the user name may be more likely to be
exposed
> to a third party. Keep the birth date in the database so it's only used
> under the privacy policy that the user has agreed to.
> Finally, don't encrypt the password in the database. Don't store the
> password at all. Store a secure hash of the password instead.
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^'
> + @.validchars +']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> SELECT dbo.CleanChars('O''Donnell','A-Z')
> --
> David Portas
> SQL Server MVP
> --
>