[Solved] How to parse apt no from the address field in SQL [closed]

Question

One more method based on JSON.

It will work starting from SQL Server 2016 onwards.

SQL Server 2016

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(250));  
INSERT Into @tbl (tokens) VALUES 
('250 Cambridge ST 305'),
('30 ACKERS AVE 2');
-- DDL and sample data population, end

DECLARE @Word INT = 4;
SELECT * 
    , token = JSON_VALUE('["' +
        REPLACE(REPLACE(tokens
            ,'"','\"')
            ,' ','","') +
        '"]', CONCAT('$[', @Word-1,']'))
FROM @tbl ;

SQL Server 2008 onwards

XML/XQuery based method. Useful when number of tokens fluctuates (or unknown) for each row.

DECLARE @separator CHAR(1) = SPACE(1);
SELECT * 
    , TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML).value('(/root/r[last()]/text())[1]', 'NVARCHAR(30)') AS token
FROM @tbl;

Output

+----+----------------------+-------+
| ID |        tokens        | token |
+----+----------------------+-------+
|  1 | 250 Cambridge ST 305 |   305 |
|  2 | 30 ACKERS AVE 2      |     2 |
+----+----------------------+-------+

Accepted Answer