Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 15 Mar 2004 @ 12:29:24 GMT

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

Subj:   Re: Manipulate string spaces
From:   Barbara George

As I read this, the Index_n columns are the positions of the spaces, all spaces including those that are next to each other.

To achieve this, take the 'trim' function out of the 'index (trim(substr(Str_Address, index_......' .

This is removing all the leading spaces from the shortened name without identifying their positions.

Rather put the trim in the concatenate statement which will remove any leading or trailing blanks immediately (trim(LN_1_STR_ADD) || ' ' || trim(LN_2_STR_ADD))

Also, it may be more useful to look for ' ' (2 spaces) rather than 1, which is presumably legitimate.

Select DISTINCT (trim(LN_1_STR_ADD) || ' ' || trim(LN_2_STR_ADD)) AS
      index (Str_Address,' ') as Index_1,
      (character_length (str_address) - index_1) as rem_add_1,
      index ((substr(Str_Address, index_1+1)), ' ') + Index_1 As Index_2


  <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: 23 Jun 2019