Swap first last names in string

In dbo.tblDataParcel column name OwnerName, I want to swap first and last names
from this: Smith John H
to this: John H Smith

from this: Smith John
to this: John Smith

I have this code but get an error: "Invalid length parameter passed to the LEFT or SUBSTRING function".

SELECT SUBSTRING(OwnerName, CHARINDEX(' ', OwnerName) + 2, LEN(OwnerName) - CHARINDEX(' ', OwnerName) + 1)
+ ' ' + SUBSTRING(OwnerName, 1, CHARINDEX(' ', OwnerName) - 1)
FROM dbo.tblDataParcel

I realize I may need a CASE WHEN statement depending on whether or not a middle initial exists. Thank you for any help!

might this be a good time to change things so that your tblDataParcel table has

FirstName, MiddleInitial, Lastname ?

I agree, but I have no control over how the software program my office uses places the data into sql.

1 Like

so are you going to be updating the table or this is just for a certain view/report? presentation layer?

I'd like to update the table.

Here's a screen shot:

I would remove that address from this post if I were you.

is this a one time update or continuous?

Select id, 
       --Substring(OwnerName, 1,Charindex(' ', OwnerName)-1) as Name,
       --Substring(OwnerName, Charindex(' ', OwnerName)+1, LEN(OwnerName)) as  Surname,
	   Substring(OwnerName, Charindex(' ', OwnerName)+1, LEN(OwnerName)) + ' ' + Substring(OwnerName, 1,Charindex(' ', OwnerName)-1)
from tblDataParcel

fake name and address

one time

I think you actually just need to move the first word to the end (code to figure out multi-word last names / suffixes is much more complex and would require lots of additional code no matter what approach you took).

SELECT DP.OwnerName AS OwnerName_Original,
    SUBSTRING( DP.OwnerName, alias1.FirstSpaceInOwnerName, 1000) + ' ' +
    LEFT(DP.OwnerName, alias1.FirstSpaceInOwnerName - 1)
FROM ( --dbo.tblDataParcel DP
    VALUES('Smith John H'),('Smith John'),('OneNameOnly')
) AS DP(OwnerName)
    SELECT CHARINDEX(' ', DP.OwnerName + ' ') AS FirstSpaceInOwnerName
) AS alias1

If this is one time - then what are you going to do next month after the users have entered more data? There are several approaches you can take without modifying the application, but it will depend on why you need to change the data.

1 Like


I tried to do this

Hope it helps

drop create data ...
drop table #abc 

create table #abc 
col varchar(100)

insert into #abc select 'Smith John ' 

select * from #abc
SQL ..
;WITH rec_cte 
     AS (SELECT Cast(Stuff(b.col, Patindex('%[^a-z]%', b.col), 10, ' ') AS 
                     VARCHAR) AS 
                Substring(b.col, Len(Stuff(b.col, Patindex('%[^a-z]%', b.col), 
                                 ' ')) 
                                 + 2, 10) 
         FROM   #abc b 
         UNION ALL 
         SELECT Cast(Stuff(rec_cte.ok, Patindex('%[^a-z]%', rec_cte.ok), 10, ' ' 
                     ) AS 
                     VARCHAR) AS 
                Substring(rec_cte.ok, Len(Stuff(rec_cte.ok, Patindex('%[^a-z]%', 
                                      rec_cte.ok), 10 
                                      , ' ')) 
                                      + 2, 10) 
                AS OK 
         FROM   rec_cte 
         WHERE  Len(rec_cte.ok) > 0), 
     AS (SELECT abc, 
                Stuff((SELECT ' ' + abc 
                       FROM   rec_cte 
                       ORDER  BY abc 
                       FOR xml path(''), type).value('.', 'varchar(max)'), 1, 1, 
         FROM   rec_cte 
         GROUP  BY abc) 
SELECT DISTINCT name_reverse 
FROM   cte_final

Here is a useful article about names.