como separar cadena

[Teodor]

Que tal, estoy estructurando una base de datos sin embargo me encontre con el siguiente problema que tengo un campo llamado direccion en donde tengo agregado el nombre de la calle, el numero y la colonia y quiero separar este campo en 2 donde en direccion quede el nombre de la calle y el numero en otro campo solo quede la colonia. les mando un ejemplo de la informacion que tengo:

direccion
VALLE DEL SAGUAN #712 COL. VALLE DEL REAL
BOSQUES MEXICANOS #424 COL. BOSQUES DE LA PRADERA
JERÉZ DEL MIRADOR #110-A COL. CAÑADA DE JERÉZ
ZEMPOALA #512-A COL.AZTECA
PQUE. FRANCIA #124-A COL. LA NORIA

y quiero que quede de la siguiente manera:

direccion
VALLE DEL SAGUAN #712
BOSQUES MEXICANOS #424
JERÉZ DEL MIRADOR #110-A
ZEMPOALA #512-A
PQUE. FRANCIA #124-A


colonia
COL. VALLE DEL REAL
COL. BOSQUES DE LA PRADERA
COL. CAÑADA DE JERÉZ
COL.AZTECA
COL. LA NORIA

ojala y alguien pueda ayuarme y de ante mano muchas gracias

[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!