September 17, 2008 at 3:15 pm
I am trying to create a new column that takes the string values of two other columns and sticks them together.
I put my formula in the "formula" box of the design view of the table in enterprise manager. Both of the two source columns are of data type varchar.
My formula uses the "&" operator, but SQL Server seems to be having problems with it. Here is my formula:
([STATE_ID] & [COUNT_ID])
I get the following error message:
Invalid operator for data type. Operator equals boolean AND, type equals varchar
What is wrong?
September 17, 2008 at 3:19 pm
The concatenation operator in SQL Server is the plus sign (+). Change your formula to ([STATE_ID] + [COUNT_ID]).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2008 at 3:21 pm
Concatenation operator in SQL Server is +, not &
September 17, 2008 at 3:57 pm
I read somewhere that SQL Server can convert strings to numbers implicitly.
Using the "+" operator is there no danger that if the string values appear numeric, SQL Server will add them together instead of combining them?
September 17, 2008 at 6:52 pm
Yes it can happen, especially if those 2 ids are in any numeric format.
What are the base datatypes of those columns?
What are you trying to accomplish with the calculated field?
You can also you cast as such : (CAST([STATE_ID] AS VARCHAR(10)) + CAST([COUNT_ID] AS VARCHAR(10))).
September 17, 2008 at 6:54 pm
Also keep in mind that if you have ids that go over 9, you can run into this situation :
4 & 44 = 444
44 & 4 = 444
If you need to split them backup later on, you can't figure out what is what (unless both codes are completely unique across both columns combined).
If that's a problem you can add a comma in the concatenation...
we'll be able to send you in the right direction as soon as we know more about your problem!
September 18, 2008 at 6:30 am
Thank you Ninja,
I did in fact have to cast the state_ID in order to stop SQl Server from adding the two values together, but it worked. The other problem you warned me about will not be an issue because the Count_ID is a fixed 3 digit number in a varchar format, with leading zeros if the value is under 99, i.e. 001,002, 010, 011, 099, 100, 101, etc.
So as long as I put the state_ID first and then the count_id, I will always get unique numbers.
September 18, 2008 at 6:45 am
... and why did you decide the create such a column in the DB / select statement?
September 18, 2008 at 6:59 am
I did not create the column, it came with the data.
I originaly thought that the count_ID (County ID) was unique, but it turns out that it was only unique in each state, therefore when i combined all the state tables into one table, i needed a new unique ID to identify counties. That is why I am combining the county ID with the state ID.
September 18, 2008 at 7:04 am
Cool, thanks for letting us know!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply