Find an exact match in SQL

March 22, 2022

I’m new to SQL programming and I was creating a table doing several when cases and the fact is that I have files that end in ‘.sas’ and I want to put these as programs and then others ‘.sas7bdat’ that I want that I put them as tables, I am doing all this with a case when, the problem is that when I say case when t1.var1 contains ‘.sas’ I also see the .sas7bdat’ and I just want it to match exactly with .sas to differentiate it of the tables.

Does anyone know how I could do this? I leave you my code:

PROC SQL;
CREATE TABLE test AS
SELECT t2.var1,
       t1.var2,
       t1.var3,
       t1.var4,
       t1.var5,
       t1.vr6,
       t1.var7,
       t2.var8,
       t2.var9,
       CASE
         WHEN t1.var1 contains '.log' THEN
          'LOG'
         WHEN t1.var1 contains '.csv' OR '.xlsx' THEN
          'FICHERO'
         WHEN t1.var1 contains '.sas7bdat' THEN
          'TABLA'
         WHEN t1.var1 contains '.sas' THEN
          'PROGRAMA'
       END AS var10
  FROM origin AS t1
  LEFT JOIN test2 t2
    ON t1.var3 = t2.var2
 WHERE var5 IS NOT NULL
 ORDER BY var5 DESC;
QUIT;

I have also tried with the like but it does not return what I want

>Solution :

Use like '%.extension':

CASE
   WHEN t1.var1 like '%.log' THEN
      'LOG'
   WHEN t1.var1 like '%.csv' OR t1.var1 like '%.xlsx' THEN
      'FICHERO'
   WHEN t1.var1 like '%.sas7bdat' THEN
      'TABLA'
   WHEN t1.var1 like '%.sas' THEN
      'PROGRAMA'
END AS var10

Note also corrected a minor syntax problem: you can’t use the expression t1.var1 contains '.csv' OR '.xlsx'. You must repeat the operation, eg t1.var1 contains '.csv' OR t1.var1 contains '.xlsx'