Home » SQL & PL/SQL » SQL & PL/SQL » extracting data from the field
extracting data from the field [message #258209] Fri, 10 August 2007 10:15 Go to next message
itzikma
Messages: 6
Registered: August 2007
Junior Member
Hi,

I am not an expert and need your help.

I have a field that i want to extract:

once it is: "abcdefg 123456789."
or : "hello abcdefgpppppp 12345."
or : "need your help 645213."


I would like to get from the field : 123456789 or 12345 or 645213 (without the dot (.))

[Updated on: Fri, 10 August 2007 10:21]

Report message to a moderator

Re: extracting data from the field [message #258215 is a reply to message #258209] Fri, 10 August 2007 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select translate(field,'0abcdefghijklmnopqrstuvwxyz. ','0') from mytable;

Regards
Michel
icon14.gif  Re: extracting data from the field [message #258233 is a reply to message #258215] Fri, 10 August 2007 11:22 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

IF you are using 10g then you can use the following query also:
SELECT  REGEXP_SUBSTR(<YOURSTRING>,'+[[:digit:]]+') FROM DUAL;


Cheers
Sanka

[Updated on: Fri, 10 August 2007 11:22]

Report message to a moderator

Re: extracting data from the field [message #258274 is a reply to message #258209] Fri, 10 August 2007 14:08 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
What happens if the description before the final field contains a numeric. Try the following

select substr(my_field,instr(my_field,' ',-1) + 1,length(my_field)- instr(my_field,' ',-1) - 1) from my_table;

[Updated on: Fri, 10 August 2007 14:10]

Report message to a moderator

icon10.gif  Re: extracting data from the field [message #258276 is a reply to message #258233] Fri, 10 August 2007 14:21 Go to previous messageGo to next message
itzikma
Messages: 6
Registered: August 2007
Junior Member
Hey,

Many 10x.

I'll try .

You r great


Itzik Mad
Re: extracting data from the field [message #258491 is a reply to message #258276] Sun, 12 August 2007 13:52 Go to previous message
itzikma
Messages: 6
Registered: August 2007
Junior Member
Hello,

I would like to thank you for help.

It works and solved a lot for me.

again 10x,

Itzik
Previous Topic: need help urgent
Next Topic: count as a part of the query
Goto Forum:
  


Current Time: Tue Dec 06 10:01:48 CST 2016

Total time taken to generate the page: 0.19087 seconds