问题描述
我想要一个 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') bI 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 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！