Searching SQL-Funktion ...

[Dirk Deimeke]

Hello,

we have a database with one column CHAR(10), which contains numeric data
(10 digits).
(That was not my idea).

Now, I have to find out all rows, which do not have numeric data in this
column.

Unfortunately, I don't know the corresponding function for the
followeing query:

select * from table where function(column)>999999999;

Any suggestions?

Thanks in advance for any support.

Dirk

[Knut Stolze]

Dirk Deimeke wrote:

Hello,

we have a database with one column CHAR(10), which contains numeric data
(10 digits).
(That was not my idea).

Now, I have to find out all rows, which do not have numeric data in this
column.

Unfortunately, I don't know the corresponding function for the
followeing query:

select * from table where function(column)>999999999;

Any suggestions?

Besides the obvious use of regular expressions
(http://www.ibm.com/developerworks/db...301stolze.html)

you could do something like this:

SELECT *
FROM yourTable
WHERE SUBSTR(column, 1, 1) NOT BETWEEN '0' AND '9' AND
SUBSTR(column, 2, 1) NOT BETWEEN '0' AND '9' AND
...
SUBSTR(column, 10, 1) NOT BETWEEN '0' AND '9'

You can also wrap the predicates in the WHERE clause into a function if you
like. And if you want to generalize this to be able to deal with strings
of different lengths, have a look here for some ideas:
http://www.ibm.com/developerworks/db...301stolze.html

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Dirk Deimeke]

Knut Stolze:

Besides the obvious use of regular expressions (http://www.ibm.com/developerworks/db...301stolze.html)

That is pretty cool. Thank you for the link.
you could do something like this:
SELECT *
FROM yourTable
WHERE SUBSTR(column, 1, 1) NOT BETWEEN '0' AND '9' AND
SUBSTR(column, 2, 1) NOT BETWEEN '0' AND '9' AND
...
SUBSTR(column, 10, 1) NOT BETWEEN '0' AND '9'

I wanted to avoid that ...

It could help to react on the error message you get by using:
select * from ourTable where int(column)>0;

Thank you for your support.

Kind regards.

Dirk

[Tokunaga T.]

Here is a sample UDF ISNUMERIC/ISNUM.

--
-- DB2 UDB UDF(User-Defined Function) Samples for Migration
--
-- Created: 2002/07/18
-- Updated: 2004/02/08
--
-- Name of UDF: ISNUMERIC (Source VARCHAR(40))
-- ISNUM (Source VARCHAR(40))
--
-- Used UDF: None
--
-- Description: Returns 1 when the input string is a valid integer or
decimal representation,
-- otherwise returns O.
--
-- Validity test cases.
-- Test 1 : All characters are '0123456789.-+' or
blank.
-- Test 2 : No other characters exists left of '-' or
'+' except blank(s).
-- Test 3-1: Number of '-' or '+' should be 0 or 1.
-- Test 3-2: Number of '.' should be 0 or 1.
-- Test 4 : No blanks are allowed between characters
except after leading '-' or '+'.
-- Test 5 : It should have at least one digit
character.
--
-- Author: TOKUNAGA, Takashi
--

CREATE FUNCTION ISNUMERIC (Source VARCHAR(40))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
CASE
WHEN translate(source,'','0123456789.-+') <> ''
THEN 0
WHEN posstr(ltrim(source),'-') > 1
OR posstr(ltrim(source),'+') > 1
THEN 0
WHEN length(rtrim(ltrim(translate(source,'','0123456789 .')))) > 1
OR length(rtrim(ltrim(translate(source,'','0123456789-+')))) > 1
THEN 0
WHEN posstr(ltrim(rtrim(translate(source,'','-+'))),' ') > 0
THEN 0
WHEN translate(source,'','.-+') = ''
THEN 0
ELSE 1
END
!

--------------------------------------------------------------------------
CREATE FUNCTION ISNUM (Source VARCHAR(40))
RETURNS INTEGER
SOURCE ISNUMERIC
!

[Dirk Deimeke]

Tokunaga T.:

Here is a sample UDF ISNUMERIC/ISNUM.

Thanks a lot for your support.

Can you please explain me, how do i get this funktion into db2?

db2 -td! -vf yourfunction.sql

Is that the right way?

Kind regards

Dirk

[Tokunaga T.]

> Can you please explain me, how do i get this funktion into db2?

db2 -td! -vf yourfunction.sql

I did not try this.
Usually, I copy and paste on Command Editor and execute it.

[Dirk Deimeke]

Tokunaga T.:

I did not try this.
Usually, I copy and paste on Command Editor and execute it.

That is too easy ;-)

Thanks again.

Dirk

[Dirk Deimeke]

Dirk Deimeke:

I did not try this.
Usually, I copy and paste on Command Editor and execute it.

Your function works great.

Thanks a lot.

Kind regards,

Dirk Deimeke

[raid3n]

Dirk Deimeke:
[color=blue][color=green]
>> I did not try this.
>> Usually, I copy and paste on Command Editor and execute it.[/color][/color]

Your function works great.

Thanks a lot.

Kind regards,

Dirk Deimeke

Hello,

I create this table in DB2 to experiment the function ISNUMERIC:

create table tab (
prova VARCHAR(40) CHECK (ISNUMERIC(prova) = 1)
);

but i have some errors: pratically the functions not exists

I copy the funcion in the editor and all is ok. But i can not use it.
Excuse my bad english

Regards
Andrea