Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.</p> <p>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):</p> <pre><code>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' ) </code></pre> <p>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:</p> <pre><code>;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 &lt;&gt; '' </code></pre> <p>You can carry this theory over to a direct UPDATE statement:</p> <pre><code>;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 &lt;&gt; '' </code></pre> <p>You had requested this as a function so that is as follows:</p> <pre><code>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 &lt;&gt; '' ) GO </code></pre> <p>And can be used like so:</p> <pre><code>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 </code></pre> <p>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#:</p> <pre><code>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 &lt;&gt; '' ) GO </code></pre> <p>which can then be used as follows:</p> <pre><code>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 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POTSQL Statement to move Name Suffix (Jr, Sr, IV, etc) into another field
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USSolomon Rutzky
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload