It seems that you really need a CASE expression. I'm not sure how would you need to use it, so I'm giving you 3 options.

--Creating some sample data

CREATE TABLE #Staging(

someColumn int,

vchDept varchar(100),

vchEmail varchar(100)

);

INSERT INTO #Staging VALUES

(1, 'IT', 'it@abc.com'),

(2, 'IT', 'it@123.com'),

(3, 'IT', 'it@whocares.com'),

(4, 'HR', 'it@abc.com'),

(5, 'HR', 'it@123.com'),

(6, 'HR', 'it@whocares.com');

--Validate Data

SELECT * FROM #Staging;

GO

--Use a query

SELECT *,

CASE WHEN vchDept='IT' THEN 1

WHEN vchEmail like '%@abc.com' THEN 2

WHEN vchEmail like '%@123.com' THEN 3

ELSE 5 END AS iDeptID

FROM #Staging;

--Update a column

ALTER TABLE #Staging ADD iDeptID int; --Not needed if the column already exists

GO

UPDATE s SET

iDeptID = CASE WHEN vchDept='IT' THEN 1

WHEN vchEmail like '%@abc.com' THEN 2

WHEN vchEmail like '%@123.com' THEN 3

ELSE 5 END

FROM #Staging s;

SELECT * FROM #Staging;

ALTER TABLE #Staging DROP COLUMN iDeptID; --To clean changes

--Use a computed column

ALTER TABLE #Staging

ADD iDeptID AS (CASE WHEN vchDept='IT' THEN 1

WHEN vchEmail like '%@abc.com' THEN 2

WHEN vchEmail like '%@123.com' THEN 3

ELSE 5 END);

SELECT * FROM #Staging;

GO

DROP TABLE #Staging; --Clean my sandbox