General discussion

  • Creator
    Topic
  • #2080489

    Oracle

    Locked

    by wbenson ·

    I have a field in my database that is 100 characters long. I want to change characters 50, 51, 52, 53, 54 as follows:

    currently: 2345U 7834U
    want: 02345 07834

    Essentially I’m trying to get rid of the end “U” and put a zero on thefront of the number. I need to do this generically as I have about 400 records that require this change. In other words, each of the 400 occurences could use different numbers (though they all end in U and I want to drop the U and put the zero in front).

All Comments

  • Author
    Replies
    • #3902588

      Oracle

      by mcristiano ·

      In reply to Oracle

      I need more particular to try to solve your problem. First of all, do you have to use only ORACLE language (like PL-SQL) or other languages like Visual BAsic? 2) The operation is automatically executed or need an user input? What about locking problems?
      Bye
      MAX

      • #3794335

        Oracle

        by wbenson ·

        In reply to Oracle

        The question was auto-closed by TechRepublic

    • #3902812

      Oracle

      by xx ·

      In reply to Oracle

      Try using translate function.

      • #3794336

        Oracle

        by wbenson ·

        In reply to Oracle

        The question was auto-closed by TechRepublic

    • #3902715

      Oracle

      by narendra ·

      In reply to Oracle

      Please try the following SELECT query first :

      SELECT REPLACE (fieldname, SUBSTR(fieldname, INSTR(fieldname, ‘U’) – 4, 5), ‘0’||RTRIM(SUBSTR(fieldname, INSTR(fieldname, ‘U’) – 4, 5), ‘U’) from TABLENAME;

      If the result you get is what you reallywantas the replacement value use it in the UPDATE statement.

      The expression is rather complex, I could not simplify it or even try it out on the database as I did not have ORACLE running when I answered this question, so please check the result before applying.

      • #3794337

        Oracle

        by wbenson ·

        In reply to Oracle

        The question was auto-closed by TechRepublic

    • #3901374

      Oracle

      by bsadiq ·

      In reply to Oracle

      DECLARE
      /* TABLE NAME IS : your_table
      COLUMN NAME IS : your_column
      Note : Since the column is in CHAR format, it’s length is always 100 */

      cursor c1 IS
      select your_column FROM your_table;
      your_var char(100);
      begin
      for i in c1 loop
      /* Checking if the column_name includes the letter ‘u’ or ‘U’ */
      IF UPPER(SUBSTR(RTRIM(i.your_column),
      LENGTH(RTRIM(i.your_column)),
      LENGTH(RTRIM(i.your_column)))) = ‘U’THEN
      your_var := ‘0’||replace(upper(i.your_column),’U’);
      update your_table set your_column = your_var
      where your_column = i.your_column;
      commit;
      ELSE
      /* nothing to update */
      NULL;
      end if;
      end loop;
      end;
      /

      • #3794338

        Oracle

        by wbenson ·

        In reply to Oracle

        The question was auto-closed by TechRepublic

    • #3901356

      Oracle

      by cdungey ·

      In reply to Oracle

      Hope this helps..
      Index you values in and let the sample code filter..
      If InStr(Text1, “U”) Then
      Text2 = “0” & Mid(Text1, 1, InStr(Text1, “U”) – 1)
      Else
      Text2 = Text1
      End If

      • #3794339

        Oracle

        by wbenson ·

        In reply to Oracle

        The question was auto-closed by TechRepublic

    • #3901254

      Oracle

      by srini ·

      In reply to Oracle

      If your number sequences are of the same length (5 + space + 5), then try the following query…

      SELECT ‘0’ || SUBSTR(col1, 1, 4) || ‘ ‘ || ‘0’ || SUBSTR(col1, 7, 4) FROM table;

      • #3794340

        Oracle

        by wbenson ·

        In reply to Oracle

        The question was auto-closed by TechRepublic

    • #3901197

      Oracle

      by pratap_singh ·

      In reply to Oracle

      Use Update statement as follows
      for string starting at loc1 and len1 and

      Update table1
      set col1=
      substr(col1,1,locstart)||’0’||substr(col1,loc1,len1)||
      substr(col1,loc1+len1+1)
      ;

      • #3794341

        Oracle

        by wbenson ·

        In reply to Oracle

        The question was auto-closed by TechRepublic

    • #3901659

      Oracle

      by vs ·

      In reply to Oracle

      Update YTAB
      set YF = substr(YF,1,49)||’0’||substr(YF,50,4)||substr(YF,55,46)
      where substr(YF,54,1)=’U’;
      — that is the simplest and effective solution.

      • #3794342

        Oracle

        by wbenson ·

        In reply to Oracle

        The question was auto-closed by TechRepublic

    • #3788998

      Oracle

      by srishankar.r ·

      In reply to Oracle

      You Can try out the following update statement. But before proceeding try the
      select version of the same update statement
      or after updation view the record and then commit.

      Let emp be the Table

      EMP

      EMP_REG_NO VARCHAR2(100)

      update emp set
      emp_reg_no = substr(emp_reg_no,1,49)
      || ‘0’
      || substr(emp_reg_no,51,4) ||
      substremp_reg_no,56,(length(emp_reg_no) – 56))

      • #3794343

        Oracle

        by wbenson ·

        In reply to Oracle

        The question was auto-closed by TechRepublic

    • #3794334

      Oracle

      by wbenson ·

      In reply to Oracle

      This question was auto closed due to inactivity

Viewing 9 reply threads