Remove unreadable characters from DB2 string

Lenny77 · #1 (**permalink**) 09-09-09, 17:56

This is the very simple query which I created to myself to remove the unreadable characters from table column, or any text, using DB2.

Unreadable characters how you know from X'00' to X'3F' and X'FF'.

You can create an UDF based on this query:

Code:

select 
text "Original Text", 
hex(text) hx_org_text, 
translate(text, chars_translate_to, chars_to_be_remove       ) trt_text,
hex(translate(text, chars_translate_to, chars_to_be_remove ) ) hx_trt_text,
replace
(translate(text, chars_translate_to, chars_to_be_remove), 
    substr(chars_translate_to, 1, 1), '') "Cleared text"   

from 
(select 
   'This' || x'0024' || ' text' || x'213733' || ' is unreadable' || x'383912'  as text
   from sysibm.sysdummy1 ) tx
join
(select 
x'000102030405060708090a0b0c0d0e0f' ||
x'101112131415161718191a1b1c1d1e1f' ||
x'202122232425262728292a2b2c2d2e2f' ||
x'303132333435363738393a3b3c3d3e3f' as chars_to_be_remove,
repeat(x'FF', 80) chars_translate_to
from sysibm.sysdummy1 ) gt
on 1 = 1

Lenny

Lenny77 · #2 (**permalink**) 09-09-09, 17:58

Please, run the query and compare results in the different columns.

Thanks, Lenny

Lenny77 · #3 (**permalink**) 09-24-09, 15:04

Suppose we have string looks like this:

Quote:

'You _______ are _____ the ____ best DB2 ____ programmers in ____ the ____ world !!!!'

You have to transform this string in following:

Quote:

'You are the best DB2 programmers in the world !!!!'

What you have to do ? There are no functions exist to do it.

I give you the simple SQL which you can use if you'll problem like this.

Code:

with
Source(source_str) as
(select 'You       are   the    best DB2      programmers in       the     world !!!!'
from sysibm.sysdummy1
)
,
String_cleaner(result_string) as
(
select strip(source_str) from Source
union All
select replace(result_string, '  ', ' ') from String_cleaner  
where Length(replace(result_string, '  ', ' ')) < length(result_string)
)
,
Final_operation(result_string, source) as ( select result_string, source_str
  from String_cleaner, Source
 where length(result_string) 
         = (select min(length(result_string)) from String_cleaner)
)
select source "Source String", result_string "Result String" 
  from Final_operation

Result of run will be:

Quote:

Result String
You are the best DB2 programmers in the world !!!!

Lenny

Lenny77 · #4 (**permalink**) 09-24-09, 16:14

I like this algorithm more then previous, but it depends on you:

Code:

with
Source(source_str) as
(select 'You     are   the    best DB2      programmers in       the     world !!!! '
from sysibm.sysdummy1
)
,
string_posn(posn, charposn, iterno) as
(
select int(1), substr(source_str, 1, 1), length(source_str)  
       from Source
union all
select posn + 1, substr(source_str, posn + 1, 1), iterno   
  from string_posn, Source
where posn + 1 <= length(source_str)
)
,
Final_operation(result_string, lastchar, K) as 
(select varchar(charposn, 1000), charposn, 1
   from string_posn where posn = 1
union all
select result_string  || 
          case 
          when lastchar = ' ' and charposn = ' ' 
          then '' 
          else charposn  
          end, charposn, K + 1 
from Final_operation, string_posn 
where posn   = K + 1
   and K + 1 <= iterno
)
,
Get_result_string(source_str, result_string) as 
( select source_str, result_string 
from Final_operation, Source 
where K = (select max(iterno) from string_posn)
) 
select * from Get_result_string

Same result in another way...

Lenny

Lenny77 · #5 (**permalink**) 09-24-09, 17:50

This one will work also, but for me it looks like hard code:

Code:

select source_str "Source String", 
replace(replace( replace( replace(source_str, rem2, sngl), rem2, sngl) , rem2, sngl), rem2, sngl ) "Result String"      
from 
(select 
'You       are   the    best DB2      programmers in       the     world !!!!' 
as source_str, repeat(' ', 2)  rem2, ' ' sngl
From sysibm.sysdummy1 ) ii

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech