Archives of the TeradataForum
Message Posted: Mon, 15 Mar 2004 @ 12:29:24 GMT
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 .....
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|