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
Gordon Linoff :
If you need the second filled in address field, you can use apply and some unpivoting logic:\n\nselect t.*, s.sourceaddr\nfrom mytable t cross apply\n (select v.sourceaddr\n from (values (1, t.sourceaddr1),\n (2, t.sourceaddr2),\n (3, t.sourceaddr3),\n (4, t.sourceaddr4)\n ) v(i, sourceaddr)\n where sourceaddr is not null\n order by i\n offset 1 fetch first 1 row only\n ) s;\n\n\nEDIT:\n\nYou can also express this as:\n\nselect t.*, s.sourceaddr\nfrom mytable t cross apply\n (select v.sourceaddr, row_number() over (order by i) as seqnum\n from (values (1, t.sourceaddr1),\n (2, t.sourceaddr2),\n (3, t.sourceaddr3),\n (4, t.sourceaddr4)\n ) v(i, sourceaddr)\n where sourceaddr is not null\n offset 1 fetch first 1 row only\n ) s\nwhere seqnum = 2;\n",
2020-01-03T22:16:10