问题描述
我想要一个 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