将名称后缀(Jr、Sr、IV 等)移动到另一个字段的 TSQL 语句

2021-09-10 00:00:00 tsql sql-server

我想要一个 TSQL 语句将名称后缀(Jr、Sr.、IV 等)移动到另一个字段中.

我看到的后缀是JR SR I II III IV V

这是一个数据样本

<前>姓布朗宁二号伯奇二世布斯，JR.卡尼III克里斯蒂安，SR小科尔文牛郎，JR.

我希望将后缀从姓氏字段移到另一个名为后缀的字段中.

<前>姓氏后缀布朗宁二号伯奇一世小布斯卡尼III克里斯蒂安 SR小科尔文小牛

我使用的是 SQL Server 2005，可以使用 SQL# 函数.
任何帮助将不胜感激.

解决方案

使用 SQL# 函数可能比这做得更好，但在直接的 T-SQL 中，您可以使用.

这里的主要思想是使用 REVERSE 和 PATINDEX 解析出名称中的最后一个段/标记，然后将其与已知后缀列表进行匹配.

首先是一些测试数据:

IF OBJECT_ID('tempdb..#names') IS NOT NULL DROP TABLE #names如果 OBJECT_ID('tempdb..#suffixes') 不是 NULL 删除表 #suffixesCREATE TABLE #names (name VARCHAR(32))CREATE TABLE #suffixes(后缀VARCHAR(32))走INSERT #names VALUES ('BRUNNING, II')插入 #names 值 ('BURCH II')INSERT #names VALUES ('BUSS, JR.')插入 #names 值('CANI III')插入 #names 值 ('CHRISTIAN,SR')插入 #names 值('COLVIN Jr')插入 #names 值 ('COWHERD,JR.')插入 #names 值('BILLY BOB')插入 #names 值 ('JOHNNY')INSERT #suffixes VALUES ('II')INSERT #suffixes VALUES ('III')INSERT #suffixes VALUES ('JR')INSERT #suffixes VALUES ('SR')

然后，一个内联 SELECT 版本.注意使用 NULLIF 来控制 SUBSTRING 错误.

