Archives of the TeradataForum

Message Posted: Fri, 21 Sep 2007 @ 17:56:07 GMT

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

You may want to use some kind of Name cleansing software. Sql can get really complicated. However, if this is the format per your example, try some thing like this. This will give you some idea. Hope this helps.

-- Thanks

     sel
     r_name
     ,trim(Substring (r_name
     >From        1 For Index (r_name, ' ' ) -1)) As First_nm
     ,Trim(Substring(r_name
     >From        char_length (First_nm)+1))  As M_temp
     ,trim(
                     Case
                             When        Index (M_temp, ' ' ) >0 Then
     (Substring (M_temp
     >From        1 For Index (M_temp, ' ' ) -1))
                             Else        ' '
                     End )        As MI
     ,trim(Substring(r_name
     >From        char_length(Trim(First_Nm) || MI) + (
                     Case
                             When        MI=' ' Then 1
                             Else        2 end) ) ) As Last_nm
     ,trim(Last_Nm || ','|| First_nm || ' ' || MI ) as Desired_Output
     >From        vol
     ;

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