Tame Those Strings Part 8 - Quoting
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.
- Part 5 deals with STUFF.
- Part 6 works with concatenating results.
- Part 7 works with proper casing strings.
Introduction
Recently I was working with some data and I needed to build some complex strings. However there was a catch. These strings would be used for for shell commands. I ran into an interesting problem that I solved one way, and then, while digging through BOL on another matter, I found a better solution.
The Problem
What I needed to do was build a string that looked like the following:
if not exist c:\backup md c:\backup
Sounds simple, right?
Well it is. I can easily concatenate the strings I need in the following manner:
select @cmd = 'if not exist ' + @drive + ':\' + @folder + ' md ' + @drive + ':\' + @folder
Ignore the fact that I am not trimming things here to keep it simpler. In reality, you should probably trim these strings somewhere to be sure no unintended whitespace appears.
What about trying to create a folder called "My SQL Backups"?
Open a command prompt and type:
if not exist c:\My SQL Backups md c:\My SQL Backups
you will get:
'SQL' is not recognized as an internal or external command, operable program or batch file.
So what can you do? Well, one can easily enclose these items in double quotes so your OS will not complain. Now in the same command window, type this:
if not exist c:\"My SQL Backups" md c:\"My SQL Backups"
It will work.
The Second Solution
So what was my second solution? Well, while browsing BOL, I ran into the QUOTENAME function. For those of you who are not familiar with this, it is a function that takes a string and an optional enclosing character, and will return the original string enclosed by the other character.
Now it appears that this function was built to handle keywords in object names.
Why?
Because the default enclosing character is the brackets. Try this in your Query Analyzer:
declare @c char( 30) select @c = 'This is a test' select @c, quotename( rtrim( @c))
This will return the following results:
------------------------------ ---------------- This is a test [This is a test]
This is handy when using object names, like id and description, which are reserved words.
This can also be used with any character. Look at these examples.
declare @c char( 30)
select @c = 'This is a test'
select @c, quotename( rtrim( @c), '{'), quotename( rtrim( @c), '''')
This will return the following results:
-------------------- ----------------------- -----------------------------
This is a test {This is a test} 'This is a test'
As you can see, this is handy for string manipulation without having to explicitly code a long line of code. It also works great for my problem above. In my case, it shortened the line of code substantially.
select @cmd = 'if not exist ' + @drive + ':\' + quotename(@folder, '"') + ' md ' + @drive + ':\' + quotename(@folder, '"')
Conclusions
I know this is nothing earth-shattering, but it can be a useful function. And one that is not readily apparent to the casual programmer. Hopefully someone will find this useful and helpful in making their code easier to read.
As always I welcome feedback on this article using the "Your Opinion" button below. Please also
rate this article.
Steve Jones
©dkRanch.net March 2002
