Robbie Mills Robbie Mills - 4 months ago 9
SQL Question

Swapping strings in TSQL

In T-SQL, how do I swap 2 substrings?

My URL column has:

https://www.host.com/test/last-first


How can I change it to:

https://www.host.com/test/first-last


eg:

https://www.host.com/test/smith-tim --> https://www.host.com/test/tim-smith


Is this even possible?

Answer

I thought https://www.host.com/test string same for all records.

Length of https://www.host.com/test is 27

DECLARE @Value NVARCHAR(MAX) = 'https://www.host.com/test/smith-tim'

SELECT 
    SUBSTRING(@Value, 27, CHARINDEX('-', @Value, 27)) AS FullName, -- smith-tim
    SUBSTRING(@Value, 27, CHARINDEX('-', @Value, 27) - 27) AS FirstName, -- smith
    SUBSTRING(@Value, CHARINDEX('-', @Value, 27) + 1, 10000) AS LastName, -- tim
    SUBSTRING(@Value, 0, 27) + 
        SUBSTRING(@Value, CHARINDEX('-', @Value, 27) + 1, 10000) + '-' +
        SUBSTRING(@Value, 27, CHARINDEX('-', @Value, 27) - 27) AS Result -- https://www.host.com/test/tim-smith