Home » SQL & PL/SQL » SQL & PL/SQL » How to find the occurance of non numeric characters in number column.
How to find the occurance of non numeric characters in number column. [message #411623] Mon, 06 July 2009 02:00 Go to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi,
I need to check whether a column contains non numeric characters using like operator.
I have searched the forum for the result but i didn't find exact solution..

Hoping for quick reply.
Thanks in advance,
Ravi.
Re: How to find the occurance of non numeric characters in number column. [message #411626 is a reply to message #411623] Mon, 06 July 2009 02:27 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Is there any specific reason that you want to try using LIKE only,

Post DDL/DML and test case
Re: How to find the occurance of non numeric characters in number column. [message #411629 is a reply to message #411626] Mon, 06 July 2009 02:35 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
There is no specific reason.
Like is a simple expression thats why i want to try that.
Actually i need to check this condition in filter of OWB map.
Regards,
Ravi.
Re: How to find the occurance of non numeric characters in number column. [message #411631 is a reply to message #411623] Mon, 06 July 2009 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You will not find it with LIKE but you will find many other solutions.

Regards
Michel
Re: How to find the occurance of non numeric characters in number column. [message #411632 is a reply to message #411623] Mon, 06 July 2009 02:37 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

This is just an example to take the Numbers out of a String,

Hope this Hint will help you in any regard

select REPLACE (TRANSLATE ('A1234zwe34', '1234567890', '0'), '0', '') from dual


I think this following link will help you better,

http://www.oracle.com/technology/oramag/code/tips2004/120604.html

[Updated on: Mon, 06 July 2009 02:40]

Report message to a moderator

Re: How to find the occurance of non numeric characters in number column. [message #411635 is a reply to message #411631] Mon, 06 July 2009 02:48 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi Michel,
Provide any solution.
Regards,
Ravi.
Re: How to find the occurance of non numeric characters in number column. [message #411636 is a reply to message #411635] Mon, 06 July 2009 02:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why can't you use ashoka_bl's solution?
Re: How to find the occurance of non numeric characters in number column. [message #411637 is a reply to message #411623] Mon, 06 July 2009 02:53 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

OP doesn't want to try i guess, If he click the link and understand...There is a ready made solution available..

Or OP doesn't trust newbie like me ?? is it Smile
Re: How to find the occurance of non numeric characters in number column. [message #411638 is a reply to message #411635] Mon, 06 July 2009 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
LIKE won't do it, but REGEXP_LIKE will.
Re: How to find the occurance of non numeric characters in number column. [message #411639 is a reply to message #411637] Mon, 06 July 2009 03:17 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi,
The solution provided by ashoka is working.
Thank you very much ashoka.

Thanks and Regards,
Ravi.
Re: How to find the occurance of non numeric characters in number column. [message #411640 is a reply to message #411639] Mon, 06 July 2009 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
adusur wrote on Mon, 06 July 2009 10:17
Hi,
The solution provided by ashoka is working.

Why? It doesn't use LIKE!

Regards
Michel

Re: How to find the occurance of non numeric characters in number column. [message #411644 is a reply to message #411623] Mon, 06 July 2009 03:26 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Ravi,

Post the solution here so that it can be used by other people as well.

You can use it in the "Expression Transformation " i believe in OWB
Re: How to find the occurance of non numeric characters in number column. [message #411654 is a reply to message #411644] Mon, 06 July 2009 04:19 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi,
In filter condition i am using the statement provided by you.
And depending on the length of returned string i am deciding whether nonnumeric cheractres are present or not.
Regards,
Ravi.
Re: How to find the occurance of non numeric characters in number column. [message #411656 is a reply to message #411654] Mon, 06 July 2009 04:35 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
adusur wrote on Mon, 06 July 2009 11:19
Hi,
In filter condition i am using the statement provided by you.
And depending on the length of returned string i am deciding whether nonnumeric cheractres are present or not.
Regards,
Ravi.

Why don't you provide the query itself?

Regards
Michel

Previous Topic: Data Not insert into table from PROCEDURE
Next Topic: sub query logic
Goto Forum:
  


Current Time: Sun Dec 11 02:28:19 CST 2016

Total time taken to generate the page: 0.10384 seconds