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

Question

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

Need only exact numerics i.e. 0-9 from number records, rest all (special characters/alphanumeric) passed as null. — user1538020, Nov 29 '16 at 04:10
Please clarify the question. What is `[/alpha/?/$/encoding/.]`? Integer numbers or floating point numbers? With or without sign? Do In understand correctly that the result can only be a number or `NULL`? — Laurenz Albe, Nov 29 '16 at 10:53
alpha - alphabets , $ - special characters , encoding could be any characters that are in the table. I tried the below,it works for alphanumeric: SELECT (case when 'kk9' ~ '([ a-z A-Z ])' then null else 77 end) K. But I want to check for "$99$" - This should give me null but it is not giving me null. So, for $ it is not able to handle it. — user1538020, Nov 29 '16 at 12:07
1) What is [/alpha/?/$/encoding/.]? alpha - alphabets,?,$ and any encoding/special characters should be passed as null. 2) Integer numbers or floating point numbers? Integer numbers 3) With or without sign? It can have - + signs as well , this should be passed as null 4) Do In understand correctly that the result can only be a number or NULL? The result should contain ONLY numbers for only number values and null for other values. — user1538020, Nov 29 '16 at 12:34
I tried using the below and it seems that it works, i am not sure if I can use the below or if this is correct or any other better way ? : SELECT (case when '9955?' ~ '([ a-z A-Z $ - ?])' then null else 77 end) K — user1538020, Nov 29 '16 at 12:34

Laurenz Albe · Answer 1 · 2016-11-29T15:10:59.723

0

SELECT nullif(
          regexp_replace(
             value_exist,
             E'^.*[^\\d].*$',
             'x'),
          'x')
FROM test_table;

edited Nov 29 '16 at 15:10

answered Nov 29 '16 at 12:40

Laurenz Albe

209,280
17
206
263

SELECT nullif( regexp_replace( '44', '^.*[^\d].*$', ''), 'g'); FROM test_table; --> This gives me null – user1538020 Nov 29 '16 at 13:23
SELECT nullif( regexp_replace( '44', '^.*[^\d].*$', 'x'), 'x'); -- This gives me null – user1538020 Nov 29 '16 at 13:24
Maybe you have a very old PostgreSQL version. I have modified the answer to use extended string syntax, that should work everywhere. Second thought: if you embed this string in a programming language like Java or C, you'd have to double backslashes. – Laurenz Albe Nov 29 '16 at 15:12
Thanks Laurenz.. Yups i have a old PostgreSQL version.. thanks :) – user1538020 Nov 29 '16 at 18:01
Thanks are best expressed with upvote and/or accepting as correct answer. I'm pleased I could help. – Laurenz Albe Dec 01 '16 at 09:45

score 0 · Accepted Answer · answered Nov 29 '16 at 13:20

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

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

2 Answers2