SQL Nested Case When with numeric check

Question

I'm trying to create a "filter" with SQL.

I have a table named ARTICLE like this :

    CODE           |  NAME           |  QUANTITY    |
    _______________|_________________|_______________   
    0020717270084  |  MANGO FRUIT 1L |      3       |
    0884394000774  |  ALOE VERA 50CL |      4       |
    16             |  CHEWING GUM    |      10      |
    IGLOO          |  IGLOO ICE      |      5       |

I want to do a SELECT with a verification on CODE. If CODE is a number AND has a length of 8 OR 10 OR 13 digits, i display CODE ELSE i have to display * before CODE (simple concat).

I can do CASE WHEN but this one is a little bit tricky for me.

Thanks for your help.

Answer 1

You would do this with a case statement. Databases do the checks differently. The following is an approach using SQL Server:

select (case when len(code) in (8, 10, 13) and code not like '%[^0-9]%'
             then code
             else '*' + code
        end)