try

<pre>

declare @table table ( col nvarchar(50) )

insert into @table values ('ACT ONLY')
insert into @table values ('NSW ONLY')
insert into @table values ('QLD ONLY')

declare @find  nvarchar(50)
declare @result  nvarchar(50)
set @find ='ABC - QLD ONLY'
set  @result =    ( select top 1 col from @table where @find like '%'+col+'%')
if(@result is not null )
begin
 select @result = REPLACE(@find , @result ,'') 
  select @result = REPLACE(@result , '-' ,'') 
end
select  @result -- ABC

这应该也工作

This should work too

DECLARE @temp TABLE (Col1 VARCHAR(50))
DECLARE @input VARCHAR(50)

SET @input = 'ABC - QLD ONLY'

INSERT INTO @temp
	SELECT 'ACT ONLY' UNION SELECT 'NSW ONLY' UNION SELECT 'QLD ONLY'

SELECT REPLACE(REPLACE(@input, col1, ''),' - ','') FROM @temp
WHERE CHARINDEX(col1,@input)  > 0