SQL Server - Separar cadena de campo direccion el cual lleva una coma

Estimados

por favor su ayuda, como puedo separar los registros de un campo que contienes direcciones de calles las cuales vienen de esta forma:

avenida jose prieto, 453

y poder separarlos en dos campos como el campo2 y campo3

campo1 campo2 campo3
avenida jose prieto, 453 avenida jose prieto 453

saludos y gracias.

Con CHARINDEX, localizas la posicion de la coma y con SUBSTRING, tomas el primero valor, de la posicion 1 y hasta la posicion de la coma.


SELECT SUBSTRING('avenida jose prieto, 453 avenida jose prieto 453', 1, CHARINDEX(',','avenida jose prieto, 453 avenida jose prieto 453')-1), SUBSTRING('avenida jose prieto, 453 avenida jose prieto 453', CHARINDEX(',','avenida jose prieto, 453 avenida jose prieto 453') + 1, LEN('avenida jose prieto, 453 avenida jose prieto 453'))

Gracias Isaias,

pero tengo un problema, mi estoy utilizando un qury similr a la que me enviaste, le puse un order by para saber en que registro se cae, ya que me entrega este error

"Invalid length parameter passed to the SUBSTRING function."


estoy utilizando eta query:

select direccion_receptor
,substring(direccion_receptor,1,patindex('%,%',direccion_receptor)-1)as calle, substring(direccion_receptor ,patindex('%,%',direccion_receptor)
+ 1,len(direccion_receptor) - patindex('%,%',direccion_receptor) + 1) as numero
from Devoluciones
order by direccion_receptor

la cual se cae en el regitro 191, y revisando en la base el registro 192 viene asi:

21 de mayo

sin coma y sin numeración,

como lo puedo solucionar por favor Isaias?

Primero, yo no utilizo PATINDEX
Segundo, debes aplicar el filtro en el WHERE CHARINDEX(',', TUCAMPO) > 1