SQL Convertion Query using Oracle, MySQL, PostgreSQL and SQL Server

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;