SQL concatenate columns into string and only pull address

I have 4 fields that are address fields, but the address fields can be blank and have a persons name within one of the 4 fields. The name is usually before the start of the address. What I need to do is remove and names and empty string and only pull the core address or column that starts with a number.

For Example:

Select sourceaddr1, sourceaddr2, sourceaddr3, sourceaddr4
From MyTable

Results:

What I need to see is the core address only in a single string Like this..

10767CHILDRESSCT

Answer

I took this a little different direction.

First I concatenated the 4 sourceaddr fields together in one temp table like this.

IF OBJECT_ID('tempdb..#core') IS NOT NULL DROP TABLE #core

SELECT DISTINCT             
,'ADDR2'    =   REPLACE(SOURCEADDR1, ' ','')+REPLACE(SOURCEADDR2, ' ','')+REPLACE(SOURCEADDR3, ' ','')+REPLACE(SOURCEADDR4, ' ','')
into #core
  FROM table

Next I created another temp table and brought the concatenated field in and removed all alpha characters like this.

IF OBJECT_ID('tempdb..#coreadd') IS NOT NULL DROP TABLE #coreadd

 Select
, 'CoreAdd' = stuff(addr2, 1, patindex('%[0-9]%', addr2)-1, '')
into #coreadd
from #core

By using the STUFF function and the above logic I was able to get just the core address all concatenated together like this. This logic removes any of the field that were empty strings and only looks for the first number in the string.

RESULTS:

10767CHILDRESSCT