Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Unanswered: Converting numbers to string characters

    I want to create a view whereby I want to convert numbers below to String characters. They will be taken from another table called hierachy to which the numbers represent?

    here is the current view..

    CREATE OR REPLACE VIEW v_field_audit
    AS
    SELECT FIELD_AUDIT_ID,
    TABLE_TYPE,
    TABLE_ID,
    FIELD_NAME,
    decode(instr(old_value,chr(124)),0,old_value,subst r(old_value,1,instr(old_value,chr(124),1,1)-1)) OLD1,
    decode(instr(old_value,chr(124)),0,null,substr(old _value,instr(old_value,chr(124),1,1)+1,decode(inst r(old_value,chr(124),1,2),0,length(old_value)-instr(old_value,chr(124),1,1),instr(old_value,chr( 124),1,2)-instr(old_value,chr(124),1,1)+1))) OLD2,
    decode(instr(old_value,chr(124)),0,null,substr(old _value,instr(old_value,chr(124),1,2)+1,decode(inst r(old_value,chr(124),1,3),0,decode(instr(old_value ,chr(124),1,2),0,null,length(old_value)-instr(old_value,chr(124),1,2)),instr(old_value,chr (124),1,3)-instr(old_value,chr(124),1,2)+1))) OLD3,

    decode(instr(old_value,chr(124)),0,null,substr(old _value,instr(old_value,chr(124),1,3)+1,decode(inst r(old_value,chr(124),1,4),0,decode(instr(old_value ,chr(124),1,3),0,null,length(old_value)-instr(old_value,chr(124),1,3)),instr(old_value,chr (124),1,4)-instr(old_value,chr(124),1,3)+1))) OLD4,

    decode(instr(new_value,chr(124)),0,new_value,subst r(new_value,1,instr(new_value,chr(124),1,1)-1)) NEW1,
    decode(instr(new_value,chr(124)),0,null,substr(new _value,instr(new_value,chr(124),1,1)+1,decode(inst r(new_value,chr(124),1,2),0,length(new_value)-instr(new_value,chr(124),1,1),instr(new_value,chr( 124),1,2)-instr(new_value,chr(124),1,1)+1))) NEW2,
    decode(instr(new_value,chr(124)),0,null,substr(new _value,instr(new_value,chr(124),1,2)+1,decode(inst r(new_value,chr(124),1,3),0,decode(instr(new_value ,chr(124),1,2),0,null,length(new_value)-instr(new_value,chr(124),1,2)),instr(new_value,chr (124),1,3)-instr(new_value,chr(124),1,2)+1))) NEW3,

    decode(instr(new_value,chr(124)),0,null,substr(new _value,instr(new_value,chr(124),1,3)+1,decode(inst r(new_value,chr(124),1,4),0,decode(instr(new_value ,chr(124),1,3),0,null,length(new_value)-instr(new_value,chr(124),1,3)),instr(new_value,chr (124),1,4)-instr(new_value,chr(124),1,3)+1))) NEW4,
    USER_ID,
    CHANGED_ON,
    REASON
    FROM FIELD_AUDIT
    WHERE TABLE_TYPE='P'
    and field_audit_id=138
    /

    However, I the result for a simple select is as follows:
    select Old_value from field_audit
    where field_audit_id=293
    order by changed_on desc

    1,54,191,216,227|1,54,191,216,230|1,54,191,216,229 |1,54,191,216,231

    Therefore I want to convert these numbers to characters so they make sense?

    Any ideas?
    would a function be better?

    Cheers
    Emer
    Cheers
    Etravels

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I am so confused.

    You say number --> character, but it looks like you are starting with a character string.

    Are you trying to strip out the pipe? "|"

    How about you show the output of what you currently get, and then show
    the output how you would like it?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2004
    Posts
    93

    so..

    Currently the result is:

    1,54,191,216,227|1,54,191,216,230|1,54,191,216,229 |1,54,191,216,231

    and I want to convert all the numbers to characters and replace | with |'-'| which is fine...

    and want it to display

    1-Complaint 54-complaints_handling 191-compensation, 216-amount, 227-5.00

    etc

    Cheers
    Cheers
    Etravels

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Not sure I understand you fully, but I pulled this script from

    http://www.orafaq.com/scripts/index.htm#GENSQL

    It changes numerics to their character equivalent (i.e. 10 = ten)

    select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
    decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
    from dual
    /
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    I think he's confusing a character-string (that happens to have numbers in it)
    and saying it is a number.

    What makes a character-string is the column that the value is stored in.
    old_value varchar2(1000) = characters (regardless of what is in there)
    old_value number(100) = numbers

    anyways, so you want to replace values with other values.
    let's see if we can help you.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    quick question.

    Code:
    so you want this:
    
    1,54,191,216,227|1,54,191,216,230|1,54,191,216,229|
    
     to look like this: 
    
    1-Complaint 54-complaints_handling 191-compensation, 216-amount, 227-5.00 |'-'|1-Complaint 54-complaints_handling 191-compensation, 216-amount, 230-8.00|'-'|1-Complaint 54-complaints_handling 191-compensation, 216-amount, 229-7.00
    that right? or do you want seperate lines for each pipe "|"

    look at this link:

    http://asktom.oracle.com/pls/ask/f?p...69234696::NO::
    Last edited by The_Duck; 11-23-04 at 13:57.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Yeh, Tom says you do not need PLSQL, but he should also say that you can't read the SQL (he typed). Or if you can read it I wonder how long it would take to retype it without errors!! :-)

    Given your input you have and the output you want Emer I would opt for a function (You should know I am a developer first then a DBA).

    However, looking deeper into your issue I wonder you can get to the values of 1 or 54 or etc. before they are added to the string? That would simplfy your life.

    THe bottom line for me is a function that will parse through the string and return a string formatted the way you want it. The view could call the function itself.
    NOTE: Please disregard the label "Senior Member".

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    If you are on 10g, with a string of..
    Code:
    SQL@10g> select old_value from field_audit;
    
    OLD_VALUE
    ----------------------------------------------------------------------------------------------------
    1,54,191,216,227|1,54,191,216,230|1,54,191,216,229|1,54,191,216,231
    .. using the regexp_replace function, you will get the following output ( see the regular expression pattern matching )..
    Code:
    SQL@10g> select regexp_replace( old_value,
      2                         '\|?([[:digit:]]{1,3}),([[:digit:]]{1,3}),([[:digit:]]{1,3}),([[:digit:]]{1,3}),([[:digit:]]{1,3})\|?',
      3                         '\1-Complaint \2-complaints_handling \3-compensation, \4-amount, \5-5.00 ' ) new_value
      4    from field_audit
      5  /
    
    NEW_VALUE
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1-Complaint 54-complaints_handling 191-compensation, 216-amount, 227-5.00 1-Complaint 54-complaints_handling 191-compensation, 216-amount, 230-5.00 1-Complaint 54-complaints_handling 191-compensation,
     216-amount, 229-5.00 1-Complaint 54-complaints_handling 191-compensation, 216-amount, 231-5.00
    Now, if you are not on 10g, it will be of help if you provide your version so we can determine a work around approach for you. Also, it is very important if you answer The_Duck's question.
    Last edited by JMartinez; 11-23-04 at 15:18.

Posting Permissions

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