Remove Last part

Try:

DECLARE @City TABLE ( CityName varchar(100));
INSERT @City VALUES
('1. Egypt'),
('1.1. Cairo'),
('1.1.21. New Cairo'),
('1.1.21.5. Alexandria')
-- SELECT STUFF('abcdef', 2, 3, 'ijklmn');

SELECT 
CityName, RenumberedCityName=
LTRIM(STUFF(REVERSE(SUBSTRING(SUBSTRING(REVERSE(CityName), CHARINDEX('.', REVERSE(CityName),1)+1,LEN(CityName))+'.',
CHARINDEX('.',SUBSTRING(REVERSE(CityName), CHARINDEX('.', REVERSE(CityName),1)+1,LEN(CityName))+'.'),len(CityName))),1,1,'')+SPACE(1)+
REVERSE(LTRIM(RTRIM(LEFT (REVERSE(CityName), CHARINDEX('.', REVERSE(CityName),1)-1)))))
FROM @City WHERE CHARINDEX('.',CityName) > 0;
/*
CityName        RenumberedCityName
1. Egypt        Egypt
1.1. Cairo      1. Cairo
1.1.21. New Cairo       1.1. New Cairo
1.1.21.5. Alexandria    1.1.21. Alexandria
*/

-- USING hierarchyid method
SELECT 
CityName, RenumberedCityName=
LTRIM(REPLACE(STUFF(CONVERT(hierarchyid,'/'+REPLACE(LEFT(CityName, CHARINDEX(' ', CityName)-1),'.','/')).GetAncestor(1).ToString(),1,1,''),'/','.')
+SPACE(1)+REVERSE(LTRIM(RTRIM(LEFT (REVERSE(CityName), CHARINDEX('.', REVERSE(CityName),1)-1)))))
FROM @City WHERE CHARINDEX('.',CityName) > 0;
/*
CityName        RenumberedCityName
1. Egypt        Egypt
1.1. Cairo      1. Cairo
1.1.21. New Cairo       1.1. New Cairo
1.1.21.5. Alexandria    1.1.21. Alexandria
*/

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming

New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012