Aleks
asked on
Update when email is in all caps
I have a field in my database called 'email', stores all emails for clients. When an email is sent out it works UNLESS the email is in all caps. For some reason in this scenario the email never makes it.
Can I run a script for this table "Users" and field "email" to turn all CAPS into non-caps ?
So if email is: NAME@DOMAIN.COM changes to: name@domain.com
OR even if it is : Name@DOMain.com changes to: name@domain.com
THx
Can I run a script for this table "Users" and field "email" to turn all CAPS into non-caps ?
So if email is: NAME@DOMAIN.COM changes to: name@domain.com
OR even if it is : Name@DOMain.com changes to: name@domain.com
THx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Easiest and cleanest is probably to add a trigger to the table to insure that all inserted and updated emails are in lower case:
CREATE TRIGGER Users__Trg_Set_Email_Lower _Case
ON dbo.Users
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
UPDATE u
SET email = LOWER(u.email)
FROM dbo.Users u
INNER JOIN inserted i ON i.email = u.email
GO
CREATE TRIGGER Users__Trg_Set_Email_Lower
ON dbo.Users
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
UPDATE u
SET email = LOWER(u.email)
FROM dbo.Users u
INNER JOIN inserted i ON i.email = u.email
GO
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for amucinobluedot's comment #a39528709
for the following reason:
Works perfectly, thanks !
Accepted answer: 0 points for amucinobluedot's comment #a39528709
for the following reason:
Works perfectly, thanks !
Curiosity overwhelms me ... if it 'works perfectly', then wouldn't you want to accept an answer to this question, and not request it be deleted by accepting one of your own comments?
ASKER
It apparently was a mistake on the comment that I accepted as answer.
Thanks for the grade. Good luck with your project. -Jim
btw Scott brings up a valid point, if this columns should always be small letters only, and this isn't be handled in any front-end, might as well enforce it when the rows are added to the table with a trigger.
btw Scott brings up a valid point, if this columns should always be small letters only, and this isn't be handled in any front-end, might as well enforce it when the rows are added to the table with a trigger.
ASKER
Yes, I took care of that :)
ASKER
UPDATE Users
SET email = LOWER(Email)
Thanks.