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 |
|
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 #619395 is a reply to message #619387] |
Tue, 22 July 2014 04:10 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Littlefoot wrote on Tue, 22 July 2014 14:07SQL> 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 #619398 is a reply to message #619396] |
Tue, 22 July 2014 04:24 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Littlefoot wrote on Tue, 22 July 2014 14:43I 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 |
|
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 #619406 is a reply to message #619397] |
Tue, 22 July 2014 04:52 |
|
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.
|
|
|
Re: To_number on only number fields [message #619410 is a reply to message #619406] |
Tue, 22 July 2014 05:23 |
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.
|
|
|
Re: To_number on only number fields [message #619412 is a reply to message #619410] |
Tue, 22 July 2014 05:30 |
|
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:
(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 #619426 is a reply to message #619415] |
Tue, 22 July 2014 06:59 |
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.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 07:51:21 CDT 2024
|