| Find Numbers [message #1829] |
Mon, 03 June 2002 03:26  |
Tina
Messages: 38
Registered: November 2001
|
Member |
|
|
Oracle 8.6.1
Please can you advise. I'm trying to show only an eight digits in a field VARCHAR2(30) field. This field can hold these eight digits at the end middle or beginning of text. The eight digit can also be preceded by 00.
Example
Field
0078965432
24579893
END 23794832
IN 0078434521 MIDDLE
What I need from this field
78965432
24579893
23794832
78434521
Any help would be much appreciated.
Many Thanks
|
|
|
|
| Re: Find Numbers [message #1844 is a reply to message #1829] |
Tue, 04 June 2002 06:34  |
Thiyagarajan
Messages: 9
Registered: October 2000
|
Junior Member |
|
|
Hai,
This code might work. But this will work only if the data is of similar pattern u have given in the example.
select to_number(substr(column_name, instr(column_name,' ')+1, decode(instr(column_name,' ',instr(column_name,' ')+1),0,length(<<column_name>>),instr(<<column_name>>,' ',instr(column_name,' ')+1))-instr(column_name,' '))) from table_name
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
