如何递归解析SQL语句中的数据

Question

我正在尝试清理数据库中“名称”字段中的数据，并将该数据拆分为名字、中间名和名称。姓。目前，我正在使用 Case 语句来查找文本内的各种触发器，以帮助我以某种方式格式化输出。

然而，我开始注意到我将测试嵌套在其他测试中，并且需要弄清楚如何递归地处理数据。请参阅此示例，了解我如何提取名字。

Case 
  When CharIndex(' ',LTrim(RTrim(Name))) in (0,1) Then '' --'empty or LName'
  When Left(Name,3) IN ('MR ','MS ', 'DR ','MRS') Then    --'Prefix Titles'
    Case --'If we found a prefix, run the same "tests" with the prefix removed'
      When CharIndex(' ',LTrim(RTrim(Right(Name,Len(Name)-CharIndex(' ',Name)))))
        in (0,1) Then '' 
      When SubString(LTrim(RTrim(Right(Name,Len(Name)-CharIndex(' ',Name)))),3,1) 
        = '&' Then SubString(LTrim(RTrim(Right(Name,Len(Name)-CharIndex(' ',
        Name)))),1,5)
      Else Left(LTrim(RTrim(Right(Name,Len(Name)-CharIndex(' ',Name)))),
        CHarIndex(' ',LTrim(RTrim(Right(Name,Len(Name)-CharIndex(' ',Name)))))-1) 
    End
  When SubString(LTrim(RTrim(Name)),3,1) = '&' Then 
    SubString(LTrim(RTrim(Name)),1,5) --'Look for initials e.g. J & A Smith'
  Else Left(LTrim(RTrim(Name)),CHarIndex(' ',LTrim(RTrim(Name)))-1) 
  End

因此，为了让它在更复杂的情况下工作（例如MR JOHN A SMITH JR），我需要递归测试。在命令式编程中，如果我有一个名为 GetFirstName 的函数，我会执行类似的操作：

GetFirstName('MR JOHN A SMITH JR')
//GetFirstName identfies 'MR' and within the function it calls:
||
\\
   ==> GetFirstName('JOHN A SMITH JR')
       //GetFirstName identifies 'JR' and within the function it calls:
       ||
       \\
          ==> GetFirstName('JOHN A SMITH')
              //Finally, it returns 'JOHN'

理想情况下，用直接 SQL 执行此操作会很棒，但我不确定这是否可行。如果我不使用直接 SQL，我还有哪些替代方案？ （我使用的是 SQL Server 2005）

Answer 1

我认为用直接的SQL 来完成这件事并不容易/干净。您可以使用正则表达式，但是您必须编写自己的 CLR 函数来提供正则表达式功能。

Answer 2

这听起来像是一项一次性活动。您确定不能在使用临时表的多个语句中执行此操作吗？如果它是一次性活动，那么您可能对代码调试的准确性和简单性的需求比对性能的需求更高。

考虑这样的事情：

CREATE TABLE #MyNames (
  PersonID INT PRIMARY KEY,
  OriginalName VARCHAR(50),
  WorkingName VARCHAR(50),
  CandidateTitle VARCHAR(10),
  CandidateLastName VARCHAR(50),
  CandidateFirstName VARCHAR(50),
  CandidateMiddleName VARCHAR(50)
  --Your other candidate fields.....
)

INSERT INTO #MyNames (PersonID, OriginalName)
 SELECT TOP 100 ID, LTRIM(RTRIM(Name)) from SourcePersonTable

--Possibly add some indexes here for original name

UPDATE #MyNames
 SET CandidateTitle = LEFT(OriginalName,3),
     WorkingName = SUBSTRING(OriginalName,4,9999)
Where LEFT(OriginalName,3) IN
    ('MR ','MRS','MS ','DR ')

-- etc...

只需继续添加步骤并编辑 [WorkingName] 字段即可。完成一次扫描后，只需执行...

UPDATE #MyNames SET WorkingName = OriginalName

...即可准备进行另一次扫描。