access sql SELECT question

Question

here is my code:

select column1, column2 from table1;

here is a preview of column1:

1.1 Specimen Mislabeled
1.9 QNS- Specimen Spilled in transit
1.3 QNS-Quantity Not Sufficient
1.6 Test Requisition Missing
1.11 Other - Validity only
1.11 Other-reject per practice
1.5 Patient Info. entered Incorrectly
1.11 Other-Validity
1.11 Other-validity only
1.11 Other-Reject per agency
1.11 Other - not our req
1.11 Other - Not ML requisition
1.11 Other - Defective POC cups?

i would like it to return only 1.11 Other when it sees anything like "*1.11 Other*"

another words i would like the result of the sql statement to be:

1.1 Specimen Mislabeled
1.9 QNS- Specimen Spilled in transit
1.3 QNS-Quantity Not Sufficient
1.6 Test Requisition Missing
1.11 Other
1.11 Other
1.5 Patient Info. entered Incorrectly
1.11 Other
1.11 Other
1.11 Other
1.11 Other
1.11 Other
1.11 Other

how do i do this?

Answer 1

select column1 from table1 where column1 like '1.11 Other*'

It might be % instead of * I get those mixed up between sql and access. But I think it is *. Though if this doesn't work try %. Basically you are using a wildcard for the end so match the beginning and anything starting at the wildcard.

Answer 2

Use a CASE Statement; i.e.

SELECT 
  CASE WHEN Column1 LIKE '%1.11 Other%' 
     THEN '1.11 Other' 
     ELSE Column1 END AS Column1,
  Column2
FROM
   table1