Something that I wanted to do the other day was to parse out the different parts of and email address. The user and the domain part. However the data I was using was quite poor. I also wanted to be able to use this information in a computed column.

First of all I tried it this way.

User part

left([EMailAddress],charindex('@',[EMailAddress])-(1))

Domain part

right([EMailAddress],len([EMailAddress])-charindex('@',[EMailAddress]))

Which of course would crash on poor data. So they very quickly turned into functions so they were easier to change and also decided that they should return null if there was no '@' in the data.

CREATE FUNCTION EMailExtractDomain
      (@EMail VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
      DECLARE @idx INT
      DECLARE @domain VARCHAR(MAX)

      SELECT @idx = CHARINDEX('@', @EMail)
      IF @idx = 0
            RETURN NULL

      SELECT @domain = SUBSTRING(@EMail, @idx + 1, LEN(@EMail))
      RETURN @domain
END
GO

CREATE FUNCTION EMailExtractUser
      (@EMail VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
      DECLARE @idx INT
      DECLARE @domain VARCHAR(MAX)

      SELECT @idx = CHARINDEX('@', @EMail)
      IF @idx = 0
            RETURN NULL

      SELECT @domain = SUBSTRING(@EMail, 0, @idx)
      RETURN @domain
END
GO

I also ran them though a set of quick tests.

SELECT dbo.EMailExtractUser('nobody@example.com'), dbo.EMailExtractDomain('nobody@example.com')
SELECT dbo.EMailExtractUser('example.com'), dbo.EMailExtractDomain('example.com')
SELECT dbo.EMailExtractUser(''), dbo.EMailExtractDomain('')
SELECT dbo.EMailExtractUser(NULL), dbo.EMailExtractDomain(NULL)




Last Modified: 21 December 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - All temp tables are global
2011-02-20 - MSSQL - Unsigned int
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-29 - MSSQL - TRIM Function