SQL Server解析泰语全名到第一个 [英] SQL Server Parsing Thai Language Full Name to First Last

问题描述

我有一个独特的问题.我在SQL Server上工作了很长时间.我们将包含全名的文件导入SQL Server.我需要做的就是将全名解析为名字和姓氏.如果名称是英文字符集，我的解析工作正常.但是，我们正在解析使用不同字符集的泰语名称?

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?

这是我的代码:

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;

结果:

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

就像我说的那样，当我使用英语时，查询工作正常.例如，"John Smith"返回:

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.

推荐答案

我以前已经看过这种涉及反向和东西的(IMO)复杂逻辑，并且不了解使用反向的目的.对我来说，这似乎太复杂了.下面做了您想要的而不使用反向的操作.

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
;

为了保持一致性(您应该养成一种习惯)，我已将字符串文字从ansi字符串更改为unicode字符串(以N'开头).并警告一句-名称很复杂.验证关于始终具有2个零件名称的假设.

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.

这篇关于SQL Server解析泰语全名到第一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！