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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 15, 2001

Tame Those Strings Part 4 - Numeric Conversions

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.


Continuing on with taming strings...

Often when I am reporting data from SQL Server, I am concatenating information together to form more readable output. Many times this output will also include some numeric data that needs to be placed inside a string. I am not sure exactly why an integer is not implicitly converted to a string, but it doesn't work, so I have to use another solution.

The Problem

Numeric values (whether integer or float) are not implicitly converted to characters within a string concatenation statement. Instead, the following statement (using Northwind):

select customerID + 5
   from customers
returns this:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'ALFKI' to a column of data type int.
And this code:

select 5 + customerID
   from customers
still returns this:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'ALFKI' to a column of data type int.
Apparently the numeric data type takes some precendence over the character data types. In some searching of Books Online, I have not found any documentation of this.

In the past I have used CONVERT, and more recently CAST, to convert the numeric data into character data. However there are a few issues with this technique. Consider the following code:

select quantity, 
		cast( quantity as char( 1)) 'Char_quantity'
 from [order details]
This results in something that looks like:
quantity Char_quantity 
-------- ------------- 
12       *
10       *
5        5
9        9
Notice that a number of the results are not returned because the data type does not fit into the space allowed. So what can you do?

In the past, I have usually used code like the following:

select quantity, 
       rtrim( cast( quantity as varchar( 50))) 'Char_quantity'
 from [order details]
This will ensure that I get all results, but still contains some issues.

Suppose that I have a series of decimals like the following:

select 4.35,
which will return:
----- ------- ------- ------- 
4.35  12.423  234.34  6.3345
Let us apply the previous formatting to this set of data and see the results.

select rtrim( cast( 4.35 as varchar( 50))),
       rtrim( cast( 12.423 as varchar( 50))),
       rtrim( cast( 234.34 as varchar( 50))),
       rtrim( cast( 6.3345 as varchar( 50)))

------------ -------------- ----------- ----------------
4.35         12.423         234.34      6.3345
I have shortened the result set, but this results in a wide result set in Query Analyzer, though the actual strings are the proper length.

But what if I need specific formatting? What if I need a specific length for formatting columns in a report? What if I am looking for xx number of decimals? It is easy to use SUBSTRING and SPACE to trim and then pad the columns, but this is cumbersome. For the decimals, then you run into another problem.

select rtrim( cast( round( 4.35, 2) as varchar( 50))),
       rtrim( cast( round( 12.423, 2) as varchar( 50))),
	    rtrim( cast( round( 234.34, 2) as varchar( 50))),
	    rtrim( cast( round( 6.3345, 2) as varchar( 50)))

------------ ------------ ---------- ---------- 
4.35         12.420       234.34     6.3300

In this code, the numbers are rounded to the proper number of decimals, but there are still the original number of characters in the converted strings. SUBSTRING presents a problem here unless I use CHARINDEX to find the decimal and then perform the proper operations. This gets cumbersome and I decided to search for an easier solution.

The Solution

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 found STR. This is a string function that is designed to convert numeric values to characters. Let us apply this to our sample data set.

select str( 4.35, 5, 2),
       str( 12.423, 5, 2),
       str( 234.34, 5, 2),
       str( 6.3345, 5, 2)

----- ----- ----- ----- 
 4.35 12.42 234.3  6.33
This almost appears to work, but there are still some issues. Notice that in column 3, the decimals are not set to 2 because the length of the string exceeds the total length. If we adjust the query as follows:
select ltrim( str( 4.35, 25, 2)),
       ltrim( str( 12.423, 25, 2)),
       ltrim( str( 234.34, 24, 2)),
       ltrim( str( 6.3345, 25, 2))

------------- ------------- ------------ -------------- 
4.35          12.42         234.34       6.33
Now I have strings with the proper number of decimals. Of course, if I want to get each string set to the same length and right justified, I still have some formatting to do, but I will stop here for now.


I hope that I have shed some light on a little used funciton and an alternative for converting numeric values into strings. No earth shattering technical knowledge in this article, but perhaps I will spark an idea or two in some of you.

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
November 2000

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