I have a test table in Postgresql. It has a field value_exist which is a string but it has alphanumeric values in it and some special characters in the data. I want to ignore the characters [/alpha/?/$/encoding/.] in it and check if the record has only numbers. If its only numbers then we need only numbers else for alphanumeric we need null. Refer the below example :
I tried the below code but it doesn't give me the desired output :
SELECT CASE WHEN value_exist ~ '^([$?\\*+-][0-9]|[0-9][$?\\*+-]|[0-9][0-9])$'
THEN REGEXP_REPLACE(value_exist, '[$?\\*+-]', '', 'g')
ELSE NULL END value_new
FROM test_table
I tested this and this works.. It takes only numbers and if we get other than numbers including numbers then it passes it as null .
select case when 'H44$'~'^[0-9]*$' then 88
else null end ---- Result -> null
select case when '44'~'^[0-9]*$' then 88
else null end ---- Result -> 44