How to do text manipulation with MSSQL

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005

Data used

USE sql911;
go

SELECT deptno AS 'Dept', deptname AS 'Department'
FROM department;
go
Dept Department
---- ------------------------------------
A00  SPIFFY COMPUTER SERVICE DIV.
B01   **PLANNING
C01  INFORMATION CENTER
D01  DEVELOPMENT CENTER
D11  MANUFACTURING SYSTEMS
D21  ADMINISTRATION SYSTEMS
E01  SUPPORT SERVICES
E11    ;;OPERATIONS
E21  SOFTWARE SUPPORT

(9 row(s) affected)

Text manipulation

SELECT LOWER(deptno) AS 'Dept',
       deptname AS 'Department',
       LTRIM(deptname) AS 'Left justified',
       REVERSE(deptname) AS 'Reversed',
       STUFF(deptname,5,0,'*;:&') AS 'Stuffed',
       LEN(LTRIM(RTRIM(deptname))) AS 'Num of chars'
FROM department;
go
Dept Department                           Left justified                       Reversed                             Stuffed                                  Num of chars
---- ------------------------------------ ------------------------------------ ------------------------------------ ---------------------------------------- ------------
a00  SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         .VID ECIVRES RETUPMOC YFFIPS         SPIF*;:&FY COMPUTER SERVICE DIV.         28
b01   **PLANNING                          **PLANNING                           GNINNALP**                            **P*;:&LANNING                          10
c01  INFORMATION CENTER                   INFORMATION CENTER                   RETNEC NOITAMROFNI                   INFO*;:&RMATION CENTER                   18
d01  DEVELOPMENT CENTER                   DEVELOPMENT CENTER                   RETNEC TNEMPOLEVED                   DEVE*;:&LOPMENT CENTER                   18
d11  MANUFACTURING SYSTEMS                MANUFACTURING SYSTEMS                SMETSYS GNIRUTCAFUNAM                MANU*;:&FACTURING SYSTEMS                21
d21  ADMINISTRATION SYSTEMS               ADMINISTRATION SYSTEMS               SMETSYS NOITARTSINIMDA               ADMI*;:&NISTRATION SYSTEMS               22
e01  SUPPORT SERVICES                     SUPPORT SERVICES                     SECIVRES TROPPUS                     SUPP*;:&ORT SERVICES                     16
e11    ;;OPERATIONS                       ;;OPERATIONS                         SNOITAREPO;;                           ;;*;:&OPERATIONS                       12
e21  SOFTWARE SUPPORT                     SOFTWARE SUPPORT                     TROPPUS ERAWTFOS                     SOFT*;:&WARE SUPPORT                     16

(9 row(s) affected)
  • lower(deptno) returns the lower case.
  • ltrim(deptname) left justifiy the character string by removing the leading spaces.
  • reverse(deptname) returns the the string left justified in reverse order.
  • stuff(deptname,5,0,'*;:&') start at character 5, delete 0 character and inserts the string *;:&
  • len(ltrim(rtrim(deptname))) return the number of characters in a string, you do not need the ltrim(rtrim()) combination on a varchar string.