Home » SQL & PL/SQL » SQL & PL/SQL » To_number on only number fields (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
To_number on only number fields [message #619385] Tue, 22 July 2014 03:18 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All ,

I want to update the code column to hold only number value(with out leading zero)if it contains number values .
if it is string it should store the total value.


create table TEST_TABLE
(
  CODE VARCHAR2(2)
)

insert into dbo.test_table (CODE)
values ('');

insert into dbo.test_table (CODE)
values ('HQ');

insert into dbo.test_table (CODE)
values ('04');

insert into dbo.test_table (CODE)
values ('BM');

insert into dbo.test_table (CODE)
values ('08');

insert into dbo.test_table (CODE)
values ('26'); 




Output

08-8
26-26
BM-BM

Thanks
SaiPradyumn
Re: To_number on only number fields [message #619387 is a reply to message #619385] Tue, 22 July 2014 03:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL> update test_table set
  2    code = to_number(code)
  3    where regexp_like(code, '^\d+$');

3 rows updated.

SQL> select * From test_Table;

CO
--

HQ
4
BM
8
26

6 rows selected.

SQL>
Re: To_number on only number fields [message #619392 is a reply to message #619385] Tue, 22 July 2014 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Never put the schema name in the test case, we have not the same accounts.
That said, thanks for the test case.

SQL> create or replace trigger trg before insert on test_table for each row
  2  declare
  3    v varchar2(2);
  4  begin
  5    :new.code := case
  6                   when regexp_like(:new.code,'^[0-9]*$')
  7                     then to_char(to_number(:new.code))
  8                   else :new.code
  9                 end;
 10  end;
 11  /

Trigger created.

SQL> insert into test_table (CODE) values ('');

1 row created.

SQL> insert into test_table (CODE) values ('HQ');

1 row created.

SQL> insert into test_table (CODE) values ('04');

1 row created.

SQL> insert into test_table (CODE) values ('BM');

1 row created.

SQL> insert into test_table (CODE) values ('08');

1 row created.

SQL> insert into test_table (CODE) values ('26');

1 row created.

SQL> select * from test_table;

CO
--

HQ
4
BM
8
26

Re: To_number on only number fields [message #619395 is a reply to message #619387] Tue, 22 July 2014 04:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Littlefoot wrote on Tue, 22 July 2014 14:07
SQL> update test_table set
  2    code = to_number(code)
  3    where regexp_like(code, '^\d+$');

3 rows updated.


@LF, If I am not missing something,
Why to_number for a varchar2 data type? And it should update only 2 rows, i.e. for values 4 and 8, 26 should be left out.

[Updated on: Tue, 22 July 2014 04:13]

Report message to a moderator

Re: To_number on only number fields [message #619396 is a reply to message #619395] Tue, 22 July 2014 04:13 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understand what you are saying.

I usually use TO_NUMBER if I want to convert '08' (string) to 8 (number), which is what the OP wants. True, I could/should have applied additional TO_CHAR to it (as Michel did) as the results is stored back into a VARCHAR2 column.

Unless that's what you asked, could you, please, explain the question?
Re: To_number on only number fields [message #619397 is a reply to message #619385] Tue, 22 July 2014 04:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
SQL> UPDATE test_table
  2     SET code = ltrim(code, '0')
  3   WHERE substr(code, 0, 1) = '0';

2 rows updated.

SQL>
SQL> SELECT * FROM test_table;

CO
--

HQ
4
BM
8
26

6 rows selected.
Re: To_number on only number fields [message #619398 is a reply to message #619396] Tue, 22 July 2014 04:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Littlefoot wrote on Tue, 22 July 2014 14:43
I usually use TO_NUMBER if I want to convert '08' (string) to 8 (number), which is what the OP wants. True, I could/should have applied additional TO_CHAR to it (as Michel did) as the results is stored back into a VARCHAR2 column.


I updated my comment with the reason. I am concerned with the number of rows being updated, i.e. the rows being considered for update as picked by the predicate.


Quote:
could you, please, explain the question?


Ok. Let's say I add 4 more rows which DOESN'T have leading zeroes in the code value. So, the update should not affect them.

SQL> INSERT INTO test_table VALUES ('1');

1 row created.

SQL> INSERT INTO test_table VALUES('2');

1 row created.

SQL> INSERT INTO test_table VALUES('3');

1 row created.

SQL> INSERT INTO test_table VALUES('4');

1 row created.


Your query :

SQL> update test_table set
  2        code = to_number(CODE)
  3        where regexp_like(code, '^\d+$');

7 rows updated.


So, your query updated 7 rows. But it should ONLY update the values with leading zeroes.

My query :

SQL> rollback;

Rollback complete.

SQL> UPDATE test_table
  2     SET code = ltrim(code, '0')
  3   WHERE substr(code, 0, 1) = '0';

2 rows updated.


It updated only those 2 rows which have leading zeroes.
Re: To_number on only number fields [message #619401 is a reply to message #619398] Tue, 22 July 2014 04:39 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

What about this one ?

insert into test_table (CODE)
values ('');
insert into test_table (CODE)
values ('0');

SELECT * FROM test_table;

UPDATE test_table
    SET CODE = ltrim(CODE, '0')
    WHERE substr(CODE, 0, 1) = '0';
    ROLLBACK;
    
    SELECT * FROM test_table;
 update test_table set
     CODE = to_number(CODE)
      where regexp_like(CODE, '^\d+$');    
      
      COMMIT;


[Updated on: Tue, 22 July 2014 04:40]

Report message to a moderator

Re: To_number on only number fields [message #619402 is a reply to message #619401] Tue, 22 July 2014 04:42 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@Lalit: now I understand. Your initial question (before you modified it) was why I used TO_NUMBER.

I agree; I'm doing unnecessary updates.
Re: To_number on only number fields [message #619406 is a reply to message #619397] Tue, 22 July 2014 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Lalit Kumar B wrote on Tue, 22 July 2014 11:14
SQL> UPDATE test_table
  2     SET code = ltrim(code, '0')
  3   WHERE substr(code, 0, 1) = '0';

2 rows updated.

SQL>
SQL> SELECT * FROM test_table;

CO
--

HQ
4
BM
8
26

6 rows selected.


This does not work for a code '0A' which should remain as '0A', so the regexp.
And, indeed, I had the same idea than Littlefoot to remove the leading 0 of a number. Smile

Re: To_number on only number fields [message #619410 is a reply to message #619406] Tue, 22 July 2014 05:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 22 July 2014 15:22

This does not work for a code '0A' which should remain as '0A', so the regexp.


Good point, I did not think about such values as combination of digits and alphabets. By adding another filter it should be taken care of.

In this test case, I will insert two more values as '0A' and 'A0', the expectation is that my query should still only update 2 rows, rest should remain untouched.

SQL> INSERT INTO test_table VALUES('0A');

1 row created.

SQL> INSERT INTO test_table VALUES('A0');

1 row created.

SQL>
SQL> SELECT * FROM test_table;

CO
--
0A
A0

HQ
04
BM
08
26

8 rows selected.

SQL>
SQL> UPDATE test_table
  2     SET code = LTRIM(CODE,'0')
  3   WHERE
  4   substr(code, 0, 1) = '0' AND regexp_instr(LTRIM(CODE,'0') , '[[:alpha:]]')<>1;

2 rows updated.

SQL>
SQL> SELECT * FROM test_table;

CO
--
0A
A0

HQ
4
BM
8
26

8 rows selected.


Smile
Re: To_number on only number fields [message #619412 is a reply to message #619410] Tue, 22 July 2014 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
substr(code, 0, 1) = '0' AND regexp_instr(LTRIM(CODE,'0') , '[[:alpha:]]')<>1;


Not easy to understand, why not combine the 2 previous conditions?
substr(code, 1, 1) = '0' AND regexp_like(code,'^[0-9]*$')

or, combining the expressions:
regexp_like(code,'0\d')


(Note: strings start at index 1 in Oracle, not 0.)

[Edit: add code tags, modify regexp as code is max 2 characters]

[Updated on: Tue, 22 July 2014 05:34]

Report message to a moderator

Re: To_number on only number fields [message #619415 is a reply to message #619412] Tue, 22 July 2014 05:41 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 22 July 2014 16:00
or, combining the expressions:
regexp_like(code,'0\d')

/forum/fa/2115/0/

Quote:

(Note: strings start at index 1 in Oracle, not 0.)

Thanks for pointing it out yet again, I have this bad habit since I first used substr Sad . I wish Oracle didn't allow my bad habit.

[Updated on: Tue, 22 July 2014 05:42]

Report message to a moderator

Re: To_number on only number fields [message #619426 is a reply to message #619415] Tue, 22 July 2014 06:59 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oh boy, using canons to shoot birds. Why regular expressions? On top of being slower by nature it also prevents optimizer from using index:

SQL> create index test_table_idx1 on test_table(code);

Index created.

SQL> explain plan for
  2  UPDATE test_table
  3    SET code = LTRIM(CODE,'0')
  4    WHERE substr(code, 0, 1) = '0' AND regexp_instr(LTRIM(CODE,'0') , '[[:alpha:]]')<>1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 385506193

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |            |     2 |     6 |     3   (0)| 00:00:01 |
|   1 |  UPDATE            | TEST_TABLE |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_TABLE |     2 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter(SUBSTR("CODE",0,1)='0' AND  REGEXP_INSTR
              (LTRIM("CODE",'0'),'[[:alpha:]]')<>1)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

SQL> UPDATE test_table
  2    SET code = LTRIM(CODE,'0')
  3    WHERE regexp_like(code,'0\d');

2 rows updated.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 385506193

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |            |     2 |     6 |     3   (0)| 00:00:01 |
|   1 |  UPDATE            | TEST_TABLE |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_TABLE |     2 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter(SUBSTR("CODE",0,1)='0' AND  REGEXP_INSTR
              (LTRIM("CODE",'0'),'[[:alpha:]]')<>1)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

SQL> explain plan for
  2  UPDATE test_table
  3    SET code = LTRIM(CODE,'0')
  4    WHERE code like '0%' AND LTRIM(CODE,'0123456789') IS NULL;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 2894885330

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |                 |     1 |     3 |     0   (0)| 00:00:01 |
|   1 |  UPDATE           | TEST_TABLE      |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_TABLE_IDX1 |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("CODE" LIKE '0%')
       filter("CODE" LIKE '0%' AND LTRIM("CODE",'0123456789') IS NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

SQL> 


SY.
Previous Topic: SQL
Next Topic: Nested Aggregate Functions Group By Error
Goto Forum:
  


Current Time: Thu Mar 28 07:51:21 CDT 2024