Archives of the TeradataForum
Message Posted: Mon, 26 Sep 2005 @ 12:16:47 GMT
Subj: | | Re: Query to get the last space in a string. |
|
From: | | Dieter Noeth |
ulrich arndt wrote:
| two SQL's which are doing the job - one direct with difficult performance on my demo version (7 minutes) - it's more an academic solution -
| |
With some minor modifications (see below) it's fast (but not as fast as
the second), but this one can be extended to split the whole column into
separate words in rows.
| and one with is generated with a case statement which comes back within some seconds. | |
As long as you hide that ugly generated code within a batch script ;-) And if it's a huge varchar you'll hit the "1MB maximum sql size"
limit...
> -- one SQL with product join to get right id's
> -- it is generic but might not show best performance espacially for
big
> varchar fields
> select t.upi_id,
> coalesce(c.id,0) as last_position,
> substring(char_field from last_position + 1) as last_field,
> char_field
> from char_test t
> left outer join
> (select ci.calendar_date-current_date as id
> from sys_calendar.calendar ci,
> (select max(characters(char_field)) as max_len from
> char_test) m
> where id between 1 and m.max_len
> ) c
> on index(t.char_field,' ') > 0
> and last_position between index(t.char_field,' ') and
> characters(t.char_field)
> where (last_position is null
> or last_position between index(t.char_field,' ') and
> characters(t.char_field)
> )
> qualify max(case when substring(char_field from last_position for 1) ='
> ' then last_position else null end) over (partition by upi_id) =
> last_position or last_position = 0
> order by upi_id
> ;
If you apply a filtering condition early the number of spool rows will be fairly reduced and the qualify will be simpler:
select
t.upi_id,
coalesce(c.id,0) as last_position,
substring(char_field from last_position + 1) as last_field
from char_test t
left outer join
(select ci.calendar_date-current_date as id
from sys_calendar.calendar ci,
(select max(characters(char_field)) as max_len
from char_test) m
where id between 1 and m.max_len
) c
on id <= char_length(t.char_field)
and substring(char_field from id for 1 ) = ' '
qualify
max(last_position) over (partition by upi_id) = last_position
;
and instead of max a rank might be faster:
qualify
rank() over (partition by upi_id order by last_position desc) = 1
Thus you could get the Nth occurence also...
Dieter
|