how to check character in field

newdeveloper india
newdeveloper india used Ask the Experts™
on
I have table having maximum number values in character datatype. I want to find those values where no character is defined and replace it with null. eg. table having field receiptno which is character field. I have minimum records having values like a12455,c24353,21243,c34345.
I want to replace these values with null or 0 and change this field with number datatype.

How can I write case statement for this field in simple sql query?????
Please help.

receiptno
a12345
12234
122434
23255
d34355

I want output like

0
12345
122434
23255
0
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
I do this using this function:
create or replace FUNCTION GetNumber(txt_IN IN varchar2)
RETURN NUMBER
IS
	res NUMBER (30);
BEGIN

BEGIN
  res := TO_NUMBER(txt_IN);
EXCEPTION
  WHEN VALUE_ERROR THEN res:=0;
END;
RETURN (res);
END GetNumber;

 

Open in new window

awking00Information Technology Specialist
Commented:
select case when regexp_like(receiptno,'[a-z]') then '0' else receiptno end receiptno
from yourtable;

If you want numbers to be returned, then just add to_number -
select to_number(case when regexp_like(receiptno,'[a-z]') then '0' else receiptno end) receiptno
from yourtable;
Most Valuable Expert 2011
Top Expert 2012
Commented:
rather than looking for specific "bad" characters to turn into 0, it might be more effective to look for anything that isn't a "good" character and then turn the string into 0

So, assuming 0-9 are your "good" characters, that might look something like this


select case when regexp_like(receiptno,'[^0-9]') then 0 else receiptno end
from yourtable

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial