Swap first last names in string

Transact-SQL
1

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

or
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!

2

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

FirstName, MiddleInitial, Lastname ?

3

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

1 Like
4

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

5

I'd like to update the table.

6

Here's a screen shot:
image

7

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

8

is this a one time update or continuous?

Select id, 
       OwnerName,
       --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
9

fake name and address

10

one time

11

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)
CROSS APPLY (
    SELECT CHARINDEX(' ', DP.OwnerName + ' ') AS FirstSpaceInOwnerName
) AS alias1
12

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
13

hi

I tried to do this

Hope it helps
:slight_smile:
:slight_smile:

drop create data ... 
drop table #abc 
go 

create table #abc 
(
col varchar(100)
)
go 

insert into #abc select 'Smith John ' 
go 

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

image
image.png396×511 3.77 KB

14

Here is a useful article about names.