Parse name from Email Address

Question

Post reply

Parse name from Email Address

jeboesch

Old Hand

Points: 323
More actions
October 6, 2010 at 9:13 am

#230079

In a SQL Server Stored Procedure, I am inserting fields from Active Directory into a table and need to use the email address field to populate my User ID field. I just need the characters before the @ sign in the email address.
Example:
I need to parse jwdoe from jwdoe@mydomain.com
This must be simple, but I am just learning!
Thanks.

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

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Look at the CHARINDEX function and combine it with the LEFT, or SUBSTRING, function.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 2

try this

select SUBSTRING(EmailCOL, 1,CHARINDEX('@', EmailCol)-1) AS email from tableA

jeboesch Old Hand Points: 323 More actions · Answer 3

Thanks, this gives me the desired output but I received the following error message:

Invalid length parameter passed to the SUBSTRING function.

The email field contains some NULL values, could that be the problem?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

jeboesch (10/6/2010)
Thanks, this gives me the desired output but I received the following error message:
Invalid length parameter passed to the SUBSTRING function.
The email field contains some NULL values, could that be the problem?

A Null email address will return that error - if this is the case you have to add code to deal with those values either ignoring them or translating a null email address into something like "N/A"

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Philipp Post Default port Points: 1401 More actions · Answer 5

jeboesch (10/6/2010)
The email field contains some NULL values, could that be the problem?

Elimitate NULLs either in the WHERE condition of the query (SELECT .... WHERE email_address IS NOT NULL) or replace the nulls using something like COALESCE(email_address, 'dummy@mydomain.com')

brgds

Philipp Post

jeboesch Old Hand Points: 323 More actions · Answer 6

jeboesch

Old Hand

Points: 323

October 7, 2010 at 6:39 am

#1233470

Thanks everyone, I have it working now!

lukus_g SSC Eights! Points: 958 More actions · Answer 7

This may be a silly question, but if you have access to Active directory, couldn't you get the username directly?

sAMAccountName...

I'm just thinking is someone has a different logon to their email address, eg: jo'connor@sample.com might be "joconnor" (without the apostrophe).