Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 16, 2001

Tame Those Strings Part 5 - Using STUFF

By Steve Jones

This is a continuation of string manipulation techniques. If you are interested, you can read the other articles, though you do not need to read it before this one. These are mostly beginning programming articles, but advanced T-SQL programmers may still find something useful here.
  • Part 1 deals with SUBSTRING and how it can be used to extract some information from a field of data
  • Part 2 deals with CHARINDEX and how it can be used to extract some information from a field of data when the data is delimited rather than stored in a particular format.
  • Part 3 deals with REPLACE and how it can be used to remove unwanted information from a field of data when the data is not in a known format.
  • Part 4 deals with numeric conversions.

Introduction

Continuing on with taming strings...

The first three parts of this series dealt with manipulating strings in response to some business issue, usually dealing with phone numbers or zip codes. This article continues in the same vein with another alternative for dealing with the formatting issues that occur with these types of data.

The Problem

Usually I receive data in a variety of formats and need to force it into a standard format. In past articles, I have used various string techniques to remove unnecessary data and reformat the data into a standard format. A lack of validation of input data, or no control over the data being input is the cause, but it happens. However, once I remove all extraneous formatting to standardize the data, I sometimes need to add additional formating to standardize the data for a front end application(s).

Suppose that I had scrubbed and cleansed all my phone number data from Part 1 of this series to look like the following:
phone
    ----------
    6055552862
    5615552700
    9045555680
    N/A
    5805555371
    2815558368
    2545558430
    3365552797
    3365557233
    5925553181x4951
    96615551222x249
    447930555271
Now I wish to format this data so that all phone numbers are stored in the following format:
(999) 999-9999
for US phone numbers. For this article, assume that I have some method of determining which rows are US phone numbers and which are not. This leaves me with the following rows
phone
    ----------
    6055552862
    5615552700
    9045555680
    5805555371
    2815558368
    2545558430
    3365552797
    3365557233
    5925553181x4951
    96615551222x249
that I want to convert to:
phone
    ----------
    (605) 555-2862
    (561) 555-2700
    (904) 555-5680
    (580) 555-5371
    (281) 555-8368
    (254) 555-8430
    (336) 555-2797
    (336) 555-7233
    (592) 555-3181x4951
    (966) 155-51222x249

The Solution

Once again, I decided to search my handy-dandy Books Online (for those of you with young kids, you will get the joke. For the rest of you look here) in the string functions area and I decided to use STUFF. This is a string function that allows you to delete characters from a string and insert a string value into another string with control over the positioning of the insertion. The difference from REPLACE is that this function uses a position in the string to make replacement rather than a pattern.

The format for STUFF is as follows:
STUFF ( character_expression 1, start , length , character_expression 2)
where the parameters are:

  • Character_expression 1 - The string expression in which to insert data
  • Start - The start position for the insertion
  • Length - The number of characters in character_expression 1 to delete.
  • Character_expression 2 - the string to insert into character_expression 1
Normally, I have used this function in the same manner as REPLACE to remove some data from a string and add different data. However, there is a trick to using this function to convert the phone number data above.

The trick is to not delete any characters. If I use the following code:

declare @d char( 14)
select @d = '6055552862'
select stuff( @d,1 ,1,'('), @d
I get
(055552862
which is not what I want. Instead, I want to change the length parameter to a 0, so no characters are deleted. In this case, an insertion occurs with no characters being deleted. The code now looks like:
declare @d char( 14)
select @d = '6055552862'
select stuff( @d,1 , 0,'('), @d
I get
(6055552862

By expanding this to include all the characters that I need, I can reformat the entire string in one (messy) SQL statement. Here is it:
declare @d char( 14)
select @d = '6055552862'
select stuff( stuff( stuff( stuff( @d,1 ,0, '('), 5, 0, ')'), 6, 0, ' '), 10, 0, '-')
which yeilds
(605) 555-2862

Conclusion

This statement could have easily been done with the SUBSTRING command and a few concatenations. I think the STUFF command is cleaner and easier to read. Once you get used to using it's syntax. Of course, to update the table of information, I would have to be sure that all rows were in the same format or use a WHERE clause to limit the update to those rows that match the criteria.

I hope and am sure this function will really spark some ideas in many of you. Hopefully I will come in handy for you in solving some problem.

As always, I welcome feedback and please rate this article below (and any you read on Swynk). It helps to motivate and assist us authors in writing better columns.

Steve Jones
March 2001


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM