Db2 – Select Statement with RegEx Replace – DB2 9.7

db2db2-luwregular expressionselect

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:

\s{2,}

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

select
  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

     between

   words.

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).

Best Answer

You could use the the

replace

function like this:

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

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

EDIT:

For multiple blanks this sould work - natively:

SELECT c1, length(c1), xmlquery('fn:replace($c,"[ ]+", " ")'  PASSING c1 AS "c") 
  FROM test1
Related Question