I am a TSQL Newbie trying to concatenate two columns (DocumentNo & SequenceNo) that were created with a “smallint” data type constraint in a full-text search database.I want to end up with a column containing varchar data such as “5-2” where this row of data contains information about the 2nd document in a series for a person or group designated as 5.
If I could change the data type for the columns to varchar I think I could query them like this:
SELECT ("DocumentNo" + '-' + "SequenceNo") AS DocumentNoFull
FROM Full_Documents
ORDER BY DocumentNo, SequenceNo
When I try to concatenate with this query the result is a mathematical addition of the numbers, not what I am trying to achieve (which is to combine the two numbers to produce a text string).
Due to the full-text search parameters for the database I have not been able to modify the data type constraints on the two relevant columns.Is there a way to concatenate the two “smallint” columns and create a new column with text data (e.g., 5-2) for each row in the table?
My research suggests that “casting” could be used to convert between data types, but I have not been able to figure out how to apply it to my situation.Any help would be appreciated.
Casting should work.
It would be something like.
SELECT CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5)) AS DocumentNoFull
FROM Full_Documents
ORDER BY DocumentNo, SequenceNo
|||Hi Ryan: Thanks, that was so easy. Now I know how to cast.
How do I create a new column in the database into which the results of the query will automatically be inserted?
|||I'm not sure exactly what you mean.
Do you want to add a column to your table and populate it for all existing rows using your query? With this approach you would have to change future inserts to the table to populate this field. (Or use something like a trigger to populate it, if you don't have control of the insert statements)
Or do you want a computed column that is added to the table and then calculated based on the values in the other fields?
Can I ask why you need to add this as a column at all? Why can't you just do the concatenation in SQL when you need it?
If you really need to do either the first option or second, I can point you toward how to do it.
|||I think I want the first option. I don't foresee any additions to the database (which is based on historical records from a closed source).
I hope to be able to do full text searches in a VB application and possibly from a web form and am looking to keep things simple when I write those applications. As I get more experience I will surely become more confident in my ability to concatenate, etc. But at this point I just want to make sure I can get it to work. I can do full text searches easily from within SQL Management Studio, but have not yet been able to achieve it from Visual Basic. So I just want to eliminate as many possible sources of error until I know that I can do it all properly.
Also, I will learn to create a new column and insert data from a query (which could be useful as I progress in my TSQL education).
|||Okay. If you really want the first option.
Do something like this. For the added column you either need to allow it to be NULL or give it a default value. I went with the NULL option
ALTER TABLE Full_Documents ADD concat_col VARCHAR(15) NULL
UPDATE Full_Documents SET concat_col = CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5))
|||
Thanks Ryan. Exactly what I wanted in this instance.
Just so that I will understand my choice - would the second option have created a dynamic field that would have automatically been updated with the properly concatenated text when a new row was added? If not, what did I miss by choosing the first option?
|||Yes, that is exactly the difference. You can use what is called a computed column. From a performance standpoint, it is not usually the best idea. But, you can declare that column using a function that returns the value that you want. With this column, the concat_col would always have values associated with the other 2 columns instead of needing it to be inserted with each row.
The typical way to do this is to declare the column with a type that references a function (instead of varchar). The function would return the value that you want based on the other values in your row.
|||Thanks again Ryan.
No comments:
Post a Comment