SELECT姓名, left_segments, right_segment, new_name = CASE WHEN b.suffix IS NOT NULL THEN a.left_segments ELSE a.name END, b. 后缀从 (选择姓名, left_segments = CASE WHEN left_segments LIKE '%[,]' THEN LEFT(left_segments,LEN(left_segments)-1) ELSE left_segments END, right_segment = CASE WHEN right_segment LIKE '%[.]' THEN LEFT(right_segment,LEN(right_segment)-1) ELSE right_segment END从 (选择 *, left_segments = RTRIM(LEFT(RTRIM(name),LEN(name)-NULLIF(PATINDEX('%[ ,]%',REVERSE(RTRIM(name))),0))), right_segment = RIGHT(RTRIM(name),NULLIF(PATINDEX('%[ ,]%',REVERSE(RTRIM(name))),0)-1)来自#names) 一种) 一种LEFT JOIN #suffixes b ON a.right_segment = b.suffix

或者，UPDATE 带有本地变量:

ALTER TABLE #names 添加left_segments VARCHAR(64), right_segment VARCHAR(64)走宣布@name VARCHAR(64), @len INT, @last_delim INT, @left_segments VARCHAR(64), @right_segment VARCHAR(64)更新#names SET@name = RTRIM(name), @len = LEN(@name), @last_delim = @len-NULLIF(PATINDEX('%[ ,]%',REVERSE(@name)),0), @left_segments = RTRIM(LEFT(@name,@last_delim)), @right_segment = RIGHT(@name,@len-@last_delim-1), @left_segments = CASE WHEN @left_segments LIKE '%[ ,]' THEN LEFT(@left_segments,LEN(@left_segments)-1) ELSE @left_segments END, @right_segment = CASE WHEN @right_segment LIKE '%[.]' THEN LEFT(@right_segment,LEN(@right_segment)-1) ELSE @right_segment END, left_segments = @left_segments, right_segment = @right_segment选择一个.*, new_name = CASE WHEN b.suffix IS NOT NULL THEN a.left_segments ELSE a.name END, 后缀 = b.suffixFROM #names a LEFT JOIN #suffixes b ON a.right_segment = b.suffix

内联 SELECT 相当方便，但难以阅读和排除故障.我更喜欢带有本地变量的 UPDATE 用于以后可能需要返回的任何内容.此外，它还可以更轻松地应用单个编辑.

EDIT、SELECT 方法，稍作修改，并包含在内联表值函数中.内联 TVF 应该比标量 UDF 更有效，并且您可以获得多个返回值来启动.

CREATE FUNCTION dbo.ParseNameAndSuffix (@name VARCHAR(64), @ValidSuffixes VARCHAR(512))返回表作为返回(选择left_segments, right_segment, new_name = CASE WHEN CHARINDEX(';'+right_segment+';',';'+@ValidSuffixes+';') >0 THEN a.left_segments ELSE a.name END, suffix = CASE WHEN CHARINDEX(';'+right_segment+';',';'+@ValidSuffixes+';') >0 THEN a.right_segment END从 (选择姓名, left_segments = CASE WHEN left_segments LIKE '%[,]' THEN LEFT(left_segments,LEN(left_segments)-1) ELSE left_segments END, right_segment = CASE WHEN right_segment LIKE '%[.]' THEN LEFT(right_segment,LEN(right_segment)-1) ELSE right_segment END从 (选择名称, left_segments = RTRIM(LEFT(name,LEN(name)-NULLIF(PATINDEX('%[ ,]%',REVERSE(name)),0))), right_segment = RIGHT(name,NULLIF(PATINDEX('%[ ,]%',REVERSE(name)),0)-1)FROM (SELECT name = LTRIM(RTRIM(@name))) a) 一种) 一种)走SELECT * FROM #names a交叉应用 dbo.ParseNameAndSuffix(a.name,'II;III;JR;SR') b

I would like to have a TSQL Statement to move Name Suffix (Jr, Sr., IV, etc) into another field.

The suffixes I see are JR SR I II III IV V

Here is a sample of the data

LastName
BRUNNING, II
BURCH II
BUSS, JR.
CANI III
CHRISTIAN,SR
COLVIN Jr
COWHERD,JR.

I would like the suffix moved out of the LastName field into another field called Suffix.

LastName   Suffix  
BRUNNING   II
BURCH      I     
BUSS       JR
CANI       III
CHRISTIAN  SR
COLVIN     JR
COWHERD    JR

I am using SQL Server 2005 and can use SQL# functions.
Any help would be greatly appretiated.

解决方案

You can probably do better than this using the SQL# functions, but in straight T-SQL, here you go.

The main idea here is to parse out the last segment/token in the name using REVERSE and PATINDEX, and then match it to a list of known suffixes.

First some test data:

IF OBJECT_ID('tempdb..#names') IS NOT NULL DROP TABLE #names
IF OBJECT_ID('tempdb..#suffixes') IS NOT NULL DROP TABLE #suffixes
CREATE TABLE #names (name VARCHAR(32))
CREATE TABLE #suffixes (suffix VARCHAR(32))
GO

INSERT #names VALUES ('BRUNNING, II' )
INSERT #names VALUES ('BURCH II'     )
INSERT #names VALUES ('BUSS, JR.'    )
INSERT #names VALUES ('CANI III'     )
INSERT #names VALUES ('CHRISTIAN,SR' )
INSERT #names VALUES ('COLVIN Jr'    )
INSERT #names VALUES ('COWHERD,JR.'  )
INSERT #names VALUES ('BILLY BOB'    )
INSERT #names VALUES ('JOHNNY'       )

INSERT #suffixes VALUES ('II' )
INSERT #suffixes VALUES ('III')
INSERT #suffixes VALUES ('JR' )
INSERT #suffixes VALUES ('SR' )

Then, an inline SELECT version. Notice the use of NULLIF to control for SUBSTRING errors.

SELECT
  name
, left_segments 
, right_segment
, new_name = CASE WHEN b.suffix IS NOT NULL THEN a.left_segments ELSE a.name END
, b.suffix
FROM (
  SELECT 
    name
  , left_segments = CASE WHEN left_segments LIKE '%[ ,]' THEN LEFT(left_segments,LEN(left_segments)-1) ELSE left_segments END
  , right_segment = CASE WHEN right_segment LIKE '%[.]' THEN LEFT(right_segment,LEN(right_segment)-1) ELSE right_segment END
  FROM (
    SELECT * 
    , left_segments = RTRIM(LEFT(RTRIM(name),LEN(name)-NULLIF(PATINDEX('%[ ,]%',REVERSE(RTRIM(name))),0)))
    , right_segment = RIGHT(RTRIM(name),NULLIF(PATINDEX('%[ ,]%',REVERSE(RTRIM(name))),0)-1)
    FROM #names
    ) a
  ) a
LEFT JOIN #suffixes b ON a.right_segment = b.suffix

Alternately, UPDATE w/ local vars:

ALTER TABLE #names ADD 
  left_segments VARCHAR(64)
, right_segment VARCHAR(64)
GO

DECLARE 
  @name VARCHAR(64)
, @len INT
, @last_delim INT
, @left_segments VARCHAR(64)
, @right_segment VARCHAR(64)

UPDATE #names SET 
  @name           = RTRIM(name)
, @len            = LEN(@name)
, @last_delim     = @len-NULLIF(PATINDEX('%[ ,]%',REVERSE(@name)),0)
, @left_segments  = RTRIM(LEFT(@name,@last_delim))
, @right_segment  = RIGHT(@name,@len-@last_delim-1)
, @left_segments  = CASE WHEN @left_segments LIKE '%[ ,]' THEN LEFT(@left_segments,LEN(@left_segments)-1) ELSE @left_segments END
, @right_segment  = CASE WHEN @right_segment LIKE '%[.]'  THEN LEFT(@right_segment,LEN(@right_segment)-1) ELSE @right_segment END
, left_segments   = @left_segments
, right_segment   = @right_segment

SELECT a.*
, new_name = CASE WHEN b.suffix IS NOT NULL THEN a.left_segments ELSE a.name END
, suffix = b.suffix
FROM #names a LEFT JOIN #suffixes b ON a.right_segment = b.suffix

The inline SELECT is fairly convenient, but difficult to read and troubleshoot. I prefer the UPDATE with local vars for anything I might have to return to later. Plus, it makes individual edits easier to apply.

EDIT, SELECT method, slightly edited, and wrapped in an inline table-valued function. A inline TVF should be more efficient than a scalar UDF, and you get multiple return values to boot.

CREATE FUNCTION dbo.ParseNameAndSuffix (@name VARCHAR(64), @ValidSuffixes VARCHAR(512))
RETURNS TABLE AS RETURN (
  SELECT
    left_segments 
  , right_segment
  , new_name = CASE WHEN CHARINDEX(';'+right_segment+';',';'+@ValidSuffixes+';') > 0 THEN a.left_segments ELSE a.name END
  , suffix   = CASE WHEN CHARINDEX(';'+right_segment+';',';'+@ValidSuffixes+';') > 0 THEN a.right_segment END
  FROM (
    SELECT 
      name
    , left_segments = CASE WHEN left_segments LIKE '%[ ,]' THEN LEFT(left_segments,LEN(left_segments)-1) ELSE left_segments END
    , right_segment = CASE WHEN right_segment LIKE '%[.]' THEN LEFT(right_segment,LEN(right_segment)-1) ELSE right_segment END
    FROM (
      SELECT name
      , left_segments = RTRIM(LEFT(name,LEN(name)-NULLIF(PATINDEX('%[ ,]%',REVERSE(name)),0)))
      , right_segment = RIGHT(name,NULLIF(PATINDEX('%[ ,]%',REVERSE(name)),0)-1)
      FROM (SELECT name = LTRIM(RTRIM(@name))) a
      ) a
    ) a
  )
GO

SELECT * FROM #names a
CROSS APPLY dbo.ParseNameAndSuffix(a.name,'II;III;JR;SR') b

相关文章