Sql Server 2000 Text Data Manipulation

SQL Server 2000 Text Data Manipulation

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 2000 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 2000 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 Repeats a character expression for a specified , integer_expression ) 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 11 (SQL is found in the 11 position) Server') SELECT CHARINDEX('SQL', 24 (SQL is found in the 24 position, 'Microsoft SQL Server - SQL since we started looking in position Server', 20) 20) SELECT LEFT('Microsoft SQL Microsoft SQL Server (left 20 Server - SQL Server' , 20 ) characters of the string) SELECT LEN('Microsoft SQL Server - SQL Server')

33 (total length of the string)

SELECT LOWER('Microsoft SQL microsoft sql server - sql server 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 11 (SQL is found in the 11 position) Server' ) 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 MICROSOFT SQL SERVER - SQL SERVER 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

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

Microsoft SQL Server - SQL Server (trim leading and trailing spaces)