IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].UFD_OBTERBANDEIRACARTAO') AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [DBO].UFD_OBTERBANDEIRACARTAO
GO 

CREATE FUNCTION dbo.UFD_OBTERBANDEIRACARTAO
(
    @Bandeira VARCHAR(200)
)
RETURNS INT
AS
BEGIN
    DECLARE @bandeiraLower VARCHAR(200);
    SET @bandeiraLower = LOWER(@Bandeira);

    RETURN
    (
        SELECT
            CASE
                WHEN @bandeiraLower LIKE '%visa%'         THEN 1
                WHEN @bandeiraLower LIKE '%mastercard%'   THEN 2
                WHEN @bandeiraLower LIKE '%maestro%'      THEN 2
                WHEN @bandeiraLower LIKE '%amex%'         THEN 3
                WHEN @bandeiraLower LIKE '%express%'      THEN 3
                WHEN @bandeiraLower LIKE '%soro%'         THEN 4
                WHEN @bandeiraLower LIKE '%scard%'        THEN 4
                WHEN @bandeiraLower LIKE '%diners%'       THEN 5
                WHEN @bandeiraLower LIKE '%alelo%'        THEN 10
                WHEN @bandeiraLower LIKE '%elo%'          THEN 6
                WHEN @bandeiraLower LIKE '%hipercard%'    THEN 7
                WHEN @bandeiraLower LIKE '%aura%'         THEN 8
                WHEN @bandeiraLower LIKE '%cabal%'        THEN 9
                WHEN @bandeiraLower LIKE '%banes%'        THEN 11
                WHEN @bandeiraLower LIKE '%calcard%'      THEN 12
                WHEN @bandeiraLower LIKE '%credz%'        THEN 13
                WHEN @bandeiraLower LIKE '%discover%'     THEN 14
                WHEN @bandeiraLower LIKE '%goodcard%'     THEN 15
                WHEN @bandeiraLower LIKE '%greencard%'    THEN 16
                WHEN @bandeiraLower LIKE '%hiper%'        THEN 17
                WHEN @bandeiraLower LIKE '%jcb%'          THEN 18
                WHEN @bandeiraLower LIKE '%mais%'         THEN 19
                WHEN @bandeiraLower LIKE '%maxvan%'       THEN 20
                WHEN @bandeiraLower LIKE '%policard%'     THEN 21
                WHEN @bandeiraLower LIKE '%redecompras%'  THEN 22
                WHEN @bandeiraLower LIKE '%sodexo%'       THEN 23
                WHEN @bandeiraLower LIKE '%valecard%'     THEN 24
                WHEN @bandeiraLower LIKE '%verocheque%'   THEN 25
                WHEN @bandeiraLower LIKE '%vr%'           THEN 26
                WHEN @bandeiraLower LIKE '%ticket%'       THEN 27
                ELSE 99 
            END
    );
END;
GO