USE [tempdb]

GO

--== TEST DATA ==--

IF OBJECT_ID('dbo.UserDetails') IS NOT NULL DROP TABLE dbo.UserDetails

CREATE TABLE dbo.UserDetails

(

StaffNumber Varchar(30),

Name Varchar(30),

UserEmail Varchar(50) NULL

)

INSERT INTO dbo.UserDetails

(StaffNumber, Name, UserEmail)

VALUES

('A111116', 'Fred Smith', 'user1@email.com'),

('B22371', 'Jan Richards', NULL),

('X06742198', 'Anne Spencer', 'annespencer@email.com'),

('B43521', 'John James', 'jj-invalid')

SELECT * FROM dbo.UserDetails

--== CREATE VIEW ==--

-- It's best to run this code command-by-command from here, to see what's happening:

IF OBJECT_ID('dbo.View1') IS NOT NULL DROP VIEW dbo.View1

GO

-- This view doesn't handle invalid email addresses (NULL is OK):

CREATE VIEW dbo.View1 AS

SELECT *,

[NewColumn]=LEFT(UserEmail, CHARINDEX('@', UserEmail, 1) -1)

FROM dbo.UserDetails

GO

-- This fails:

SELECT * FROM dbo.View1

GO

-- Handle invalid data:

ALTER VIEW dbo.View1 AS

SELECT *,

[NewColumn]=CASE WHEN CHARINDEX('@', UserEmail, 1)=0 THEN UserEmail ELSE LEFT(UserEmail, CHARINDEX('@', UserEmail, 1) -1) END

FROM dbo.UserDetails

GO

-- And it works...

SELECT * FROM dbo.View1

-- ... but you may want to handle missing/invalid data differently.