首页 > 数据库 > SQL Server > SQL Server - String Functions

SQL Server - String Functions

原创 SQL Server 作者:xxdxxdxxd 时间:2007-01-06 17:35:47 0 删除 编辑

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


Set iNumValue = 0

-- Do the stuff here to increment the alpha part(see below)


-- 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


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.


SUBSTRING ( expression , start , length )



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.


Is an integer that specifies where the substring begins.


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








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.

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 pubs
SELECT au_lname, SUBSTRING(au_fname, 1, 1)
FROM authors
ORDER BY au_lname

Here is the result set:

---------------------------------------- - 
Bennet                                   A 
Blotchet-Halls                           R 
Carson                                   C 
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:

(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 pubs
SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, 
   SUBSTRING(pr_info, 1, 10) AS pr_info
FROM pub_info
WHERE 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.

      WHERE table_name = 'npub_info')
   DROP TABLE npub_info
-- Create npub_info table in pubs database. Borrowed from instpubs.sql.
USE pubs
CREATE TABLE npub_info
 pub_id         char(4)           NOT NULL
         REFERENCES publishers(pub_id)
 pr_info        ntext             NULL
-- Fill the pr_info column in npub_info with international data.
RAISERROR('Now at the inserts to pub_info...',0,1)
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')
-- 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_info
FROM pub_info pr INNER JOIN npub_info npr
   ON pr.pub_id = npr.pub_id
ORDER BY pr.pub_id ASC
See Also

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



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



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 = 

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



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



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



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



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



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



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



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




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


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:

SELECT @AlphaCode = ' AB03543'
SELECT LEN(@AlphaCode) - LEN(LTRIM(@AlphaCode)) AS NumberOfLeadingSpaces



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:




To replace the leading spaces with zeros, you simply replicate the '0' string times the number of leading spaces in your column:

SELECT @AlphaCode = ' AB03543'
SELECT @AlphaCode = REPLICATE('0', LEN(@AlphaCode) - LEN(LTRIM(@AlphaCode))) 
+ LTRIM(@AlphaCode)
SELECT @AlphaCode AS NewAlphaCode



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:




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


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


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'


My Output

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)


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)



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


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


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:


Meaning of Suffix












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.


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:

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. 

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  

Now take a look at the suffix column along with the name and see how we did:

SELECT full_name, suffix FROM #names




Jane M. Smith


L Brian Brown


Gerhard J. Volzkoff, Jr., M.D.

Jr., M.D.

John J. Walters, Sr.


James P. McPherson III


L. M Kim


Richard B. Johnson, IV


Karen Hornsby


Bill Stanford JR


I B. Prufen


Erika L Grey


J. B. Edwards


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))  

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 
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

'% %.%'


Initial with a period

'% %.%'


Initial without a period

'% % %'

Initial with a period


'%.% %'

Initial without a period


'% % %'



'% % %'

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))))))
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 =  
CHARINDEX(' ', REVERSE(full_name)))  

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  
RTRIM(SUBSTRING(full_name, 1, (CHARINDEX(Suffix, full_name)-1)))) 
RTRIM(SUBSTRING(full_name, 1, (CHARINDEX(Suffix, full_name)-1)))) 
FROM #names

Now we can examine our data table again and see if it needs further polishing:

SELECT * FROM #names







Jane M. Smith





L Brian Brown


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量