Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

how to check character in field

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
0
newdeveloper india
Asked:
newdeveloper india
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
awking00Commented:
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;
0
 
sdstuberCommented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now