Home > Articles > Data > SQL Server

String Manipulations with SQL Server 2000

String manipulations are an inherent part of any programming language. Fortunately, Microsoft SQL Server 2000 provides a number of functions that help you along the way. In this article, Baya Pavliashvili introduces you to the many string functions available in SQL Server, and gives you an example of how you can apply these functions in your code.
Like this article? We recommend

Like this article? We recommend

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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020