oops i missed that part;

doing something like SELECT RIGHT('0000' + SomeString,3) will give you stuff like 002 with preceeding zeros;

then it's the find and replace you were after.

i think this slices up teh peices, so you can decide how you wnat them reassembled:

with myCTE (ID)

AS

(

SELECT 'AG001/1/P1' UNION ALL

SELECT 'AG002/1/P1' UNION ALL

SELECT 'AG003/1/P1' UNION ALL

SELECT 'AG004/1/P1' UNION ALL

SELECT 'AG006/1/P1' UNION ALL

SELECT 'AG007/1/PO1' UNION ALL

SELECT 'AG008/1/P01' UNION ALL

SELECT 'AG009/1/P01' UNION ALL

SELECT 'AG010/1/P01' UNION ALL

SELECT 'AL001/1/P1' UNION ALL

SELECT 'AL002/1/P1' UNION ALL

SELECT 'AL003/1/P1' UNION ALL

SELECT 'CC013/1/P01')

SELECT

PARSENAME(REPLACE(ID,'/','.'),3) As Tier1,

PARSENAME(REPLACE(ID,'/','.'),2) As Tier2,

'L' + RIGHT('000' + REPLACE(REPLACE(PARSENAME(REPLACE(ID,'/','.'),1),'P','') ,'L',''),3) As Tier3

FROM myCTE