TSQL Statement to move Name Suffix (Jr, Sr, IV, etc) into another field

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#开发者_JAVA百科 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

Off the top of my head, since you've got a small number of replacements, you could do something like this:

UPDATE [TableName] SET LastName = SUBSTRING(LastName,0, CHARINDEX(lastname,'III')), SUFFIX = 'III' WHERE CHARINDEX(lastname,'III') > 0;

You might want to do some pre-processing to make the formats more consistent.

Consider removing the ending period and replacing all commas with a space. After that, your sample should look like:

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

Then you can identify rows ending in ' I',' II',' III',' JR', and ' SR' and strip their suffix according to its length and update the Suffix field with the value you want.

If CLR is not an option then what Peter suggested is great. However, since you said that you have SQL# then you can do this in a simpler and more functional manner using the RegEx_MatchSimple function in SQL#. I will explain using Peter's example as the starting point.

We can set up the test using nearly the same SQL that Peter used but in this case I will create the Suffixes table as a real (non-Temp) table so that I can reference it in one of the example TVFs below. Maybe you might want to keep them in a table as opposed to passing in as a parameter, but I will show both styles. I also added two names to the #Names table to show how using RegEx can help capture variations in the data (extra spaces and/or commas):

USE [tempdb]
GO
IF OBJECT_ID('tempdb..#Names') IS NOT NULL DROP TABLE #Names
IF OBJECT_ID('tempdb.dbo.Suffixes') IS NOT NULL DROP TABLE dbo.Suffixes
CREATE TABLE #Names (LastName VARCHAR(32))
CREATE TABLE dbo.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 #Names VALUES ('BRUNNING, II ' )
INSERT #Names VALUES ('SMITH ,, SR. ' )

INSERT dbo.Suffixes VALUES ('II' )
INSERT dbo.Suffixes VALUES ('III')
INSERT dbo.Suffixes VALUES ('JR' )
INSERT dbo.Suffixes VALUES ('SR' )

The first thing to show is a simple example of it working with the above data. In this case I use a CTE to generate the list of matches against the names and then filter out the rows that did not match anything. I enclosed the [FullMatch] field in colons so it would be easier to see the leading and trailing spaces being captured:

;WITH cte AS (
    SELECT  names.LastName,
            [SQL#].[SQL#].RegEx_MatchSimple(names.LastName, '(([ ]*,+[ ]*)|([ ]+))' + suff.Suffix + '[.]*[ ]*$', 1, 'IgnoreCase') AS [FullMatch],
            suff.suffix
    FROM    #Names names
    CROSS JOIN tempdb.dbo.Suffixes suff
)
SELECT  cte.LastName, ':' + cte.FullMatch + ':' AS [FullMatch], REPLACE(cte.LastName, cte.FullMatch, '') AS [Replacement], cte.Suffix
FROM    cte
WHERE   cte.FullMatch <> ''

You can carry this theory over to a direct UPDATE statement:

;WITH cte AS (
    SELECT  names.LastName,
            [SQL#].[SQL#].RegEx_MatchSimple(names.LastName, '(([ ]*,+[ ]*)|([ ]+))' + suff.Suffix + '[.]*[ ]*$', 1, 'IgnoreCase') AS [FullMatch],
            suff.Suffix
    FROM    MyTable names
    CROSS JOIN NameSuffixes suff
)
UPDATE  mt
SET     mt.LastName = REPLACE(cte.LastName, cte.FullMatch, ''),
        mt.NameSuffix = cte.Suffix
FROM    MyTable mt
INNER JOIN  cte
        ON  cte.LastName = mt.LastName
WHERE   cte.FullMatch <> ''

You had requested this as a function so that is as follows:

CREATE FUNCTION dbo.ParseNameAndSuffix (@Name VARCHAR(64))
RETURNS TABLE AS RETURN
(
    WITH cte AS (
        SELECT  @Name AS [LastName],
                [SQL#].[SQL#].RegEx_MatchSimple(@Name, '(([ ]*,+[ ]*)|([ ]+))' + suff.Suffix + '[.]*[ ]*$', 1, 'IgnoreCase') AS [FullMatch],
                suff.Suffix
        FROM    tempdb.dbo.Suffixes suff
    )
    SELECT  cte.LastName, cte.FullMatch, REPLACE(cte.LastName, cte.FullMatch, '') AS [Replacement], cte.Suffix
    FROM    cte
    WHERE   cte.FullMatch <> ''
)
GO

And can be used like so:

SELECT  *   
FROM    #Names a
CROSS APPLY dbo.ParseNameAndSuffix(a.LastName) b

-- or --

UPDATE  mt
SET     mt.LastName = REPLACE(parse.LastName, parse.Found, ''),
        mt.NameSuffix = parse.Suffix
FROM    MyTable mt
CROSS APPLY dbo.ParseNameAndSuffix(mt.LastName) parse

To more closely match the example given by Peter that passed in the suffixes as a parameter, that can be done as follows using the String_Split function in SQL#:

CREATE FUNCTION dbo.ParseNameAndSuffix2 (@Name VARCHAR(64), @Suffixes VARCHAR(MAX))
RETURNS TABLE AS RETURN
(
    WITH cte AS (
        SELECT  @Name AS [LastName],
                [SQL#].[SQL#].RegEx_MatchSimple(@Name, '(([ ]*,+[ ]*)|([ ]+))' + suff.Val + '[.]*[ ]*$', 1, 'IgnoreCase') AS [FullMatch],
                suff.Val AS [Suffix]
        FROM    [SQL#].[SQL#].String_Split(@Suffixes, ';', 2) suff
    )
    SELECT  cte.LastName, cte.FullMatch, REPLACE(cte.LastName, cte.FullMatch, '') AS [Replacement], cte.Suffix
    FROM    cte
    WHERE   cte.FullMatch <> ''
)
GO

which can then be used as follows:

SELECT * FROM #Names a
CROSS APPLY dbo.ParseNameAndSuffix2(a.LastName, 'II;III;JR;SR') b

-- or --

UPDATE  mt
SET     mt.LastName = REPLACE(parse.LastName, parse.Found, ''),
        mt.NameSuffix = parse.Suffix
FROM    MyTable mt
CROSS APPLY dbo.ParseNameAndSuffix2(mt.LastName, 'II;III;JR;SR') parse

I think your best bet is going to be a RegEx match for the last word (excluding punctuation marks) being in a list (JR, Sr, III, etc)

Check out this blog

http://blogs.msdn.com/b/khen1234/archive/2005/05/11/416392.aspx