How can I get all names that start with "sci" or "eng" without using the OR clause. In other words, how can I modify the following statement so I don't use the OR clause:
select * from course where (name like 'sci%' or name like 'eng%')
SQL Server does not have support for RegExp. You could create your own .NET Function for this, however this would perform suboptimal compared to the code you already have, as your code can use indexes efficiently. Thus, my advice is to leave the code as it is.
--Finds all strings containing 'sci' or 'eng'
where len(replace(replace(name, 'sci', ''), 'eng', '')) < len(name)
--Finds all strings starting with 'sci' or 'eng'
where len(replace(replace(left(name, 3), 'sci', ''), 'eng', '')) < 3
If it's not practically useful, then it's practically useless.
SET NOCOUNT ON
CREATE TABLE myTable99([name] varchar(100))
INSERT INTO myTable99([name])
SELECT 'Science of mixology' UNION ALL
SELECT 'Engineering of mixology' UNION ALL
SELECT 'Bachelor of Go Go' UNION ALL
SELECT 'Dr. Drunk'
DECLARE @like table([name] varchar(100))
INSERT INTO @like([name]) SELECT 'sci' UNION ALL SELECT 'eng'
SELECT * FROM myTable99 t JOIN @like l ON t.[name] LIKE l.[name]+'%'
SET NOCOUNT OFF
DROP TABLE myTable99