0

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.

Mark
  • 53
  • 6
  • 1
    I can't read what it's supposed to be, but this is a good reason for normalization. Store first name in it's own column, middle name in it's own, last name, suffix, etc.... – S3S Nov 09 '18 at 21:51
  • Per the REVERSE function's documentation `string_expression must be of a data type that is implicitly convertible to varchar.` ... you can't use REVERSE. You can either write your own function that will do what reverse does character by character or if it is always two words you can do it without needing to use reverse. – Eric J. Price Nov 09 '18 at 23:46

1 Answers1

0

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.

SMor
  • 2,830
  • 4
  • 11
  • 14
  • 1
    In addition, (1) the whitespace in Thai usually serves the same purpose as dot and comma in English. IOW, First and Last name can go with no space between; (2) also, in many Asian cultures the Family name goes first. – Be Brave Be Like Ukraine Nov 10 '18 at 00:46