SQL comparison with values written in different ways

Question

I am comparing two tables "A" and "B" in which I want to compare values of the columns with the same meaning, but they are written in different ways.
Table "A" has the column "Mes_ano" and has values "jan,fev,mar,abr..." while table "B" has column "Meses_promoção" and has random values from 01 to 12. How can I make these correlations via SQL by taking "Funcio_mes" from table "A" when "Feb" from table A corresponds to "02" from table "B"?


The result of SQL should only bring the months cited in table "B". Someone could help me?

Answer 1

I wanted to understand why to store 'jan' in a column in the database, puts... but, come on:

You can make a Case When directly in the query, or create a function for this, as I imagine you will have to use again in other querys, the function is the most suitable:

CREATE FUNCTION [dbo].[mesFromName]
(
    @mes Varchar(20)
)
RETURNS INT
AS
BEGIN
    RETURN
        CASE 
            WHEN @mes like 'jan%' THEN 1
            WHEN @mes like 'fev%' THEN 2
            WHEN @mes like 'mar%' THEN 3
            WHEN @mes like 'abr%' THEN 4
            WHEN @mes like 'mai%' THEN 5
            WHEN @mes like 'jun%' THEN 6
            WHEN @mes like 'jul%' THEN 7
            WHEN @mes like 'ago%' THEN 8
            WHEN @mes like 'set%' THEN 9
            WHEN @mes like 'out%' THEN 10
            WHEN @mes like 'nov%' THEN 11
            WHEN @mes like 'dez%' THEN 12
            ELSE 0
        END 

END

After that, just do the Join by the tables, using the function:

Select
a.mes, 
a.funcionario,
case 
    when p.mes is null then 
        'Não' 
    else 
   'Sim' 
End  as teve_promocao
from tabelaa a 
left outer join promocao p on dbo.mesFromName(a.mes) = p.mes;

I hope I’ve helped. And don’t forget to mark as an answer.

I put in SQL Fiddle: http://sqlfiddle.com/#! 6/ded9d/5