Residential addresses anyone done this or something similar

Transact-SQL
1

Consider Two fields, the first contains a home address, the second a postcode (zipcode)
in the first I have 112 anywhere street west ham London. I want to return just the first part, i.e. 112
The second I want SW1X1WE. UK codes have an initial part and end in 3 characters, so remove all spaces, then regardless of length, put a space before the last three characters so I would end up with SW1X 1WE.

I want the end user to be able to see enough information to validate a home address without providing them the address

It should be possible, and I can't add functions into the server to do the donkey work

2

Please provide a sampling of data in the following format
Create table sample(address varchar(50), zipcode varchar(10))

Insert into sample
Select '1234 anywhere street', 'swz djdjdj'

Then the result you want for the sample data you provided

3

Breaking apart the postcode like that is relatively trivial:

select *
from @postcodes P
cross apply
(
	select replace(P.code,' ','') as spacefreecode
) X1
cross apply
(
	select
		left(spacefreecode, len(spacefreecode) - 3) 
		+ ' '+ right(spacefreecode,3) as Postcode
) X2

Dealing with the address is more complex, as real-world addresses don't necessarily follow a simple pattern. For example "Flat 1A anywhere st west ham london" or "Greenacre House anywhere st west ham london" might be equally viable addresses and the "first part" doesn't necessarily get you what you want.

4

Here is one way to do this:

Declare @addresses Table (AddressLine varchar(50), PostCode varchar(20));
 Insert Into @addresses 
 Values ('112 anywhere street west ham London', 'SW 1X 1WE ')
      , ('1234anywherefulladdress', 'SS11 1XE')
      , ('123 somewhere else', 'SW2X2SE');

 Select *
   From @addresses a
  Cross Apply (Values (replace(a.PostCode, ' ', ''))) p(PostCode)
  Cross Apply (Values (stuff(p.PostCode, len(p.PostCode) - 2, 0, ' '))) x(PostCode)
  Cross Apply (Values (left(a.AddressLine, coalesce(nullif(charindex(' ', a.AddressLine, 1), 0), len(a.AddressLine))))) y(AddressNumber);
5

Thank you, as far as i can determine, Cross Apply does not work within the select. I have a usable method now using just the ```
select
left(spacefreecode, len(spacefreecode) - 3)
+ ' '+ right(spacefreecode,3)

6

Thank you, teh cross apply dosen't seem to work for my needs but the charindex is something I noted to try, its has given me ideas, currently I'm going for just the first 20 characters to check against and see if I can get that working

7

Why does the cross apply not work? It doesn't go in the select...

8

the data is already part of the select and that is where it is being processed, so it dosen't work there. i don't doubt it could be worked into the where, just don't have the constant exposure to queries to make that a obvious transition for me

9

Not sure i understand. Cross apply is part of the from, not the where or the select and i provided a working example.

in fact, you seem to be using a cross apply to get spacefreecode now.

10

I know I said WHERE, didn't mean that, The data is manipulated in SELECT as it is already part of the main query. Its an area I have no knowledge of, so always a steep learning curve for something new. The spacefreecode was just a simple lift and paste otherwise I would have rewritten it

, left(LTRIM(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(PRP.ADDRESS),CHAR(10),' '),CHAR(13),' '),'TERM/HOME ADDRESS:- ',''),'TERM ADDRESS:-',''),'APARTMENT',''),'TERM ADDRESS: ',''),'TERM ADDRESS/HOME AD',''),'TERM /HOME ADDRESS:- ',''),'TERM/HOME ADDRESS:- ',''),'TERM/HOME ADDRESS:-',''),'TERM/PLACEMENT ADDRE',''),'TERM/PLACEMENT ADDRESS:- ',''),'Basement Flat',''),'Ground Floor Flat',''),'First Floor Flat',''),'NO ',''),'FLAT ',''),'ROOM ',''),',',''),'.',''),' ',' '),' ',' ')),20) + ', '+CASE WHEN PRP.POSTCODE = '' THEN '' WHEN PRP.POSTCODE IS NULL THEN '' ELSE LTRIM(replace(REPLACE(LEFT(ltrim(UPPER(PRP.POSTCODE)),LEN(PRP.POSTCODE)-3) +' '+RIGHT(replace(UPPER(PRP.POSTCODE),' ',''),3),' ',' '),'O','0')) END AS [ADDRESS AND POSTCODE] untidy but does what i want roughly