sqlstringsql-updatesnowflake-cloud-data-platformsnowsql

How to replace rows containing alphabets and special characters with Blank spaces in snowflake

I have a column "A" which contains numbers for example- 0001, 0002, 0003

the same column "A" also contains some alphabets and special characters in some of the rows for example - connn, cco*jjj, hhhhhh11111 etc.

I want to replace these alphabets and special characters rows with blank values and only want to keep the rows containing the number.

which regex expression I can use here?

Solution

  • If you want to extract numbers from these values (even if they end or start with non digits), you may use something like this:

    create table testing ( A varchar ) as select *
from values ('123'),('aaa123'),('3343'),('aaaa');

select REGEXP_SUBSTR( A, '\\D*(\\d+)\\D*', 1, 1, 'e', 1 ) res 
from testing;

+------+
| RES  |
+------+
| 123  |
| 123  |
| 3343 |
| NULL |
+------+