Archives of the TeradataForum

Message Posted: Tue, 02 Apr 2002 @ 06:51:08 GMT

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

I totally agree - position is nicer. But we have to add two more conditions on + and - (both assume that + or - have to be on the first position of the string (after applying the trim function)).

Some more inserts:

insert into test values ('+74+672');
insert into test values ('-74+672');
insert into test values ('-74-672');
insert into test values ('1234.4+37');
insert into test values ('12-34.437');
insert into test values ('-74.672');

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 '%.%.%'
and trim(textfield) not like '_%+%'
and trim(textfield) not like '_%-%'

I disagree in waiting for User Defined Functions - Teradata already checks for if the strings can be converted into what ever - so why is it not possible to implement an function which return the result of the check as true or false? Why should every Teradata implementation program the checks for every data type and format on there own? Would it not be better if NCR does the job once?

Ulrich

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