t, Sql Server - rextester

Language: Layout:

create function dbo.SplitString 
    (
        @str nvarchar(4000), 
        @separator char(1)
    )
    returns table
    AS
    return (
        with tokens(p, a, b) AS (
            select 
                1, 
                1, 
                charindex(@separator, @str)
            union all
            select
                p + 1, 
                b + 1, 
                charindex(@separator, @str, b + 1)
            from tokens
            where b > 0
        )
        select
            p-1 zeroBasedOccurance,
            substring(
                @str, 
                a, 
                case when b > 0 then b-a ELSE 4000 end) 
            AS s
        from tokens
      )
    GO

CREATE TABLE T1
    ([ID] int, [NAME] varchar(4), [Address1] varchar(29), [Address2] varchar(4))
;
    
INSERT INTO T1
    ([ID], [NAME], [Address1], [Address2])
VALUES
    (1, 'kjp', 'Station raod anand', NULL),
    (2, 'Roma', '7000 ny street ny 21075', NULL),
    (3, 'rony', '5001 oak tree st jessup 21005', NULL)
;

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];

View schema
Execution time: 0,02 sec, rows selected: 3, rows affected: 3, absolute service time: 0,17 sec