Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Posts
    61

    Unanswered: Update specific position - Char null field !

    Hello,

    I'd like ask help for this community about a problem to insert a text in a column char field.


    The field can be filled with null or with some data, for this reason I can't use substr (in case null or incomplete information, the substr won't return any information to concate and back data to field).

    Ex:

    Case 1

    Field
    <null>

    Case 2

    'a12kok'


    Well I need insert information at position 30 ! Both case, I haven't information in position 30, so the final result must be:

    Case 1
    '*****************************insered'

    Case 2

    'a12kok***********************insered'

    The two examples has 29 characteres, before the text insered ! (imagine * is a blank caracter)

    In case 2, we preserved the information 'a12kok'.

    Well, I really don't know how to do this, because I did'nt find a function to "insert" a text in a char column !

    Could please anyone give me one way ?

    Thanks !

    Fernando.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Keyword here is LPAD (see what it does in documentation); here's an example:
    Code:
    SQL> create table test (col varchar2(50));
    
    Table created.
    
    SQL> insert into test (col)
      2    select val || lpad('insered', 50 - nvl(length(val), 0), '*')
      3    from (select 'a12kok' val from dual union all
      4          select null from dual
      5         );
    
    2 rows created.
    
    SQL> select * from test;
    
    COL
    --------------------------------------------------
    a12kok*************************************insered
    *******************************************insered
    
    SQL>

Posting Permissions

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