Concatenating Numeric Fields

Friends,

I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.

SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles

Returns the sum of vehFacID & vehID. Doh!

J.H.

I think I found it...Something like this works..

SELECT *, CAST(vehFacID AS VARCHAR(4)) + '-' + CAST(vehID AS VARCHAR(10)) AS vehCombo FROM Vehicles

Is this the right way to do this?

J.H.

|||

If the vehFacId and vehId are numbers, then this is the way to go.

<stuff you can ignore if you want>

A bit nasty with the column names, I hope for your sake you don't have 3 letter abbreviations in every column (but not in your table name.) That must be hard to follow.

</stuff you can ignore if you want>

|||

Are you referring to the "veh" abbreviation? If so, why would you say it would be hard to follow? A small sample of my tables is like:

Vehicles, Departments, Facilities, Customers, etc...I use the 3 (or 4 sometimes) letter abbreviation to determine which table the field came from. I am open to hearing a better suggestion if you have one.

J.H.

|||

You know what else I am curious about is the casting. My numeric columns in this case are smallInt and can hold up to 5 digits. Is the recommendation to cast them to varchar(5) in this case?

J.H.

|||

I don't see any problem even if you cast to varchar(25), that way down the road if you happen to change the datatype from smallint to int, you don't have to worry about T-SQL code like this in various stored procs and functions.

As far as database naming conventions goes there isn't a standard. I wish Microsoft would have suggested something on MSDN.

I kind of agree with a article on aspfaq: http://www.aspfaq.com/show.asp?id=2538

|||

A little bit for the veh abbreviation. I would prefer to see vehicleId, and vehicleFaciltiyId, etc, which is easier to follow for the uninitiated (and in fact good finger exercises :)

The vehFacId was what kind of concerned me. I got this flash of:

select vehId, mak, modYr, numWhl, vehIdNum...etc.

There were a lot of these sorts of naming conventions back when names could only be 30 characters (funny how many times we hit 30, but rarely do I go over it now...) I don't like to see something that might be an issue and not say something. (hence the: <stuff you can ignore if you want> tags) Like the link to aspfaq says, it is a matter of taste, but the more clear it is, the more clear it is.

If a new person or contractor or newsgroup helper can read it and understand it, your job of naming is done right.