Solved

Extracting Name from Email

Posted on 2014-10-31
3
106 Views
Last Modified: 2014-11-24
Hi

SQL 2008

I have email addresses in a column as follows - they all follow the same format

matt.smith@example.com
susan.smith@hayes.com

I just need to extract the name from it as follows

Matt Smith
Susan Smith

Any help would be appreciated
0
Comment
Question by:halifaxman
3 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40415337
Here is the code:

select Upper(substring(substring('matt.smith@example.com', 1,charindex('.','matt.smith@example.com')-1),1,1))+ 
             substring(substring('matt.smith@example.com', 1,charindex('.','matt.smith@example.com')-1),2,200) as name,
             
upper(substring(substring('matt.smith@example.com', charindex('.','matt.smith@example.com')+1,(charindex('@','matt.smith@example.com')-charindex('.','matt.smith@example.com'))-1),1,1))+
      substring(substring('matt.smith@example.com', charindex('.','matt.smith@example.com')+1,(charindex('@','matt.smith@example.com')-charindex('.','matt.smith@example.com'))-1) ,2,200) as lastname

Open in new window


you can test it here http://sqlfiddle.com/#!3/d41d8/40668

You just need to replace 'matt.smith@example.com' with you column_name
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40415341
If they're always in the form firstname.surname@blah.com then it is quite straightforward, but it looks dirty

declare @e nvarchar(100)
select @e = 'john.smith@fghjk.com'

declare @fn nvarchar(100), @sn nvarchar(100)

select @fn = LEFT(@e, charindex('.', @e) - 1),
       @sn = replace(SUBSTRING(@e, charindex('.', @e), charindex('@', @e) - LEN(@fn) - 1), '.', '')

select @fn, @sn

Open in new window


replace @e with you column name.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40415883
Rather tricky since you have to consider a '.' with no '@', an '@' with no '.', and neither an '@' nor a '.'.

SELECT
    email_address,
    CASE WHEN dot_location = 0 THEN STUFF(email_address, 1, 1, UPPER(LEFT(email_address, 1))) ELSE
        STUFF(LEFT(email_address, dot_location - 1), 1, 1, UPPER(LEFT(email_address, 1))) END AS first_name,
    CASE WHEN dot_location = 0 THEN '' ELSE
        STUFF(SUBSTRING(email_address, dot_location + 1, at_sign_location - dot_location - 1), 1, 1, UPPER(SUBSTRING(email_address, dot_location + 1, 1))) END AS last_name        
FROM (
    SELECT 'matt.smith@example.com' AS email_address UNION ALL
    SELECT 'susan.smith@hayes.com' UNION ALL
    SELECT 'george.washington@mountvernon.com' UNION ALL
    SELECT 'First_name.last_name (but no at sign)' UNION ALL
    SELECT 'somename@butnodot' UNION ALL
    SELECT 'textonlynoatordot'
) AS test_data
CROSS APPLY (
    SELECT
        CASE WHEN CHARINDEX('@', email_address) = 0 THEN LEN(email_address) + 1 ELSE CHARINDEX('@', email_address) END AS at_sign_location,
        CHARINDEX('.', email_address) AS dot_location
) AS assign_alias_names
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question