?
Solved

how to check character in field

Posted on 2016-09-06
4
Medium Priority
?
60 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 672 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 664 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 664 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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

719 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