1 2 Previous Next 25 Replies Latest reply: Jan 29, 2013 11:12 AM by rp0428 RSS

    sql not deleting spaces

    Tshifhiwa
      hi i have the folllowing sql trying to delete spaces,but it does not delete what happen is it show that 2 rows have been updated but when i go to the table i still find extra spaces because when i run again the same sql it still show that 2 rows have been updated
      UPDATE CFG_OFFICE
         SET OFFICECODE  = TRIM (OFFICECODE);   
      
      I did commit after update
      Edited by: adf009 on 2013/01/29 10:16 AM
        • 1. Re: sql not deleting spaces
          Chanchal Wankhade
          Hi,


          Have you commited the transaction after update.
          For example.
          SQL> select * from test;
          
               EMPNO EMPTYPE                  SALARY JDATE         DEPTNO
          ---------- -------------------- ---------- --------- ----------
                   6 back office                2000 24-DEC-12         99
                   1 HR                        10000 01-JAN-12         10
                   2 Admin                     10000 01-DEC-11         20
                   3 MARKETING                 12000 01-JAN-12         30
                   4 FACILITY                  14000 12-FEB-12         40
                   8 IT                        12000 10-JUN-10         80
                   5 ADMIN                     11111 23-JAN-13         50
                   9 Finance                   10000 07-NOV-12         90
                  10 "100HR"                    9000 07-NOV-12        100
          
          9 rows selected.
          
          SQL> update test set emptype=' back office' where empno=6;
          
          1 row updated.
          
          SQL> commit;
          
          Commit complete.
          
          SQL> select * from test;
          
               EMPNO EMPTYPE                  SALARY JDATE         DEPTNO
          ---------- -------------------- ---------- --------- ----------
                   6  back office               2000 24-DEC-12         99
                   1 HR                        10000 01-JAN-12         10
                   2 Admin                     10000 01-DEC-11         20
                   3 MARKETING                 12000 01-JAN-12         30
                   4 FACILITY                  14000 12-FEB-12         40
                   8 IT                        12000 10-JUN-10         80
                   5 ADMIN                     11111 23-JAN-13         50
                   9 Finance                   10000 07-NOV-12         90
                  10 "100HR"                    9000 07-NOV-12        100
          
          9 rows selected.
          
          SQL> update test set emptype=trim(emptype);
          
          9 rows updated.
          
          SQL> commit;
          
          Commit complete.
          
          SQL>
          SQL> select * from test;
          
               EMPNO EMPTYPE                  SALARY JDATE         DEPTNO
          ---------- -------------------- ---------- --------- ----------
                   6 back office                2000 24-DEC-12         99
                   1 HR                        10000 01-JAN-12         10
                   2 Admin                     10000 01-DEC-11         20
                   3 MARKETING                 12000 01-JAN-12         30
                   4 FACILITY                  14000 12-FEB-12         40
                   8 IT                        12000 10-JUN-10         80
                   5 ADMIN                     11111 23-JAN-13         50
                   9 Finance                   10000 07-NOV-12         90
                  10 "100HR"                    9000 07-NOV-12        100
          
          9 rows selected.
          
          SQL>
          Edited by: Chanchal Wankhade on Jan 28, 2013 11:15 PM
          • 2. Re: sql not deleting spaces
            Manik
            Check if its really a space...for those two records

            Print the o/p of this.. I guess they are special characters.
            select dump(OFFICECODE) from CFG_OFFICE;
            Cheers,
            Manik.
            • 3. Re: sql not deleting spaces
              Tshifhiwa
              i got this,yes i did commit;
              DUMP(OFFICECODE)
              ----------------------------------------------------------
              Typ=96 Len=5: 80,84,65,32,32
              Typ=96 Len=5: 83,85,82,86,32
              
              its showing 5 spaces allocated but i only have 3 letter in the column,so there is still 2 extra spaces,but when i run the sql it shows that its updating
              Edited by: adf009 on 2013/01/29 10:19 AM

              Edited by: adf009 on 2013/01/29 10:22 AM

              Edited by: adf009 on 2013/01/29 10:25 AM
              • 4. Re: sql not deleting spaces
                Chanchal Wankhade
                Hi,

                If you are not able to drop space where you think there is a space after performing above update and commiting then it may not be the space issue.
                • 5. Re: sql not deleting spaces
                  Manik
                  Based on your dump you are trying to remove spaces from "PTA " -- two spaces at the end of the string.
                  And this works for me ..
                  SELECT DUMP (TRIM ('PTA  ')) FROM DUAL;
                  Output:
                  DUMP(TRIM('PTA'))
                  Typ=1 Len=3: 80,84,65  ---- observe no 32 i.e no spaces.
                  What different are you doing?

                  Cheers,
                  Manik.
                  • 6. Re: sql not deleting spaces
                    Chanchal Wankhade
                    Hi,

                    You can find out spaces in the column by following query
                    Space in the start of the values
                    Here you can increase the value for the space like you have 2 space so use 2 in place of 1
                     
                    select emptype from test where substr(emptype,1,1) = ' ';
                    space in the trail of the value
                    select emptype from test where emptype like '% ' or emptype like ' %';
                    • 7. Re: sql not deleting spaces
                      Tshifhiwa
                      yes there is two sapces at the end ,this is what am doing
                      running this sql
                      UPDATE CFG_OFFICE
                         SET OFFICECODE  = TRIM (OFFICECODE);
                      
                      nothing else am doing,why the spaces ar not been updated,when running above sql
                      Edited by: adf009 on 2013/01/29 10:35 AM
                      • 8. Re: sql not deleting spaces
                        Tshifhiwa
                        hi the first sql return nothing the second sql return 2 rows
                        this sql return nothing
                        select OFFICECODE from CFG_OFFICE where substr(OFFICECODE,1,1) = ' ';
                        
                        
                        this sql return two rows
                         select OFFICECODE from CFG_OFFICE where OFFICECODE like '% ' or OFFICECODE like ' %';
                        Edited by: adf009 on 2013/01/29 10:36 AM
                        • 9. Re: sql not deleting spaces
                          Purvesh K
                          I feel confused after reading the entire thread.

                          may I request you to help with the output of each of the following SQL's:
                          select officecode, length(officecode)
                            from cfg_office;
                          
                          UPDATE CFG_OFFICE
                             SET OFFICECODE  = TRIM (OFFICECODE);
                          
                          select officecode, length(officecode)
                            from cfg_office;
                          
                          
                          commit;
                          • 10. Re: sql not deleting spaces
                            Chanchal Wankhade
                            Hi,

                            Look at my solution.
                            SQL> update test set emptype='back office  ' where empno=6;
                            
                            1 row updated.
                            
                            SQL> commit;
                            
                            Commit complete.
                            
                            SQL> select emptype from test where emptype like '% ' or emptype like ' %';
                            
                            EMPTYPE
                            --------------------
                            back office
                            
                            SQL> update test set
                              2  emptype=regexp_replace(emptype,'[[:space:]]*','');
                            
                            9 rows updated.
                            
                            SQL> commit;
                            
                            Commit complete.
                            
                            SQL> select emptype from test where emptype like '% ' or emptype like ' %';
                            
                            no rows selected
                            • 11. Re: sql not deleting spaces
                              Manik
                              What is the datatype of OFFICECODE
                              is it char(5)???

                              Cheers,
                              Manik.
                              • 12. Re: sql not deleting spaces
                                Tshifhiwa
                                ok
                                SQL> select officecode, length(officecode)
                                  2    from cfg_office;
                                
                                OFFIC LENGTH(OFFICECODE)
                                ----- ------------------
                                PTA                    5
                                SURV                   5
                                
                                SQL> UPDATE CFG_OFFICE
                                  2     SET OFFICECODE  = TRIM (OFFICECODE);
                                
                                2 rows updated.
                                
                                SQL> select officecode, length(officecode)
                                  2    from cfg_office;
                                
                                OFFIC LENGTH(OFFICECODE)
                                ----- ------------------
                                PTA                    5
                                SURV                   5
                                • 13. Re: sql not deleting spaces
                                  Tshifhiwa
                                  its
                                  Name                                                  Null?    Type
                                  ----------------------------------------------------- -------- ------------------------------------
                                  OFFICECODE                                            NOT NULL CHAR(5)
                                  OFFICENAME                                            NOT NULL VARCHAR2(20)
                                  OFFICEREGION                                          NOT NULL VARCHAR2(20)
                                  • 14. Re: sql not deleting spaces
                                    Manik
                                    Clearly its char(5)

                                    :)

                                    Cheers,
                                    Manik.
                                    1 2 Previous Next