Write to SQL query to Classify the values in the column based on criteria.

For instance, the Name column in the source table contains strings (letters), integers, as well as a mix of characters, numbers, and special symbols. 

Write SQL query to convert letter ---> string, numbers---> number and a1----> alphnumeric
and if column having printable character then special characters.
  


Explanation:
·         CASE statement is used to check the content of the name column.
·         Regular expressions (~) are used in PostgreSQL to match patterns.
·         In SQL Server, we use the LIKE operator with pattern matching to define the conditions.
·         Each condition checks for specific patterns:
o   Only characters (a-z or A-Z)
o   Only numbers (0-9)
o   Alphanumeric (letters and numbers)
o   Alphanumeric with special characters (letters, numbers, and specific special characters like #)
·         Make sure to replace your_table_name with the actual name of your table


Oracle & MYSQL

with cte as (

select 'a' as s_name from dual union all

select '1' from dual union all

select 'a1' from dual union all

select 'w#' from dual

)

select s_name,regexp_like(s_name,'[^[:alpha:]]') alp ,regexp_like(s_name,'[^[:digit:]]') dig,

regexp_like(s_name,'^[[:alnum:]]+$') spe,

case when regexp_like(s_name,'[^[:alpha:]]')=0 then 'string'

when regexp_like(s_name,'[^[:digit:]]')=0 then 'digit'

when regexp_like(s_name,'^[[:alnum:]]+$')=0 then 'special char'

else 'alphanumeric' end t_name

from cte;

 

 




PostgreSQL

SELECT

    name,

    CASE

        WHEN name ~ '^[a-zA-Z]+$' THEN 'char'                  -- Only letters

        WHEN name ~ '^[0-9]+$' THEN 'numeric'                  -- Only numbers

        WHEN name ~ '^[a-zA-Z0-9]+$' THEN 'alpha numeric'            -- Alphanumeric

        WHEN name ~ '^[a-zA-Z0-9#]+$' THEN 'alpha special'      -- Alphanumeric with special characters

        ELSE 'other'                                           -- Anything else (optional)

    END AS classification

FROM cte;

 


SQL SERVER

 

SELECT    name, CASE

WHEN name NOT LIKE '%[^a-zA-Z]%' AND name NOT LIKE '%[^0-9]%' THEN 'char'                            -- Only letters

WHEN name NOT LIKE '%[^0-9]%' THEN 'numeric'    -- Only numbers

WHEN name NOT LIKE '%[^a-zA-Z0-9]%' THEN 'Alpha numeric'   -- Alphanumeric

WHEN name NOT LIKE '%[^a-zA-Z0-9#]%' THEN 'alpha special'  -- Alphanumeric with special characters

ELSE 'other'          -- Anything else (optional)  END AS classification

FROM cte;