numeric values 2

[wouter]

i want to select all numeric values from a table, but how do i do this, there's also strings in this column. I can't find any functions that do this. I want to select them so i can delete them.

select * from usr where usrc = [a number];

This is what i want. Can anyone help?

regards Wouter Wouter
zure_zult@hotmail.com
To me, boxing is like a ballet, except there's no music, no choreography, and the dancers hit each other.

 

[fluteplr]

This tends to be really database dependant. What db are you using?

For example in sql server it would be

select * from usr where isnumeric(usrc) = 1

 

[barbarul2001]

in oracle is a litle bit complicated:

--first a function:
CREATE OR REPLACE FUNCTION CHECKN(word VARCHAR2) RETURN NUMBER IS
i NUMBER;
j NUMBER:=0;
BEGIN
FOR i IN 1..LENGTH(word) LOOP
SELECT j+COUNT(*) INTO j FROM dual
WHERE SUBSTR(word,i,1) NOT IN ('0','1','2','3','4','5','6','7','8','9');
IF j>0 THEN RETURN(j);
END IF;
END LOOP;
RETURN j;
END;

--then I use the function in sql


delete from usr
where nvl(length(usrc),0)>0
and checn(usrc) = 0
senior rdbms specialist

 

[karluk]

Actually, this issue was discussed recently in thread220-358547. A simple solution in Oracle is

select * from usr
where usrc is not null
and ltrim(usrc,'0123456789') is null;

Unfortunately, I don't think this solution is portable to other databases. The ltrim function is commonly available, but trimming multiple characters in one function call isn't always allowed.

Perhaps nested ltrims would be more portable. The select would be

select * from usr
where usrc is not null
and ltrim(ltrim(ltrim(ltrim(ltrim(ltrim(ltrim(ltrim(ltrim(ltrim(usrc,'0'),'1'),'2'),'3'),'4'),'5'),'6'),'7'),'8'),'9') is null

 

[carp]

In Oracle:
DELETE FROM usr
WHERE trim(translate(usrc,'0123456789',' ')) IS NULL;

I remember Karluk having a better way of doing this, but I can't find it.

 

[wouter]

thnx for all your response. I'm using mysql. I'll also check out the other thread. Thnx Wouter
zure_zult@hotmail.com
To me, boxing is like a ballet, except there's no music, no choreography, and the dancers hit each other.

 

[fluteplr]

Not to start a DB war here but,

From what I have seen of MySQL it is missing so many pieces of what I would consider an ANSI compliant database that you would be better of posting your question in the MYSQL forum.

For MySQL fans, please note: I make exactly the same comment about Access users asking questions here.

 

[wouter]

OK, thank you, i didn't know this. I'll try that. Wouter
zure_zult@hotmail.com
To me, boxing is like a ballet, except there's no music, no choreography, and the dancers hit each other.