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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aleks

ASKER

I will try this:

UPDATE Users
SET email = LOWER(Email)

Thanks.

Scott Pletcher

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

Aleks

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 !

Jim Horn

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?

Aleks

ASKER

It apparently was a mistake on the comment that I accepted as answer.

Jim Horn

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.

Aleks

ASKER

Yes, I took care of that :)