CREATE TABLE #ExternalCodeTemp (RowID int IDENTITY(1, 1), HierarchicalStructureId varchar(18) NOT NULL)

INSERT INTO #ExternalCodeTemp
SELECT HierarchicalStructureId FROM ra.HierarchicalStructure hie WHERE hie.HierarchyLevel='UNB' AND ExternalCode IS NULL

DECLARE @HierarchicalStructureId varchar(18)
DECLARE @NumberRecords int = @@ROWCOUNT
DECLARE @RowCount int = 1

WHILE @RowCount <= @NumberRecords
BEGIN
 SELECT @HierarchicalStructureId = HierarchicalStructureId FROM #ExternalCodeTemp WHERE RowID = @RowCount

 SELECT * INTO #ExternalCodeTempAux
 FROM SplitString(@HierarchicalStructureId, '.')
 WHERE zeroBasedOccurance>0

 DECLARE @SQLStr VARCHAR(18)
 SET @SQLStr=''
 SELECT @SQLStr=@SQLStr+'.'+[a].[Column] FROM
 (SELECT CONVERT(VARCHAR(2),s)as[Column] FROM #ExternalCodeTempAux --ORDER BY zeroBasedOccurance
  )AS a
 SET @SQLStr=RIGHT(@SQLStr,len(@SQLStr)-1)

 UPDATE ra.HierarchicalStructure SET ExternalCode = @SQLStr WHERE HierarchicalStructureId = @HierarchicalStructureId

 DROP table #ExternalCodeTempAux

 SET @RowCount = @RowCount + 1
END

DROP TABLE #ExternalCodeTemp