SQL Question 19

Problem

  • USE ADVENTUREWORKSLT2008R2
  • Write a mulistatment table valued function that accepts an email address and returns a two column table with the username and domain name
  • Test the function with the emailaddress in the Customers table

Code

USE ADVENTUREWORKSLT2008R2
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmailParts]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[EmailParts]
GO

CREATE FUNCTION EmailParts(@email varchar(1000))
RETURNS @tab TABLE
(User_Name Varchar(500),Domain varchar(500))
AS
BEGIN
DECLARE @count int
SELECT @count=CHARINDEX('@',@email,1);

if (@count > 1)
INSERT INTO @tab VALUES (left(@email,@count-1), substring(@email,@count+1,50))
ELSE
INSERT INTO @tab VALUES ('error','error')
RETURN
END;
GO
--test function
SELECT c.EmailAddress, uf.User_Name, uf.Domain
FROM SalesLT.Customer c CROSS APPLY dbo.EmailParts(c.EmailAddress) uf

Result

Question 19 Result

Question 19 Result

Notes

This function returns a table with email values parsed into the UserName and Domain name. The table returned is @tab. The function has one parameter @email which is given values when the function is tested by CROSS APPLY the Customer and the function dbo.EmailParts(c.EmailAddress). The function INSERT INTO @tab VALUES for the User_Name and Domain using string functions LEFT and SUBSTRING.

No Comments Yet.

Leave a comment