SQL Server - Separar string con delimitador

select REPLACE('CERRITO/866/PB/1010', '/', ' ')

declare @MyString varchar(100) = 'CERRITO/866/PB/1010'

declare @CALLE varchar(100)

declare @NUMERO varchar(100)

declare @PISO varchar(100)

declare @COD_POSTAL varchar(100)

SELECT @CALLE = SUBSTRING(@MyString, 1, CHARINDEX('/', @MyString) - 1)

SET @MyString = SUBSTRING(@MyString,(CHARINDEX('/', @MyString) + 1), LEN(@MyString))

SELECT @NUMERO = SUBSTRING(@MyString, 1, CHARINDEX('/', @MyString) - 1)

SET @MyString = SUBSTRING(@MyString,(CHARINDEX('/', @MyString) + 1), LEN(@MyString))

SELECT @PISO = SUBSTRING(@MyString, 1, CHARINDEX('/', @MyString) - 1)

SET @MyString = SUBSTRING(@MyString,(CHARINDEX('/', @MyString) + 1), LEN(@MyString))

SELECT @COD_POSTAL = @MyString

SELECT @CALLE AS CALLE

, @NUMERO AS NUMERO

, @PISO AS PISO

, @COD_POSTAL AS [CODIGO POSTAL]

with DIRECCION as (

select direccion = 'CERRITO/866/PB/1010' union

select direccion = 'CERRITO/866/P2/1010' union

select direccion = 'CERRITO/866/P3/1010' union

select direccion = 'CERRITO/866/P4/1010' union

select direccion = 'CERRITO/866/P5/1010'

),

	ATransformar as (

	select Direccion = replace(direccion, '/','.')

	from DIRECCION

)

select	parsename(direccion,1)

,		parsename(direccion,2)

,		parsename(direccion,3)

,		parsename(direccion,4)

from ATransformar