Getting rid of integers from the EmailAddress

Question

Post reply

Getting rid of integers from the EmailAddress

Smash125

Hall of Fame

Points: 3562
More actions
April 29, 2022 at 12:34 pm

#4024187

How to remove integers from the Email Id
aaron0@adventure-works.com
Please guide

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Try the REPLACE function, and replace with '' (no spaces)

https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?view=sql-server-ver15

This reply was modified 2 years, 6 months ago by homebrew01.
This reply was modified 2 years, 6 months ago by homebrew01.

ScottPletcher SSC Guru Points: 101097 More actions · Answer 2

If you just want to get rid of all digits in the email address, then:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(email_id, '0', ''), '1', ''), '2', ''), '3', ''), ...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Smash125 wrote:

How to remove integers from the Email Id
aaron0@adventure-works.com
Please guide

WHY???? It seems to me that you're removing a necessary part of a correct email address. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

An alternative way to do it:

REPLACE(TRANSLATE(email, '0123456789', REPLICATE(CHAR(1), 10)), CHAR(1), '')

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Again, I'd like to know why we're perverting email addresses in such a fashion. There has to be a reason.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

Jeff Moden wrote:

Again, I'd like to know why we're perverting email addresses in such a fashion. There has to be a reason.

I don't know, but I was thinking it could be that some people sign up to offers with multiple email addresses. They just create multiple email addresses by adding numbers to the end of an email address. This could be one way to identify these emails.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

How about it @Smash125? Why do you need this top be done?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)