Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 26 Sep 2005 @ 12:16:47 GMT


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


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



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023