Archives of the TeradataForum

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

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

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
STR_ADDRESS,
      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
.....

Barbara

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