Sunday, February 12, 2012

Concatinating SmallInt Data Type

Newbie question regarding a db I have inherited.

A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.

DocNohas numbers that represent persons. SequenceNo has numbers thatrepresent specific documents associated with each person (DocNo).

So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.

My SELECT statement looks like this:

SELECT ReadingNo, SequenceNo

This returns data like this: 5 3

I would like to concatenate the SELECT statement to return like this: 5-3

So I made Sql like this:

SELECT ReadingNo + '-" + SequenceNo

Whichreturns a alias ('No Column Named') result value of 8 which is anarithmetic result instead of a string concatination that I want.

So my questions are:

1. Should the original database designer have used string data types forthese columns since they will never be used for math purposes?

2. Do I need to cast them to string data type (like nchar(4) - sinceneither column will ever exceed 4 digits) to get the result I desire?

3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?

Select Cast(ReadingNo As VarChar(5)) + '-' + Cast(SequenceNo As VarChar(5)) As DocNo

Basically what you need to do is cast (or convert) the two columns as either char or varchar so the sql engine knows to concat instead of adding.

|||

Excellent. Thanks so much. For future reference (should I have to create such a db from scratch), should I use varchar data type in this situation? Or is there a good reason (such as file size) to use the smallint type for such data?

|||

Depends on the business needs of the colums in your database.

If you have a need for the columns as int, smallint ect then use that.

When designing databases always look at 1st what your going to do with the data you are storing to help you determine best model.

|||

Thanks again for your input.

No comments:

Post a Comment