Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2015
    Posts
    7

    Unanswered: ignore special character from database

    Hi,

    We use some special characters like © , ® etc and store them in © ® respectively. I would like to pull records from table and ignore these special characters. In other words, replacing special characters with nothing while pulling the data. I tried with translate, but didnt get the way i want. I am finding difficulty to pull data. Could anyone help me on this ?
    The problem with translate is, if i want to ignore © i need to use '®' in translate, but it will remove if the column has either 1, 7 or 4 and not necessary ® .
    Last edited by pradeepht; 09-16-15 at 20:53.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    take the hex value of the column for the row you want to get and use translate/replace by use of this character
    http://www-01.ibm.com/support/knowle...0%2F2-9-4-1-62
    http://www-01.ibm.com/support/knowle...%2F2-9-4-1-167
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jun 2015
    Posts
    7
    Thanks for responding . Could you give a example to take hex value from the row before I use translate ?

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    create function translate (
      str  varchar(4000)
    , to   varchar(4000)
    , from varchar(4000)
    )
    returns varchar(4000)
    contains sql
    deterministic
    no external action
    return 
    xmlcast(
    xmlquery('fn:translate($s, $o, $r)' passing 
      str  as "s"
    , from as "o"
    , to   as "r"
    )
    as varchar(4000));
    
    set path = USER, SYSTEM PATH;
    
    values(translate('Hello©, world®!', '', '[©®]'));
    
     1
     -------------
     Hello, world!
    Regards,
    Mark.

  5. #5
    Join Date
    Jun 2015
    Posts
    7
    Thanks Mark. But, that doesnt work for my requirement.

    Data in my table contains records like this > If i want replace ® , and &#8482 with nothing, your query works, but it would also replace individual number 8 with nothing which i do not want. Is it possible to replace group of special characters instead of one by one ?

    one of the record : records asdsad's asdsad ad® asad™ Slip-On 84Work2Shoes
    after : records asdsad's asdsad ad asad Slip-On WorkShoes which is not correct
    My expectation: records asdsad's asdsad ad asad Slip-On 84Work2Shoes

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Sorry, but it seems that I didn't get you. I don't understand what you mean referring to the '&#8482'. What is it?
    Can you provide exact string constant which you want to process?

    I have the following output in my environment:
    Code:
    values(translate('records asdsad''s asdsad ad® asad™ Slip-On 84Work2Shoes', '', '[®™]'))
    
     1
     ----------------------------------------------------
     records asdsad's asdsad ad asad Slip-On 84Work2Shoes
    Regards,
    Mark.

  7. #7
    Join Date
    Jun 2015
    Posts
    7
    Hi Mark, Thanks again for responding .


    here is my data

    "one two three ® four 14 five &#8482 six ”ad® 74"

    where &#174 represents trademark and &#8482 represents TM. Like this, we have different unicode sets and also plane numbers like 74 as mentioned above. If i use your query, it removes even plane number like 74 and returns like
    "one two three four five six ”ad" But, i do not want it to remove plane number 74 and 14. Is there a way we can specify like this ? just to exclude '&#8482' or ® ?

    Thanks,

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Try this:

    Code:
    create function regexp_replace(
      source varchar(4000)
    , pattern varchar(128)
    , replacement varchar(128)
    , flags varchar(4)
    )
    deterministic
    contains sql
    no external action
    returns varchar(4000)
    return xmlcast(
    xmlquery('fn:replace($s, $p, $r, $f)' passing 
      source as "s"
    , pattern as "p"
    , replacement as "r"
    , flags as "f"
    )
    as varchar(4000));
    
    select t.str, regexp_replace(t.str, '(®)|(&#8482)|®|™', '', '') replacement
    from table (values 
      'one two three ® four 14 five &#8482 six ”ad® 74'
    , 'records asdsad''s asdsad ad® asad™ Slip-On 84Work2Shoes'
    ) t (str);
    
     STR                                                    REPLACEMENT
     ------------------------------------------------------ ----------------------------------------------------
     one two three ® four 14 five &#8482 six ”ad® 74   one two three  four 14 five  six ”ad 74
     records asdsad's asdsad ad® asad™ Slip-On 84Work2Shoes records asdsad's asdsad ad asad Slip-On 84Work2Shoes
    Regards,
    Mark.

  9. #9
    Join Date
    Aug 2015
    Location
    Bucharest
    Posts
    9
    Mark, I came here with the same problem as pradeepht.
    The last function that you posted is something that I looking for. Thank you!

Tags for this Thread

Posting Permissions

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