Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SQL Server Text Data Manipulation

MSSQLTips author Greg Robidoux By:   |   Read Comments (4)   |   Related Tips: More > Functions - System

Problem
Sometimes there is a need to manipulate string values using T-SQL code.  With other languages such as Visual Basic, C++, C#, VBScript, etc... there are a lot of commands at your finger tips to manipulate string values.  With SQL Server you don't have all the same options, but there are enough commands that if correctly used together can result in the same functionality you get with other programming languages. 

Solution
The following is a list of SQL Server T-SQL commands that exist to allow you to manipulate text data either from stored procedures, triggers, functions or embedded SQL code.

Command Description
CHARINDEX( findTextData, textData, [startingPosition] ) Returns the starting position of the specified expression in a character string. The starting position is optional.
LEFT( character_expression , integer_expression )
Returns the left part of a character string with the specified number of characters.
LEN( textData ) Returns integer value of the length of the string, excluding trailing blanks
LOWER ( character_expression ) Returns a character expression after converting uppercase character data to lowercase
LTRIM( textData) Removes leading blanks
PATINDEX( findTextData, textData ) Returns integer value of the starting position of text found in the string
REPLACE( textData, findTextData, replaceWithTextData ) Replaces occurrences of text found in the string with a new value
REPLICATE( character_expression , integer_expression ) Repeats a character expression for a specified number of times.
REVERSE( character_expression ) Returns the reverse of a character expression.
RTRIM( textData) Removes trailing blanks
SPACE( numberOfSpaces ) Repeats space value specified number of times
STUFF( textData, start , length , insertTextData ) Deletes a specified length of characters and inserts another set of characters at a specified starting point
SUBSTRING( textData, startPosition, length ) Returns portion of the string
UPPER( character_expression ) Returns a character expression with lowercase character data converted to uppercase.
 

Examples

The examples below are just simple SELECT statements using hard coded values.  You can use these functions when querying your tables, so you can manipulate the string values as you query your data and return the modified result.

Query Value
SELECT CHARINDEX('SQL', 'Microsoft SQL Server - SQL Server') 11 (SQL is found in the 11 position)
SELECT CHARINDEX('SQL', 'Microsoft SQL Server - SQL Server', 20) 24 (SQL is found in the 24 position, since we started looking in position 20)
SELECT LEFT('Microsoft SQL Server - SQL Server' , 20 ) Microsoft SQL Server (left 20 characters of the string)
SELECT LEN('Microsoft SQL Server - SQL Server') 33 (total length of the string)
SELECT LOWER('Microsoft SQL Server - SQL Server') microsoft sql server - sql server (string in lower case)
SELECT LTRIM( ' Microsoft SQL Server - SQL Server ') Microsoft SQL Server - SQL Server (trimmed string removing leading spaces)
SELECT PATINDEX( '%SQL%', 'Microsoft SQL Server - SQL Server' ) 11 (SQL is found in the 11 position)
SELECT REPLACE( 'Microsoft SQL Server - SQL Server', 'Server', 'Server 2005' ) Microsoft SQL Server 2005 - SQL Server 2005 (string after we replace 'Server' with 'Server 2005')
SELECT REPLICATE( 'x' , 10 ) xxxxxxxxxx (x replicated 10 times)
SELECT REVERSE( 'Microsoft SQL Server' ) revreS LQS tfosorciM (string in reverse)
SELECT RTRIM( ' Microsoft SQL Server - SQL Server ')  Microsoft SQL Server - SQL Server (string after removing trailing spaces)
SELECT 'Microsoft' + SPACE(10) + 'SQL Server' Microsoft          SQL Server (string after inserting 10 spaces)
SELECT STUFF( 'Microsoft SQL Server', 11 , 3 , '2005' ) Microsoft 2005 Server (string after replacing positions 11, 12, 13 with '2005')
SELECT SUBSTRING( 'Microsoft SQL Server', 1, 9 ) Microsoft (substring of statement starting at position 1 for 9 characters)
SELECT UPPER('Microsoft SQL Server - SQL Server') MICROSOFT SQL SERVER - SQL SERVER (string in upper case)

In addition to using the commands by themselves you can use multiple commands at the same time to provide more meaningful results.

Query Value
SELECT LEFT('Microsoft SQL Server', CHARINDEX(' ', 'Microsoft SQL Server - SQL Server',13) - 1) Microsoft SQL (find portion of string where a space is found but starting at position 13)
SELECT LEFT('Microsoft SQL Server', CHARINDEX(' ', 'Microsoft SQL Server - SQL Server') - 1) Microsoft (find portion of string where a space is found)
SELECT LTRIM(RTRIM(' Microsoft SQL Server - SQL Server ')) Microsoft SQL Server - SQL Server (trim leading and trailing spaces)


Next Steps

  • Get familiar with these commands and how they can be used to manipulate text data
  • Look for ways of using multiple commands together to reach the desired results


Last Update: 7/25/2006


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Saturday, August 24, 2013 - 6:18:59 AM - Oluyemi Read The Tip

Sir,

I've been to use the LOWER() and UPPER() functions in SQL.  The challenge I have is to convert string data in a field submitted as e.g. "john loius" or "dennis anthony wood" to "John Loius" or "Dennis Anthony Wood", i.e. capitalising the the first letter in each name.

In MS Excel, there is a function PROPER() that does this kind of conversion.

Is there a way out in SQL to achieve this?

Thank you


Monday, February 18, 2013 - 11:51:01 PM - midhu Read The Tip

where to write this command in ms SQL server2005,

am fresher in database, am using vb2005,vb2008,vb2010

i need to write this type command in button click, is it possible?


Tuesday, February 21, 2012 - 9:18:46 AM - Greg Robidoux Read The Tip

Use the REVERSE function 

SELECT REVERSE( 'Microsoft SQL Server' )


Tuesday, February 21, 2012 - 1:14:38 AM - Pabitra Day Read The Tip

 

HOW TO REVERSE THE  WORDS WITHIN A STRING IN SQL QUAIRY.

example Given Below:

" SELECT 'My Name Is Pabitra Day'

TO

SELECT 'Day Pabitra Is Name My' "




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.