• For starters it helps if you provide DDL. For better, quicker answers on T-SQL questions, click on the following: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    But I think this should work for you: (There are multiple ways to go about this.)

    DECLARE @Var VARCHAR(100);

    SET @Var='WTP CATION EXCHANGER';

    --SET @Var='DRAUGHT- WORKING MODE';

    --SET @Var='MCWP-TESTING-MODE PART1';

    SELECT SUBSTRING(@Var, 1, CHARINDEX(' ', REPLACE(@var, '-', ' ')) - 1);

    I probably would have used LEFT instead of SUBSTRING, but since you specified what had to be used I went with that.

    BTW this really looks like homework, since you know exactly what functions you needed to use. If it is homework you should really try to do it yourself, or at least post what you have tried and where you are running into problems.