In a db2 9.7 select statement's field, I need to perform a global regular expression that replaces multiple white spaces (between words) with exactly one space. The regular expression would be base on something like this:


The query (without thinning out redundant white space) looks like this:

  random_degrees_of_redundant_white-space_between_words as fieldName
from tableName

The contents of a result looks like this:

I like  to   put    varying      numbers      of    whitespaces



I've seen examples of people claiming to achieve this in DB2 9.7 via xmlquery and fn:replace, but I can't seem to accomplish this myself by looking at their examples.

Would someone be kind enough to convert my abstract example into something I can use as a model for achieving this in db2 9.7?

Please do not provide answers that achieve this via custom functions or stored procedures. I want to achieve this using an independent select statement on a default installation of DB2 9.7 (without installing any addons to db2).


1 Answer 1


You could use the the


function like this:

select replace('Text   with  random      spaces', '  ', ' ') 
  from yourtable

The second argument shows two spaces and the third is a single blank.


For multiple blanks this sould work - natively:

SELECT c1, length(c1), xmlquery('fn:replace($c,"[ ]+", " ")'  PASSING c1 AS "c") 
  FROM test1 
  • 1
    Sorry, that'll just compress doubled spaces to one space; it won't replace multiple consecutive spaces with a single one
    – Dave Jones
    Apr 22, 2017 at 16:25
  • 1
    For the OP, I'm afraid this is one area DB2 is rather weak: no built-in regex support, and no other built-in pattern matching (beyond the usual LIKE support). As you've discovered there are methods using xmlquery (and others using recursive queries) but to me they all seem horribly over-complex compared to a straight scalar function for this. There are some third party libs which provide regex support (including mine :), but you're looking for something that'll work in a "vanilla" install I'm afraid you're in for some pain.
    – Dave Jones
    Apr 22, 2017 at 16:34
  • @DaveJones : "vanilla" is ideal, but I'd still be interested in seeing what you've created. Apr 22, 2017 at 22:07
  • 1
    My db2utils suite has a bunch of PCRE driven functions including PCRE_SUB. I should stress this isn't what you're looking for: it only does single, not global replace but it wouldn't be difficult to extend for this (PRs welcome; sadly I've no time/motivation to work on db2utils these days as I don't work with DB2 anymore but I keep it alive for anyone interested).
    – Dave Jones
    Apr 22, 2017 at 22:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.