CODEWITHSUNDEEP
snowflake-cloud-data-platform
Jenifer
Jenifer

Reputation: 347

Need help in converting alphanumeric column values to NULL

I need help in replacing alpha numeric values from Col_A to NULL

SELECT distinct o_id,
CASE when REGEXP_COUNT(Col_A,'^[0-9]+$')=1 then null else Col_A end 
as Col_1
FROM db_name.schema_name.table_name
WHERE date_ordered  = '2022-02-03'

When I run the below query, I'm getting one NULL value and one NOT NULL value for O_ID

select distinct o_id,Col_A from db_name.schema_name.table_005
WHERE o_id='3351456'
AND date_ordered= '2022-02-03'
O_ID COL_A
93261 NULL
93261 ABCDE12345

If we add Col_A IS NOT NULL in the WHERE clause, we will not get the NULL value in the result.

Is there any other way to implement this? The result should not contain NULL value and it should look like below:

O_ID COL_A
93261 ABCDE12345

Upvotes: 1

Views: 176

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

Adding a HAVING clause runs after the aggregation stages (the DISTINCT)

SELECT DISTINCT o_id,
CASE when REGEXP_COUNT(Col_A,'^[0-9]+$')=1 then null else Col_A END as Col_1
FROM table_005
WHERE date_ordered  = '2022-02-03'
HAVING Col_1 IS NOT NULL;

give you your expected results.

O_ID COL_1
93261 ABCDE12345

for the following data:

create table table_005 AS
     SELECT * FROM VALUES
        (93261, NULL, '2022-02-03'),
        (93261, 'ABCDE12345', '2022-02-03')
        v(O_ID, COL_A, date_ordered);

Also

CASE when REGEXP_COUNT(Col_A,'^[0-9]+$')=1 then null else Col_A END as Col_1

can be made smaller by using an IFF

IFF( REGEXP_COUNT(Col_A,'^[0-9]+$')=1,  null, Col_A) as Col_1b

But it actually makes more sense to move this all to the WHERE section with a REGEXP clause

SELECT o_id
    ,Col_A
FROM table_005
WHERE date_ordered  = '2022-02-03' 
  AND col_a not regexp '^[0-9]+$';

which gives:

O_ID COL_A
93261 ABCDE12345

Alpha Numermic Part

If you are wanting to match alpha numeric's like the title says, you will need to add a-z to your match pattern. then you will need to use a case insensitive match (witch will not work on the WHERE REGEXP version) or explicitly match, which could be used:

SELECT o_id
    ,Col_A
    ,REGEXP_COUNT(Col_A,'^[0-9]+$') as match_just_numbers
    ,REGEXP_COUNT(Col_A,'^[a-z0-9]+$',1,'i') as match_alhpa_numeric_case_insensitive
    ,REGEXP_COUNT(Col_A,'^[a-zA-Z0-9]+$') as match_alhpa_numeric_explicit
FROM VALUES        
    (1, NULL),
    (2, 'ABCDE12345'),
    (3, '12345'),
    (4, 'ABCDE'),
    (5, 'ABC!DE'),
    (6, '$123.00')
    v(o_id, col_a)
ORDER BY 1
;

but the above gives:

O_ID COL_A MATCH_JUST_NUMBERS MATCH_ALHPA_NUMERIC_CASE_INSENSITIVE MATCH_ALHPA_NUMERIC_EXPLICIT
1
2 ABCDE12345 0 1 1
3 12345 1 1 1
4 ABCDE 0 1 1
5 ABC!DE 0 0 0
6 $123.00 0 0 0

which show you use the exclude not alpha-numeric values you probably should use:

SELECT o_id
    ,Col_A
FROM table_005
WHERE date_ordered  = '2022-02-03' 
  AND col_a not regexp '^[a-zA-Z0-9]+$';

Upvotes: 2

Related Questions