| Parsing Help [message #198585] |
Tue, 17 October 2006 17:54  |
lowcash75
Messages: 67
Registered: February 2006
|
Member |
|
|
I have a table with a text field and I need to convert it to a number field. I know it can't be done easily. But what I'm trying to do is if the the record has a number then take that and insert that into the number field, otherwise assign it 0.
Example
ID Text_Field Number_Field
1 24h 24
2 2 2
3 ? 0
4 50-65 50
5 ~5474 5474
6 Don't know 0
Any help on this would be appreciated!
create table mig_table (id number, text_field varchar2(20), number_field number);
insert into mig_table(id, text_field, number_field) values (1, '24h',24);
insert into mig_table(id, text_field, number_field) values (2, '2',2);
insert into mig_table(id, text_field, number_field) values (3, '?',0);
insert into mig_table(id, text_field, number_field) values (4, '50-65',50);
insert into mig_table(id, text_field, number_field) values (5, '~5474',5474);
insert into mig_table(id, text_field, number_field) values (6, 'Don''t know',0);
Thanks
[Updated on: Tue, 17 October 2006 18:17] Report message to a moderator |
|
|
|
| Re: Parsing Help [message #198641 is a reply to message #198585] |
Wed, 18 October 2006 01:53  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'd do something like
CREATE OR REPLACE function convert_num (p_string in varchar2) return number as
begin
return to_number(p_string);
exception
when others then return 0;
end convert_num;
update mig_table set number_field = convert_num(text_field); |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
