• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Simple sql question

Hi
I have a table in which a column is declared to be of type varchar.
Almost all the values for this column are numbers like 100, 120, 140, etc but some of the values are alpha characters like 'hundred', 'one twenty five' etc.

Now I want to delete all these alpha entries from the table.
Is there a simple sql query which can do this ?
thanks
0
AInamdar
Asked:
AInamdar
1 Solution
 
ddandekarCommented:
Assuming that the first character in varchar column c1 is numeric for the rows which you want to retain then following will delete the rows with nonnumeric first characters
delete from t1
where  substr(c1,1,1) not in
 ( '0','1','2','3','4','5','6','7','8','9')
/

This will have to be repeated for 2nd character , 3rd character etc.
0
 
mnicorasCommented:
Hi,

not so sim[ple.

solution 1:

create or replace function is_char( iVal varchar2 ) return number is
mVal number;
mRet NUMBER;
begin
  begin
    mVal := iVal;
    mRet := 1;
  exception
    when others then
      mRet := 0;
  end;
return mRet;
end;

delete from <my_table> where is_char(<my_column>)=0;

solution 2:

delete from <my_table>
where
  instr(lower(<my_column>),'a')+
  instr(lower(<my_column>),'b')+
  instr(lower(<my_column>),'c')+
  instr(lower(<my_column>),'d')+
  instr(lower(<my_column>),'e')+
...
  instr(lower(<my_column>),'z') > 0
;


best regards,
Marius Nicoras
0
 
justvineetCommented:
The following query will delete all the rows which have an alpha in its value.

delete from t1 where
ascii(substr(c1,1,1)) not between 48 and 57 OR
ascii(substr(c1,2,1)) not between 48 and 57 OR
ascii(substr(c1,3,1)) not between 48 and 57 OR
......
......
ascii(substr(c1,size_of_c1-1,1)) not between 48 and 57 OR
ascii(substr(c1,size_of_c1,1)) not between 48 and 57
/

Regards,
Vineet.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rkogelheCommented:
update mytable
set mynum = null
where
  translate(mynum,'X0123456789','X') is not null;

or

delete mytable
where
  translate(mynum,'X0123456789','X') is not null;
0
 
AInamdarAuthor Commented:
Query from rkogelhe  was short and worked for me .
thanks.
0
 
patelgokulCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
accept rkogelhe's comment as an answer
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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