Archives of the TeradataForum

Message Posted: Mon, 01 Apr 2002 @ 20:35:26 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Numeric Value Validation

From:		Dieter N�th

does anybody know a function in V2R4.0.3 to validate if a returnstring contains a numeric value? I want to cast a substring of a char field in the case it is numeric....

It's easier if you change the logic from LIKE to POSITION. And it's always handy to have a table with sequential numbers, i called it sequences, so i don't have to mess around with sys_calendar. The following code works for decimals and negative values.

     sel
     textfield, cast(textfield as float)
     From
     test cross join
     (select i from sequence where i between 1 and 20) tmp
     where
     i <= char_length(trim(textfield))
     and
     position (substring(trim(textfield) From i For 1) in '+-0123456789.') > 0
     group by 1
     having count(*) = max(char_length(trim(textfield)))
     and trim(textfield) not like '%.%.%'
     ;

some more inserts...

     insert into test values ('1234..437');
     insert into test values ('+74672');
     insert into test values ('-74672');
     insert into test values ('1234.437');

Less sophisticated, but probably the fastest way:

     sel textfield, cast(textfield as float)
     From test
     Where position (substring(trim(textfield) From 1 For 1) in '+-0123456789.')> 0
     and position (substring(trim(textfield) From 2 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 3 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 4 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 5 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 6 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 7 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 8 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 9 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 10 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 11 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 12 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 13 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 14 For 1) in '0123456789.') > 0
     and position (substring(trim(textfield) From 15 For 1) in '0123456789.') > 0
     ...
     and trim(textfield) not like '%.%.%';

I remember that we added an enhancement request for a check function that is able to check if characters are valid dates. Maybe this function can developed to be more general. Does anybody know if NCR is really working on the implementation?

If not we'll have to wait until R5.1 and User Defined Functions ;-))

Have fun!

I do...

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference