Search Multi options in sql server

I have an requirement like.

declare  @Test table
(ID INT IDentity(1,1),
State varchar(200)
)

insert into @test (State) 开发者_如何学运维--values 
select 'AL~AM~AK'
union
select 'AI~AZ~AK'
union
select 'AZ~AK~AL'

select * from @test

Now @test (in my Database it is a physical table, in which clients data is stored).

Now i want to search those id which is having states ('AL~AM').

It is a multi-multi searching.

I haven't understood your question yet! Did you mean that it will return 'AL~AM~AK' or 2 results ('AL~AM~AK' and 'AZ~AK~AL')?

If it result only AL~AM~AK, then use this statement:

SELECT * FROM Test WHERE State like '%AL~AM%'

Hope is helps :)