-4

enter image description here

ID  NAME         Address1                    Address2
1   kjp     Station raod anand                 NULL
2   Roma    7000 ny street ny 21075            NULL
3   rony    5001 oak tree st jessup 21005      NULL

ANd I want Result like 

ID  NAME         Address1        Addres2    postalcode
1   kjp     Station raod           Anand     NULL
2   Roma    7000 ny street         ny        21075  
3   rony    5001 oak tree st       jessup    21005
marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Krushit
  • 49
  • 2
  • 5

2 Answers2

1

Parsing an address can be tricky.

One option is to automate a process using the GOOGLE API to geocode the address, parse the JSON results to return a more standardized results. This could be time consuming, but you will have more confidence in the data.

https://maps.googleapis.com/maps/api/geocode/json?address=1600%20Pennsylvainia%20Ave,%20Washington%20DC
John Cappelletti
  • 63,100
  • 6
  • 36
  • 57
0
  • I find a function to split the string by space.
  • Using REVERSE find out the last two words on each string.
  • Checking [last] and [beforeLast]:
    • Can calculate how many chars remove from [Addres1]
    • Can determinate if [last] is Numeric then that mean [beforeLast] will be [Address2] other wise [last] is [Address2]
    • Can determinate if [last] is Numeric then I have a zip code.

SQL DEMO

WITH lastToken as (
    SELECT [ID], 
           ( SELECT REVERSE(s)
             FROM  dbo.SplitString(REVERSE(Address1), ' ')
             WHERE zeroBasedOccurance = 0
           ) as last,
           ( SELECT REVERSE(s)
             FROM  dbo.SplitString(REVERSE(Address1), ' ')
             WHERE zeroBasedOccurance = 1
           ) as beforeLast
   FROM T1
)    
SELECT T1.ID, T1.Name, 
       LEFT(T1.[Address1], 
            LEN([Address1]) -
            CASE WHEN ISNUMERIC(last) = 1
                 THEN LEN(last) + LEN(beforeLast) + 2
                 ELSE LEN(last) + 1 
            END
           ) [Address1],
       CASE WHEN ISNUMERIC(last) = 1
            THEN beforeLast
            ELSE last
       END as [Address2],       
       CASE WHEN ISNUMERIC(last) = 1 
            THEN last
       END as [zipcode], 
       T2.*       
FROM T1
JOIN lastToken T2
  ON T1.[ID] = T2.[ID];

OUTPUT

enter image description here

Community
  • 1
  • 1
Juan Carlos Oropeza
  • 44,203
  • 10
  • 63
  • 104
  • except not all zipcodes are numeric and if it's the 5-4 notation you have a - or not. – xQbert Nov 11 '16 at 20:13
  • @xQbert arent zipcodes 5 digits? – Juan Carlos Oropeza Nov 11 '16 at 20:14
  • in the USA 5 or 5-4 such as 38192-3813 (not a real example) in canada it's #Z#-#Z# for 6 characters 3x3. in other countries they also vary. – xQbert Nov 11 '16 at 20:17
  • @xQbert I see, well is easy update with a regular expresion ;) – Juan Carlos Oropeza Nov 11 '16 at 20:18
  • True. but you have to know all the rules, know what country your dealing with to apply the correct rules... and things change over time. Good ideas and good starts but a complete solution I don't think's possible with the given details defiantly a good start but now you know why I say that this is not a silver bullet solution. – xQbert Nov 11 '16 at 20:20
  • @xQbert Is ok I know what you mean, I do almost the same. I usually would leave those problems alone because additional rules appear after find the solution. But is friday so almost ready to go home :) – Juan Carlos Oropeza Nov 11 '16 at 20:24