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"
If you change your regex to [^0-9.]+ it'll replace all non-numerics (i.e. , ,, ,) with a ,.