Remove last word from string in one column and put as string in existing empty column when the first column has more than one word? SQL Server

Question

Using SQL Server

I have this:

And I want this:

Does anybody know how that can be done?

question from:https://stackoverflow.com/questions/65925631/remove-last-word-from-string-in-one-column-and-put-as-string-in-existing-empty-c

Answer 1

Please try the following approach. It is based on use of XML and XQuery.

XML and XQuery data model is based on ordered sequences. That's why it is so easy to get position of any item in a sequence: first, last, etc.

SQL

-- DDL and sample data population, start
DECLARE @tbl  TABLE (ID INT IDENTITY PRIMARY KEY, Street VARCHAR(100));
INSERT INTO @tbl (Street) VALUES
('SANDISLE DR.'),
('TAMMY LANE'),
('OLD OREGON TRAIL'),
('ELTEDA'),
('PAROCAR ROAD');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

;WITH rs AS
(
    SELECT * 
        , TRY_CAST('<root><r><![CDATA[' + 
           REPLACE(Street, @separator, ']]></r><r><![CDATA[') + 
           ']]></r></root>' AS XML) AS xmldata
    FROM @tbl
)
SELECT ID, Street
, xmldata.query('if (count(/root/r) gt 1) then data(/root/r[position() ne last()]) 
    else data(/root/r[1])').value('(.)', 'VARCHAR(100)') AS StreetName
, xmldata.query('if (count(/root/r) gt 1) then data(/root/r[last()])
    else ()').value('(.)', 'VARCHAR(100)') AS StreetSuffix
FROM rs;

Output

+----+------------------+------------+--------------+
| ID |      Street      | StreetName | StreetSuffix |
+----+------------------+------------+--------------+
|  1 | SANDISLE DR.     | SANDISLE   | DR.          |
|  2 | TAMMY LANE       | TAMMY      | LANE         |
|  3 | OLD OREGON TRAIL | OLD OREGON | TRAIL        |
|  4 | ELTEDA           | ELTEDA     |              |
|  5 | PAROCAR ROAD     | PAROCAR    | ROAD         |
+----+------------------+------------+--------------+