Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Unanswered: Remove unreadable characters from DB2 string

    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

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Please, run the query and compare results in the different columns.

    Thanks, Lenny

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Remove extra spaces inside of the string

    Suppose we have string looks like this:

    'You _______ are _____ the ____ best DB2 ____ programmers in ____ the ____ world !!!!'
    You have to transform this string in following:

    '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:

    Result String
    You are the best DB2 programmers in the world !!!!
    Lenny
    Last edited by Lenny77; 09-24-09 at 16:19.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Another way to remove extra spaces from string

    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
    Last edited by Lenny77; 09-24-09 at 17:31.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down Simply Hard Code

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •