quote:

---

Originally posted by lionofdezert

SELECT * FROM Student WHERE SUBSTRING(email,CHARINDEX('@',email)+1,LEN(email)) IN (SELECT email_extension FROM Email_extension)

---

DECLARE @student TABLE (
	student_ID VARCHAR(14) PRIMARY KEY
	, firstname VARCHAR(255)
	, surname VARCHAR(255)
	, email VARCHAR(255)
	)

DECLARE @email_extension TABLE (
	email_extension VARCHAR(255) PRIMARY KEY
	)

INSERT @student (student_Id, firstname, surname, email)
      SELECT 'SG0001', 'fred', 'flintstone', 'fred@bedrock.com'
UNION SELECT 'SG0002', 'wilma', 'flintstone', 'wilma@bedrock.com'
UNION SELECT 'JJ0345', 'George', 'Jetson', 'jetsonG@SpacelySpaceSprockets.co.jup.u'
UNION SELECT 'SBD023', 'Scooby', 'Doo', 'scooby@mysteryMachine.org'

INSERT @email_extension (email_extension)
      SELECT 'bedrock.com'
UNION SELECT 'MysteryMachine.org'

SELECT
	s.*
FROM
	@student s
	JOIN @email_extension ee ON s.email LIKE '%@' + ee.email_extension