SQL Server Parsing Thai Language Full Name to First Last

Question

I have a unique issue. I've worked a long time with SQL Server. We import a file into SQL Server that includes Full Name. All I need to do is parse the full name into First and Last. If the name is English character set, my parse works fine. But, we're parsing Thai names which uses a different character set?

This is my code:

DECLARE @FullName NVARCHAR(MAX) = N'กล้วยไม้ สวามิวัศดุ์'


SELECT 
    LEN(@FullName) AS StringLength,
    @FullName AS FullName,
    REVERSE(@FullName) AS ReverseName,
    LEFT(@FullName, LEN(@FullName) - CHARINDEX(' ', REVERSE(@FullName))) AS FirstName,
    STUFF(RIGHT(@FullName, CHARINDEX(' ', REVERSE(@FullName))),1,1,'') AS LastName;

Results:

20  กล้วยไม้ สวามิวัศดุ์    ์ุดศัวิมาวส ้มไยว้ลก    กล้วยไม้ สวามิวัศดุ์     NULL

Like I said, the query works fine when I use english. For example, 'John Smith' returns:

10  John Smith  htimS nhoJ  John    Smith

I have searched this and other sites all day! Thanks in advance.

Answer 1

I've seen this (IMO) complicated logic involving reverse and stuff previously and do not understand the purpose of using reverse. It just seems overly complicated to me. Below does what you want without using reverse.

set nocount on;
declare @names table (FullName nvarchar(40) not null); 
insert @names (FullName) values (N'กล้วยไม้ สวามิวัศดุ์'), (N'John Smith'), (N'Bozo Clown'), (N'Eva Perón'), (N'Stefan Löfven');

select * from @names;


select  
    LEN(FullName) AS StringLength,
    FullName AS FullName,
    LEFT(FullName, CHARINDEX(N' ', FullName)) AS FirstName,
    RIGHT(FullName, len(FullName) - CHARINDEX(N' ', FullName) + 1) as LastName,
    STUFF(FullName, 1, CHARINDEX(N' ', FullName), N'') as LName  
from  @names
;

For consistency (a habit you should develop) I've changed your string literals from ansi strings to unicode strings (prefaced with N'). And a word of warning - names are complicated. Verify your assumption about always having 2 part names.