人生路漫,与你相伴...

字符串-Using STUFF
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.