Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    13

    Unanswered: How to escape and characters to its character and numeric entity

    Is there any in-built function to convert special characters to their numerical and character entities??

    Example:
    In the table XYZ, the content of a column has following data

    Code:
    Descp
    ------
    Caring and Clinical Judgement 
    I want to query this column such that the output of it is as follows
    [For character enity]
    Code:
    Descp
    ------
    Caring and Clinical Judgement ®
    [For numeric entity]
    Code:
    Descp
    ------
    Caring and Clinical Judgement ®
    I came across a function htf.escape_sc
    But this is used to convert only the following four characters

    • & to &

    • " to &quot:

    • < to &lt;

    • > to &gt;




    Any other function or solution to convert the to &reg; and &#174;

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    The nearest that I can think of is the ACSII function, but that doesn't help you to get from '' to '&reg'.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Mar 2011
    Posts
    13
    Yes... a customized function is used now... The solution i am currently implementing is triggers.Before the data is inserted/updated to a table, the triggers will call the function which will replace the special characters to its character/numeric entity.I am able to do this for some tables with datatype varchar2 .But if the datatype is long. i am facing problem, since triggers doesn't support long dataype
    Code:
    Table: LONGCONTENT
    Columns :
    i) ID -- Number(6)
    ii) Descp -- Long

    Below is the sample trigger :
    Code:
    CREATE OR REPLACE TRIGGER BF_UPDINS
    BEFORE INSERT OR UPDATE
    ON LONGCONTENT
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    
    BEGIN
    
    IF INSERTING THEN
    :new.descp :=pack.trans(:new.descp );
    END IF;
    
    IF UPDATING THEN
    IF :new.descp :old.descp OR :old.descp IS NULL THEN
    :new.descp := pack.trans(:new.descp );
    END IF;
    END IF;
    
    END;
    pack.trans is a customized function which is used to convert the special characters entered to its corresponding character entity

    Note:The column's datatype cannot be changed from Long to varchar2/clob due to some restrictions from our application side.

    Is there any other way to do this ... for long dataype ???
    Please help me on this... Its urgent

Posting Permissions

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