Need to add commas to a list of numbers with regexp_replace (Postgresql)

Question

I'm trying to write a postgres function that will sanitize a list of numbers into a list of comma-separated numbers. These numbers are being entered into an input field. I want to allow users to just enter a line of space-separated numbers (ex: 1 3 4 12) and have it change it to 1,3,4,12.

But, if they do enter it correctly (ex: 1,3,4,12 or 1, 3, 4, 12), I still want it to sanitize it to 1,3,4,12. I also have to account for things like (ex: 1, 3 4, 12).

This is what I'm currently doing:

select regexp_replace(trim(list_of_numbers), '[^0-9.] | [^,]', ',', 'g')

If I have a list like this:

select regexp_replace(trim('1, 2, 4, 14'), '[^0-9.] | [^,]', ',', 'g')

it returns : "1,2,4,14" so that's good.

But, if I have a list like this:

select regexp_replace(trim('1 2 4 14'), '[^0-9.] | [^,]', ',', 'g')

it returns : "1,,,4"

A. Bandtock · Accepted Answer · 2018-03-16 14:53:32Z

1

If you change your regex to [^0-9.]+ it'll replace all non-numerics (i.e. , ,, ,) with a ,.

Try it out here

answered Mar 16, 2018 at 14:53

A. Bandtock

1,2519 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 0 · Accepted Answer · 2018-03-16 14:58:41Z

0

I think the best option is to convert to an array using regexp_split_to_array then turn that back into a string:

The following:

with t(input) as (
  values 
    ('1 3 4 12'), 
    ('1,3,4,12'),
    ('1, 3 4, 12'),
    ('1,3,4,12'),
    ('1   3  4 , 12'),
    ('   1,  2  , 4 12   ')
)
select array_to_string(regexp_split_to_array(trim(input),'(\s+)|(\s*,\s*)'), ',')
from t;

returns:

array_to_string
---------------
1,3,4,12       
1,3,4,12       
1,3,4,12       
1,3,4,12       
1,3,4,12       
1,3,4,12

edited Mar 16, 2018 at 14:58

answered Mar 16, 2018 at 14:52

user330315

2 Comments

Andomar Over a year ago

Your regex would match many spaces multiple times, f.e. "1____4" becomes "1,,,4". I thought (,|\s)+ for any combination of whitespace is a better fit

Log Over a year ago

This works unless the user enters extra spaces (ex: '1, 2, 4 12'). Then you get "1,2,4,,,,12".

Andomar · Accepted Answer · 2018-03-16 14:59:49Z

0

You could split the string on any amount of whitespace or commas (,|\s)+ and join it back together using commas:

select array_to_string(regexp_split_to_array('1 2 4 14', '(,|\s)+'), ', ');

edited Mar 16, 2018 at 14:59

user330315

answered Mar 16, 2018 at 14:51

Andomar

239k55 gold badges387 silver badges412 bronze badges

3 Comments

A. Bandtock Over a year ago

The issue with this is that you'll get redundant commas, e.g. 1, 2 3 4 -> 1,,2,3,4

Log Over a year ago

That works if they enter it like, '1 2 3 14', but if they actually put in commas like, '1, 2, 3, 14', it adds extra commas (""1,, 2,, 4,, 14"").

Andomar Over a year ago

Added commas as part of the split regex, should work now

Collectives™ on Stack Overflow

Need to add commas to a list of numbers with regexp_replace (Postgresql)

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related