Solved

how to check character in field

Posted on 2016-09-06
4
56 Views
Last Modified: 2016-10-23
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
Comment
Question by:newdeveloper india
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 168 total points (awarded by participants)
ID: 41785884
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 166 total points (awarded by participants)
ID: 41786324
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 166 total points (awarded by participants)
ID: 41786373
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question