Home » SQL & PL/SQL » SQL & PL/SQL » Parsing Help
Parsing Help [message #198585] Tue, 17 October 2006 17:54 Go to next message
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 Go to previous messageGo to next message
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);
Re: Parsing Help [message #198789 is a reply to message #198641] Wed, 18 October 2006 09:39 Go to previous message
lowcash75
Messages: 67
Registered: February 2006
Member
Thanks for your help!
Previous Topic: Is there any subsitude of UNION and UNION ALL
Next Topic: how to determine a lock
Goto Forum:
  


Current Time: Sun Dec 04 10:55:24 CST 2016

Total time taken to generate the page: 0.08780 seconds