regexpostgresqlnumberscasenon-alphanumeric

Extract numbers from number records and pass null to alphanumeric in Postgresql


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 :

enter image description here
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

Solution

  • 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