問題描述

我想要一個 TSQL 語句將名稱后綴(Jr、Sr.、IV 等)移動到另一個字段中.

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

這是一個數據樣本

我希望將后綴從姓氏字段移到另一個名為后綴的字段中.

我使用的是 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

這篇關于將名稱后綴(Jr、Sr、IV 等)移動到另一個字段的 TSQL 語句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！