1

I am passing around 1000 of arguments to the function REPLACE.

Example:

String contains some values:

 Declare
        str1 varchar = '1,2,3,4.................1000';

Now I want to replace the , with the "," for which I am using the following script:

 SELECT REPLACE(str1,',','","');

But getting an error:

Error Detail:

cannot pass more than 100 arguments to a function
MAK
  • 6,824
  • 25
  • 74
  • 131
  • 4
    That error message can not be caused by the `replace()` function - at least not if you call it the way you have show us: http://sqlfiddle.com/#!15/d41d8/4617 –  Mar 03 '15 at 12:12

1 Answers1

1

The replace function can search for only one string to replace. You can look for multiple strings with the regexp_replace function. This example replaces both a and c with nothing:

select regexp_replace('abc', '(a)|(c)', '', 'g');
-->
b

The g option stands for global, which allows multiple replacements. Note that regex_replace can look for multiple strings, but is still limited to one replacement string.

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • But `SELECT REPLACE('1,2,3,4,5', ',', '","')` returns `1","2","3","4","5` – jpstrube Mar 03 '15 at 12:03
  • 1
    @struwi It does, but it can search for only one string to find and replace – Andomar Mar 03 '15 at 12:05
  • Yes, that´s what was requested. To replace commas. – jpstrube Mar 03 '15 at 12:15
  • @struwi: I agree with you that in that case `replace` should work. As that solution is even in the question I assumed the OP was looking for different strings to find and replace. – Andomar Mar 03 '15 at 12:20
  • @Andomar,Can you please help me for this: http://stackoverflow.com/questions/28855405/postgresql-9-3-dynamic-pivot-table-for-huge-records – MAK Mar 05 '15 at 06:36