bString Functions: Incrementing a Number in a Char
Derrick writes "I have a value : 'PIT000' I need to increment this value i.e. PIT001, PIT002, etc until PIT999. How do I do this using a SQL query. The next step is: once you reach PIT999, the value needs to change to PIU000!!" We certainly can do this in SQL Server and it gives us a chance to talk about some neat string functions too.
I probably shouldn't even ask this question . . . but "Which Pointy Haired Boss thought this numbering scheme up?" Actually I used to work for a consulting company that used this exact approach to number projects. And in spite of all my complaints it worked pretty well. Except I think they had a person generate the numbers. Oh well. This actually is pretty easy to do in SQL Server and along the way we'll learn some string functions.
In this article, I demonstrated a real-world solution that lets you split names into first name, middle name, last name, and suffixes. You can apply this solution to your own name dilemmas as long as you know all the anomalies that you might expect. Although this solution isn't error-proof for all possible names, it gives you a good head start for developing your own stored procedures and user-defined functions that will normalize the name data.
[@more@]The first step is to pull out the numeric portion of your "Value" (I really don't know what else to call it so I'll use "Value"). I'll use a snippet of code that looks like this:DECLARE @iNumValue int, @charOldValue char(6)
SELECT @iNumValue = CAST( SUBSTRING(ValueColumnName, 4, 3) AS Integer ),
@charOldValue = ValueColumnName
First, I declared a variable to
hold the integer part of this Value. I also captured the full Value in a
variable which we'll use below. Then I used the substring function in SQL
Server. SUBSTRING has the following format:
SUBSTRING(expression, start, length)
Substring in SQL Server works like substring in practically every other
language. You pass it a string, a starting position and how many characters you
want and it returns a substring. I'm hoping that your Values are always six
characters with the right three being numeric.
I used the CAST function to convert this string to an integer. The syntax of
the CAST function looks like this:
CAST(expression AS data_type)
CAST takes an expression and converts to the target datatype which is integer
in our case. You could also use the CONVERT function. CAST is an ANSI SQL-92
standard so I try to use it everywhere except date conversions. CONVERT does a
much better job on those.
Incrementing the numeric part of your Value is easy:
SET @iNumValue = @iNumValue + 1
I'll handle the rollover past 1000 below. Now I have to put the numeric part
back together with the alphanumeric part. Here's that code:
DECLARE @charNewValue char(6)
SET @charNewValue = LEFT(@charOldValue, 3) +
RIGHT ('000' + CAST(@iNumValue AS varchar(3), 3 )
The LEFT and RIGHT functions do
just what they do in every other language. We're starting with the three left
characters of our original string and appending the numeric portion on the
right. Our CAST function converts the numeric value back to a VARCHAR value. I
use VARCHAR because CHAR appends spaces to the end of the string. I concatenate
this with three zeroes (as a string) and take the right three characters.
So if I started with PIT002 the incremented numeric portion converted back to a
VARCHAR is '3'. I then take the RIGHT three characters of ('000' + '3') and get
'003' which is just what I wanted.
Now let's handle incrementing a character. You might write code something like
this:
SET @iNumValue = @iNumValue + 1
IF @iNumValue > 999
BEGIN
Set iNumValue = 0
-- Do the stuff here to increment the alpha part(see below)
END
-- Then put the string back together (see above)
Incrementing a string is a little trickier than incrementing
a number. You have to convert the character to ASCII, increment the value and
then convert it back to a character. Here's a piece of code to increment a
single character:
DECLARE @chOneLetter char(1)
Set @chOneLetter = 'A'
SELECT @chOneLetter = CHAR(ASCII(@chOneLetter) + 1)
This little snippet will return a 'B' in this case. The ASCII function returns
the numeric ASCII value of our variable. We add one to this value. The CHAR
function converts an ASCII value into it's equivilent character.
Of course you also have to handle the case of incrementing past Z but I'll
leave that to you. If you have SQL Server 2000 you might consider writing these
as a number of User Defined Functions. You could write a function that would
return the next Value when passed a specific value. Could you do this easier on
the client in VBScript, Java, VB, etc.? Maybe but you would have more trouble doing
it inside a transaction to insert new values. Hope this help and enjoy those
string functions :)
Transact-SQL Reference
SUBSTRING
Returns part of a character, binary, text, or image expression. For more information about the valid Microsoft® SQL Server™ data types that can be used with this function, see Data Types.
Syntax
SUBSTRING ( expression , start , length )
Arguments
expression
Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.
start
Is an integer that specifies where the substring begins.
length
Is a positive integer that specifies how many characters or bytes of the expression will be returned. If length is negative, an error is returned.
Note Because start and length specify the number of bytes when SUBSTRING is used on text data, DBCS data, such as Kanji, may result in split characters at the beginning or end of the result. This behavior is consistent with the way in which READTEXT handles DBCS. However, because of the occasional strange result, it is advisable to use ntext instead of text for DBCS characters.
Return Types
Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types.
The returned string is the same type as the given expression with the exceptions shown in the table.
|
Given expression |
Return type |
|
text |
varchar |
|
image |
varbinary |
|
ntext |
nvarchar |
Remarks
Offsets (start and length) using the ntext, char, or varchar data types must be specified in number of characters. Offsets using the text, image, binary, or varbinary data types must be specified in number of bytes.
Note Compatibility levels can affect return values. For more information about compatibility levels, see sp_dbcmptlevel.
Examples
A. Use SUBSTRING with a character string
This example shows how to return only a portion of a character string. From the authors table, this query returns the last name in one column with only the first initial in the second column.
USE pubsSELECT au_lname, SUBSTRING(au_fname, 1, 1)FROM authorsORDER BY au_lnameHere is the result set:
au_lname ---------------------------------------- - Bennet A Blotchet-Halls R CarsonC
DeFrance M del Castillo I ...Yokomoto A (23 row(s) affected)Here is how to display the second, third, and fourth characters of the string constant abcdef.
SELECT x = SUBSTRING('abcdef', 2, 3)Here is the result set:
x----------bcd (1 row(s) affected)B. Use SUBSTRING with text, ntext, and image data
This example shows how to return the first 200 characters from each of a text and image data column in the publishers table of the pubs database. text data is returned as varchar, and image data is returned as varbinary.
USE pubsSELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, SUBSTRING(pr_info, 1, 10) AS pr_infoFROM pub_infoWHERE pub_id = '1756'Here is the result set:
pub_id logo pr_info ------ ---------------------- ---------- 1756 0x474946383961E3002500 This is sa (1 row(s) affected)This example shows the effect of SUBSTRING on both text and ntext data. First, this example creates a new table in the pubs database named npr_info. Second, the example creates the pr_info column in the npr_info table from the first 80 characters of the pub_info.pr_info column and adds an ü as the first character. Lastly, an INNER JOIN retrieves all publisher identification numbers and the SUBSTRING of both the text and ntext publisher information columns.
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'npub_info') DROP TABLE npub_infoGO-- Create npub_info table in pubs database. Borrowed from instpubs.sql.USE pubsGOCREATE TABLE npub_info( pub_id char(4) NOT NULL REFERENCES publishers(pub_id) CONSTRAINT UPKCL_npubinfo PRIMARY KEY CLUSTERED, pr_info ntext NULL) GO -- Fill the pr_info column in npub_info with international data.RAISERROR('Now at the inserts to pub_info...',0,1) GO INSERT npub_info VALUES('0736', N'üThis is sample text data for New Moon Books, publisher 0736 in the pubs database')INSERT npub_info values('0877', N'üThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa')INSERT npub_info values('1389', N'üThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs da')INSERT npub_info values('9952', N'üThis is sample text data for Scootney Books, publisher 9952 in the pubs database')INSERT npub_info values('1622', N'üThis is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d')INSERT npub_info values('1756', N'üThis is sample text data for Ramona Publishers, publisher 1756 in the pubs datab')INSERT npub_info values('9901', N'üThis is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G i')INSERT npub_info values('9999', N'üThis is sample text data for Lucerne Publishing, publisher 9999 in the pubs data')GO-- Join between npub_info and pub_info on pub_id.SELECT pr.pub_id, SUBSTRING(pr.pr_info, 1, 35) AS pr_info, SUBSTRING(npr.pr_info, 1, 35) AS npr_infoFROM pub_info pr INNER JOIN npub_info npr ON pr.pub_id = npr.pub_idORDER BY pr.pub_id ASCSee Also
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_6btz.asp
String Manipulations with SQL Server 2000
String manipulations are an inherent part of any programming language. A majority of well-to-do companies collect transactional data and then want to see nicely formatted reports. Sometimes, the format of the data in the database isn't exactly "pretty"—it needs to be manipulated in some way before it is presentable to the business users. For instance, suppose you need a report of your company's employees' work schedules. If your database is normalized, your employee table probably contains the employees' last names, first names, prefixes, suffixes, and titles in separate columns, as it should. Reports, on the other hand, need to have the full name in a single field.
In data warehousing environments, you gather the data that resides in various storage systems, and give it a common shape inside the data warehouse. More often than not, you have to get the data from sources that don't have a normalized format. For instance, it is not uncommon to scrape the online reports to gather the data because that's your only source of such data.
Other times, the format of your database will be different from the format of your other data sources. For instance, if you collect data from Excel spreadsheets, they're not likely to contain normalized data. The spreadsheets usually have quarters, years, months, and weeks all in one field; whereas your normalized database have to keep such data in separate columns.
From previous examples, it should be clear that there is much need for manipulating your string data. Fortunately, Microsoft SQL Server 2000 provides a number of functions that help you along the way. This article introduces you to many string functions available in SQL Server and gives you an example of how you can apply these functions in your code.
Just like any other programming language, Transact-SQL supports retrieving portions of the string. For instance, to retrieve the first few characters from the left of the string, you use the LEFT function. The following example retrieves the first three letters of the employees' last names in the Northwind database:
SELECT LEFT(LastName, 3) AS FirstThreeLettersOfLastName FROM Employees
Results:
FirstThreeLettersOfLastName
---------------------------
Buc
Cal
Dav
Dod
Ful
Kin
Lev
Pea
Suy
Similarly, the RIGHT function lets you retrieve the portion of the string starting from the right. The following example retrieves the first two characters from the employees' last names, starting from the right:
SELECT RIGHT(LastName, 2) AS LastTwoLettersOfLastName
FROM Employees
Results:
LastTwoLettersOfLastName
------------------------
an
an
io
th
er
ng
ng
ck
ma
Notice that the RIGHT and LEFT functions don't check for blank characters. In other words, if your string contains a couple of leading blanks, the LEFT(string_variable, 2) will return you two blank spaces, which might not be exactly what you want. If your data needs to be left-aligned, you can use the LTRIM function, which removes the leading blanks. For instance, the following UPDATE statement will left-align (remove any number of leading blanks) the last names:
UPDATE Employees SET LastName =
LTRIM(LastName)
Similarly, if your data is padded with spaces, and you don't want to see spaces in your output, you can use the RTRIM function. For instance, suppose you have a variable that's 20 characters long, but the last two characters are blank. The following queries show what happens when you run the RIGHT function on such a variable before and after removing the trailing blanks:
DECLARE @string_var VARCHAR(20)
SELECT @string_var = 'my string variable '
SELECT RIGHT(@string_var, 2) AS BeforeRemovingTrailingSpaces
SELECT RIGHT(RTRIM(@string_var), 2) AS AfterRemovingTrailingSpaces
Results:
BeforeRemovingTrailingSpaces
----------------------------
AfterRemovingTrailingSpaces
---------------------------
le
At times, you might want to retrieve part of the string that does not necessarily start at the first character from the left or right. In such cases, the SUBSTRING function is your friend. It retrieves the portion starting at the specified character and brings back the number of characters specified; the syntax is SUBSTRING(string_variable, starting_character_number, number_of_characters_to_return). The following example will retrieve four characters from the employees' last names, starting at the third character:
SELECT SUBSTRING(LastName, 3, 4) AS PortionOfLastName FROM Employees
Results:
PortionOfLastName
-----------------
chan
llah
voli
dswo
ller
ng
verl
acoc
yama
Notice that the SUBSTRING function finds the starting character by counting from the left. In other words, if you run SUBSTRING(LastName, 3, 4) against the last name of "Buchanan", you start on the third character from the left—"c".
What if you want to start from the right side, you ask? Fortunately, there is a string function called REVERSE that gives you a mirror image of the given string. Check out the mirror image of Northwind employees' last names:
SELECT REVERSE(LastName) AS MirrorImage FROM Employees
Results:
MirrorImage --------------------
nanahcuB
nahallaC
oilovaD
htrowsdoD
relluF
gniK
gnilreveL
kcocaeP
amayuS
This way, if you want to use the SUBSTRING function starting from the right, you can use the combination of the REVERSE and SUBSTRING functions, as follows:
SELECT SUBSTRING(REVERSE(LastName), 3, 4) AS PortionOfLastNameMirrorImage
FROM Employees
Results:
PortionOfLastNameMirrorImage
----------------------------
nahc
hall
lova
rows
lluF
iK
ilre
ocae
ayuS
Similarly, if you want to see a mirror image of the portion, you can use REVERSE to reverse the result of the SUBSTRING, as follows:
SELECT REVERSE(SUBSTRING(LastName, 3, 4)) AS MirrorImageOfPortion
FROM Employees
Results:
MirrorImageOfPortion
--------------------
nahc
hall
ilov
owsd
rell
gn
lrev
coca
amay
You often need to find an occurrence of a particular character or number of characters inside a string. For example, you might want to find a position of a comma inside last names if they contain a last name and a suffix, separated by a comma.
The following example shows how this can be achieved using the CHARINDEX function:
DECLARE @string_var VARCHAR(20)
SELECT @string_var = 'Brown, Jr. '
SELECT CHARINDEX( ',', @string_var) AS comma_position
Results:
comma_position
--------------
6
The PATINDEX function is very similar to CHARINDEX in the way it works—it also finds the position of the first occurrence of a character or multiple characters. The difference is that you have to append % wildcards to PATINDEX, and it searches for a pattern. If you use a % wildcard with CHARINDEX, you won't find anything unless your data contains percent signs. If you're searching for a pattern at the end of the string expression, you only have to use the % wildcard at the beginning of the pattern to be found, as in PATINDEX ('%pattern_to_find', string_expression).
An example of using PATINDEX is provided in the following code:
DECLARE @companyName VARCHAR(20), @pattern_position INT
SELECT @CompanyName = 'Green & Waldorf'
SELECT @pattern_position = PATINDEX('%Wal%', @CompanyName)SELECT @pattern_position
Result:
-----------
9
Occasionally, you might need to replace some characters inside a string. For instance, suppose you're designing a report of employee titles, and you want to use the 'Customer Service' phrase instead of 'Sales' in titles. However, other reports still need to show the regular titles. No need to worry—the REPLACE function is here to help, as the following example demonstrates:
SELECT REPLACE(Title, 'Sales', 'Customer Service') AS ManipulatedTitle, Title
FROM Employees
Results:
|
ManipulatedTitle |
Title |
|
Customer Service Representative |
Sales Representative |
|
Vice President, Customer Service |
Vice President, Sales |
|
Customer Service Representative |
Sales Representative |
|
Customer Service Representative |
Sales Representative |
|
Customer Service Manager |
Sales Manager |
|
Customer Service Representative |
Sales Representative |
|
Customer Service Representative |
Sales Representative |
|
Inside Customer Service Coordinator |
Inside Sales Coordinator |
|
Customer Service Representative |
Sales Representative |
This example was relatively simple because you knew exactly what sequence of characters you wanted to replace. What if you only know the position of the characters? Suppose that you have some clients who contain ampersands (&) in their names, and your reporting tool cannot handle special characters such as ampersands. The STUFF function can help you replace such special characters with their equivalent expressions.
You saw how to find the position of a specific character or number of characters using CHARINDEX. Now, you can apply that knowledge and use the STUFF function to replace characters based on their position.
The following example determines the position of the offending character (&) in the string variable and then replaces it with 'AND':
DECLARE @CompanyName VARCHAR(20), @amp_position INT
SELECT @CompanyName = 'Green & Waldorf'
SET @amp_position = CHARINDEX( '&', @CompanyName)
SELECT @CompanyName = STUFF(@CompanyName,
@amp_position, 1, 'AND')
SELECT @CompanyName AS CompanyName
Results:
CompanyName --------------------
Green AND Waldorf
Another common need is finding the length of the character string or some portions thereof. For instance, you might have a need to replace leading spaces with zeros in some character columns. The number of zeros you need depends on how many spaces each column contains, which can vary from one row to the next. To find out how many leading spaces you have, you can use the LEN function, as the following example demonstrates:
DECLARE @AlphaCode VARCHAR(10)
SELECT @AlphaCode = ' AB03543'
SELECT LEN(@AlphaCode) - LEN(LTRIM(@AlphaCode)) AS NumberOfLeadingSpaces
Results:
NumberOfLeadingSpaces
---------------------
2
Next, to replace the leading spaces, you can use the combination of the REPLACE and REPLICATE functions. You've already seen the REPLACE function in action. The REPLICATE function simply prints a character or a number of characters as many times as you specify, as follows:
SELECT REPLICATE('MyCoolString', 5)
Result:
------------------------------------------------------------
MyCoolStringMyCoolStringMyCoolStringMyCoolStringMyCoolString
To replace the leading spaces with zeros, you simply replicate the '0' string times the number of leading spaces in your column:
DECLARE @AlphaCode VARCHAR(10)
SELECT @AlphaCode = ' AB03543'
SELECT @AlphaCode = REPLICATE('0', LEN(@AlphaCode) - LEN(LTRIM(@AlphaCode))) + LTRIM(@AlphaCode)
SELECT @AlphaCode AS NewAlphaCode
Result:
NewAlphaCode
------------
00AB03543
Notice that unlike Visual Basic and some other programming languages, string concatenation in Transact-SQL is accomplished with a plus (+) sign rather than an ampersand (&).
Another specific string function you might want to be aware of is SPACE, which works exactly like REPLICATE, except it takes a single parameter. The parameter specifies how many spaces you want printed:
SELECT SPACE(12) AS Spaces
Result:
Spaces ------------
For reporting purposes, you also might have to change the case of your output. This is a simple task using the UPPER and LOWER functions. For example, the following query will return the employees' last and first names in mixed case:
SELECT UPPER(LEFT(FirstName, 1)) + LOWER(SUBSTRING(FirstName, 2, (LEN(FirstName) - 1))) + ' '
+ UPPER(LEFT(LastName, 1)) + LOWER(SUBSTRING(LastName, 2, (LEN(LastName) - 1)))
AS FullName
FROM Employees
Results:
FullName ---------------------------------
Nancy Davolio
Andrew Fuller
Janet Leverling
Margaret Peacock
Steven Buchanan
Michael Suyama
Robert King
Laura Callahan
Anne Dodsworth
In some cases, you might wish to see the ASCII representation of your characters. You'll use the ASCII function more often when comparing characters without knowing whether they're in upper- or lowercase. Keep in mind that uppercase and lowercase letters translate into different ASCII values, as the following example shows:
SELECT ASCII('W') AS UpperCase, ASCII('w') AS LowerCase
Results:
UpperCase LowerCase
----------- ----------- 87 119
The UNICODE function works just like ASCII, except it accepts the unicode character value as input. This can be useful if you're working with international character sets.
Another useful function is CHAR. Although it's difficult to think of a business example when you need to see some weird characters on the report, it's often necessary to append a carriage return, line feed, or both to your output. In such cases, you can effectively use the CHAR function, as follows:
SELECT 'My Output' + CHAR(10) + CHAR(13)
+ 'AnotherOutput'
Results:
------------------------
My Output
AnotherOutput
The NCHAR function works exactly like CHAR, except it returns the unicode character.
The QUOTENAME function is useful when working with database object names that contain spaces and reserved words. Generally, it's a bad idea to use reserved words, special characters, and spaces inside your object names. However, if you're working with tables imported from data sources other than SQL Server, or if you inherit a database from you predecessor colleagues, you might not have a choice. The QUOTENAME function is actually very simple—it appends square brackets to the beginning and end of the string expression, and therefore makes such an expression a valid SQL Server identifier. The following example creates a temporary table with the column name that contains spaces:
CREATE TABLE #temp (id_column INT NULL)
DECLARE @column_name VARCHAR(50), @sql_string VARCHAR(200)
SELECT @column_name = QUOTENAME('invalid column name') SELECT @sql_string = 'ALTER TABLE #temp ADD ' + @column_name
+ 'VARCHAR(50)'
EXEC (@sql_string)
SELECT * FROM #temp
Results:
id_column invalid column name
----------- --------------------------------------------------
The STR function can be considered as a special case of the CAST or CONVERT functions, both of which let you convert the variable from one data type into another compatible datatype. As the name implies, the STR function converts an integer (or a decimal) value into a string. The nice part about the STR function is that it lets you specify the length of the string variable returned, as well as how many decimal points to include in the string variable. For instance, the following example converts a decimal value into a string expression and rounds one decimal place:
SELECT STR(1.2546, 6, 3)
Result:
------
1.255
The other two string functions available in SQL Server are SOUNDEX and DIFFERENCE, which happen to be rather similar. I've also found them to be extremely difficult to use. SOUNDEX provides a numeric representation of the string, and is supposed to help you determine whether two strings sound alike. DIFFERENCE, on the other hand, will provide you with a degree of similarity (or lack thereof) between two character expressions. If the SOUNDEX values are the same for the two strings passed to the DIFFERENCE function, the degree of similarity is the highest: 4. Otherwise, the DIFFERENCE function will return 3, 2, 1, or 0. The DIFFERENCE function can be used when you wish to find all customers with a name that sounds similar to a known value, as in the following example:
SELECT ContactName FROM customers WHERE DIFFERENCE (ContactName, 'ana') > 2
Results:
ContactName ------------------------------
Ana Trujillo
Antonio Moreno
Hanna Moos
Janine Labrune
Ann Devon
Aria Cruz
Lino Rodriguez
Annette Roulet
John Steel
Jaime Yorres
Jean Fresnière
Simon Crowther
Rene Phillips
Summary
In this article, I gave you an introduction to all string functions available with SQL Server 2000. The next article will dissect a real-world example of applying these string functions to solve a business problem.
Normalizing Name Data in SQL Server
My article titled "String Manipulations with SQL Server 2000" gives a quick overview of all string functions supported in Microsoft SQL Server 2000. Now it's time to apply that knowledge into a real-world example. The business case this time is a text file or any other type of denormalized data that contains individuals' names. Your goal is to load this data into a normalized table that contains peoples' first names, middle names, last names, and suffixes. Please refer to the string manipulations article for a description and examples of all functions used in this article.
First, let's look at the data that we have to work with. Splitting names is challenging because they don't follow any rules—they're specific to an individual. For instance, some people use only their middle initial instead of a full middle name. Others don't have a middle name at all. Still others use their first initial and a middle name. There might be initials that are spelled with a period (as in Jane M. Smith), as well as the ones that are spelled without a period (L Brian Brown). Suffixes complicate the matter even further; you can expect one of the suffixes shown in the following table:
|
Suffix |
Meaning of Suffix |
|
Jr. |
Junior |
|
Sr. |
Senior |
|
II |
Second |
|
III |
Third |
|
IV |
Fourth |
|
M.D. |
Medical Doctor |
In addition, some people have a double suffix, as in Gerhard J. Volzkoff, Jr., M.D. Suffixes can be separated from names by a space, comma, or period.
NOTE
Names are particularly specific to regions. In some countries, the individuals' names might consist of four, five, or even more names. For the sake of simplicity, we will only discuss getting the first, middle, and last names along with the suffix.
The following script creates the temporary table containing the data we'll manipulate:
CREATE TABLE #names (
name_id INT IDENTITY(1, 1) NOT NULL,
full_name VARCHAR(110) NULL,
first_name VARCHAR(30) NULL,
middle_name VARCHAR(30) NULL,
last_name VARCHAR(30) NULL,
suffix VARCHAR(20) NULL
)
INSERT #names (full_name) VALUES (
'Jane M. Smith')
INSERT #names (full_name) VALUES (
'L Brian Brown')
INSERT #names (full_name) VALUES (
' Gerhard J. Volzkoff, Jr., M.D. ')
INSERT #names (full_name) VALUES (
' John J. Walters, Sr. ')
INSERT #names (full_name) VALUES (
'James P. McPherson III')
INSERT #names (full_name) VALUES (
' L. M Kim ')
INSERT #names (full_name) VALUES (
' Richard B. Johnson, IV ')
INSERT #names (full_name) VALUES (
'Karen Hornsby')
INSERT #names (full_name) VALUES (
'Bill Stanford JR')
INSERT #names (full_name) VALUES (
'I B. Prufen')
INSERT #names (full_name) VALUES (
'Erika L Grey')
INSERT #names (full_name) VALUES (
'J. B. Edwards')
Notice that I tried to populate the temporary table with a variety of names. Besides each name being different in its "architecture", some of the names also contain leading and trailing spaces.
As a first step, let's get rid of leading and trailing spaces so we don't see some odd results in our queries. The following UPDATE statement will do the job.
UPDATE #names SET full_name = LTRIM(RTRIM(full_name))
Next, let's try to grab all the suffixes and put them in the suffix column. Some of the suffixes will have two characters ('jr', 'sr', 'II', 'IV', 'md'). For names having such suffixes, you can grab the last two letters from the end and consider them a suffix; as long as the third character from the right is a space, comma, or a period.
Some other suffixes will be three characters long ('III', 'jr.', 'sr.'). The logic is similar: You check for three rightmost characters and ensure that the forth character from the right is a one of the suffix delimiters. Finally, the 'm.d.' suffix is four characters long. Of course, there can be many other suffixes that you can check for: 'Ph.D', 'P.C.', and so forth. The principles discussed in this article will apply to any suffix. Due to the differences in suffixes, we could run a couple of different UPDATE statements to catch all of these anomalies. However, there is a better way—a single UPDATE query with a CASE statement shown below. See inline comments for a brief explanation of what each portion of CASE statement is doing:
UPDATE #names
SET Suffix = CASE
/* take care of 2 letter suffixes */
WHEN RIGHT(RTRIM(full_name), 2) IN ('jr', 'sr', 'ii', 'md', 'iv') AND SUBSTRING(REVERSE(full_name), 3, 1) IN (' ', ',', '.') THEN RIGHT(full_name, 2) /* take care of 3 letter suffixes */
WHEN RIGHT(RTRIM(full_name), 3) IN ('iii', 'jr.', 'sr.') AND SUBSTRING(REVERSE(LTRIM(RTRIM(full_name))), 4, 1) IN (' ', ',', '.') THEN RIGHT(full_name, 3) /* take care of 4 letter suffixes */
WHEN RIGHT(RTRIM(full_name), 4) = 'm.d.' AND SUBSTRING(REVERSE(full_name), 5, 1) IN (' ', ',', '.') THEN RIGHT(full_name, 4) /* if none of the above statements got the suffix, then the name
does not have a suffix. If so, leave suffix as null. */ELSE NULLEND
Now, you've taken care of all suffixes except for the case when a person has a double suffix. Looking at my data, the only time I can expect a double suffix is when a person happens to be a medical doctor. Therefore, let's check and see if any of the M.D.s have additional suffixes. Again, the CASE statement helps us catch all possible suffixes:
UPDATE #names
SET Suffix = CASE
WHEN full_name LIKE '%m.d.%' AND /* check for a dual suffix of 'jr' */
full_name LIKE '%jr%' THEN 'Jr., ' + Suffix WHEN full_name LIKE '%m.d.%' AND /* check for a dual suffix of 'sr' */
full_name LIKE '%sr%' THEN 'Sr., ' + Suffix WHEN full_name LIKE '%m.d.%' AND
/* check for a dual suffix of 'III' */
full_name LIKE '%iii%' THEN 'III, ' + Suffix WHEN full_name LIKE '%m.d.%' AND /* check for a dual suffix of 'IV' */
full_name LIKE '%iv%' THEN 'IV, ' + Suffix
ELSE Suffix
END
Now take a look at the suffix column along with the name and see how we did:
SELECT full_name, suffix FROM #names
Results:
|
Full_name |
suffix |
|
Jane M. Smith |
NULL |
|
L Brian Brown |
NULL |
|
Gerhard J. Volzkoff, Jr., M.D. |
Jr., M.D. |
|
John J. Walters, Sr. |
Sr. |
|
James P. McPherson III |
III |
|
L. M Kim |
NULL |
|
Richard B. Johnson, IV |
IV |
|
Karen Hornsby |
NULL |
|
Bill Stanford JR |
JR |
|
I B. Prufen |
NULL |
|
Erika L Grey |
NULL |
|
J. B. Edwards |
NULL |
Things are going well in the suffix department, so let's move on to the first names. As mentioned earlier, some first names consist of a single letter— a first initial that might be followed by a period. Let's populate the first_name column with such names. To do so, simply check the second character of the full_name: If it is blank or a period, the first name is one character long:
UPDATE #names
SET First_name = CASE
WHEN CHARINDEX('.', full_name) = 2 THEN SUBSTRING(full_name, 1, (CHARINDEX('.', full_name)-1)) WHEN CHARINDEX(' ', full_name) = 2 THEN SUBSTRING(full_name, 1, (CHARINDEX(' ', full_name)-1))
END
Finding the rest of the first names is easy. Simply grab the portion of the full name before the first space. Ensure that you update only the first_name columns that are NULL, and that the second character is not a space or period:
UPDATE #names
SET First_name = SUBSTRING (full_name, 1, (CHARINDEX(' ', full_name) - 1)) WHERE first_name IS NULL
AND
SUBSTRING(full_name, 2, 1) NOT IN ('.', ' ')
Now that we're done with the easy parts, let's move on to something more involved. Middle names are somewhat more difficult because they're not on the extreme left or extreme right. Therefore, we need to work with all possible variations of first names, suffixes, and delimiters used in the name.
The middle name dilemma can be resolved if we put a little thought into it. There are several different combinations that can occur, as shown in the following table:
|
First name |
Middle name |
Format of the full name |
|
Initial with a period |
Initial with a period |
'%.%.%' |
|
Initial without a period |
Initial without a period |
'% % %' |
|
Initial with a period |
Initial without a period |
'%.% %' |
|
Initial without a period |
Initial with a period |
'% %.%' |
|
Name |
Initial with a period |
'% %.%' |
|
Name |
Initial without a period |
'% % %' |
|
Initial with a period |
Name |
'%.% %' |
|
Initial without a period |
Name |
'% % %' |
|
Name |
Name |
'% % %' |
So there are really four combinations of periods and spaces to check for. Because names with periods are also delimited with spaces, it really boils down to three cases, as shown in the following query:
UPDATE #names
SET Middle_name = CASE
WHEN full_name NOT LIKE '%.%.%' AND CHARINDEX('.', full_name) <> 0 AND CHARINDEX(' ', full_name) <> 0 AND CHARINDEX('.', full_name) > CHARINDEX(' ', full_name)
THEN SUBSTRING(full_name, (CHARINDEX(' ', full_name) + 1),
((CHARINDEX('.', full_name) - CHARINDEX(' ', full_name)-1))) WHEN full_name NOT LIKE '% % %' AND CHARINDEX('.', full_name) <> 0 AND CHARINDEX(' ', full_name) <> 0 AND CHARINDEX(' ', full_name) > CHARINDEX('.', full_name)
THEN SUBSTRING(full_name, (CHARINDEX('.', full_name) + 1),
((CHARINDEX(' ', full_name) - CHARINDEX('.', full_name)-1))) WHEN full_name LIKE '% % %'THEN SUBSTRING(full_name, (CHARINDEX(' ', full_name) + 1), CHARINDEX(' ', (SUBSTRING(full_name, (CHARINDEX(' ', full_name) + 1), (LEN(full_name) - CHARINDEX(' ', full_name))))))ELSE NULL
END
WHERE Middle_name IS NULL
I realize that the above query might be overwhelming, so let's dissect it one step at a time. If a period occurs before the space (CHARINDEX(' ', full_name) > CHARINDEX('.', full_name) ), we have a case with a first initial with a period and the middle initial without a period. If so, we need to grab the portion from the period to the space, after chopping off the first initial. If the space occurs before the period, we have a first initial with no period and the middle initial with a period. In that situation, we need to grab the portion of the full name from space to period after chopping off the part before the first space. Finally, in the case of two periods or two spaces delimiting first and middle names, we can grab the portion from the first space to the second, again after chopping off the part before the first space. Execute a single SELECT statement against each SUBSTRING function mentioned in the above query to get a better understanding of how this all fits together.
The last part is getting the last names. This might seem like an awefully difficult task because we have to go through the first name, middle name, and any delimiters to get to the last name. However, recall that the REVERSE function, let's see the mirror image of the string. So for the names without a suffix, finding the last name is easy. All you have to do is grab the portion of the reversed string up to the first blank:
UPDATE #names SET Last_name = LTRIM(REVERSE(SUBSTRING(REVERSE(full_name), 1, CHARINDEX(' ', REVERSE(full_name))) ))
WHERE Suffix IS NULL
What about the ones with the suffixes? Well, fortunately we already have the suffixes in the suffix column. Therefore, all we have to do is remove those suffixes from the full name column and then find the first occurrence of the space in the reversed full name. Check out the query that finds the last name for the persons with suffixes:
UPDATE #names SET Last_name = LTRIM(REVERSE(SUBSTRING(REVERSE(
RTRIM(SUBSTRING(full_name, 1, (CHARINDEX(Suffix, full_name)-1))))
, 1, CHARINDEX(' ', REVERSE( RTRIM(SUBSTRING(full_name, 1, (CHARINDEX(Suffix, full_name)-1))))
))))
FROM #names
WHERE Suffix IS NOT NULL
Now we can examine our data table again and see if it needs further polishing:
SELECT * FROM #names
Results:
|
full_name |
First_name |
middle_name |
last_name |
suffix |
|
Jane M. Smith |
Jane |
M |
Smith |
NULL |
|
L Brian Brown |
L |