como separar cadena

[Libras]

Código SQL:

CREATE TABLE #temp(
dato VARCHAR(200)
)
 
INSERT INTO #temp (dato) VALUES ('VALLE DEL SAGUAN #712 COL. VALLE DEL REAL')
INSERT INTO #temp (dato) VALUES ('BOSQUES MEXICANOS #424 COL. BOSQUES DE LA PRADERA')
 
 
SELECT 
SUBSTRING(dato,1,(
charindex('COL',dato)-1)) AS direccion,  SUBSTRING(dato,(
charindex('COL',dato)), len(dato)) colonia
FROM #temp
 

Hola este query funciona siempre y cuando con lo que separas la direccion de la colonia sea la palabra COL.

Saludos!