SQL concat

What's the operator for string concatenation in SQL server - I'm trying: SET txtColumnName1 = txtColumnName1 + '#form.txtFormField1#' And that's throwing me an "Invalid operator for data type" error. Anyone help me out right quick? Adam.

That should work assuming your datatypes work together. HEre's another example 'The price is ' + CAST(price AS varchar(12)) You may need to break out CONVERT() instead of CAST() E What's the operator for string concatenation in SQL server - I'm trying: SET txtColumnName1 = txtColumnName1 + '#form.txtFormField1#' And that's throwing me an "Invalid operator for data type" error. Anyone help me out right quick? Adam.

> What's the operator for string concatenation in SQL server - I'm trying: || isn't it ? -- Tom C "Land of the free, home of the brave... you have to be brave to live there and enjoy the freedoms"

Hi, In a simple select query where the datatypes are the same you can use this: SELECT (au_lname + ', ' + au_fname) AS Name FROM authors ORDER BY au_lname ASC, au_fname ASC If the datatypes are different you need to use cast: SELECT 'The order date is ' + CAST(ord_date AS varchar(30)) as display_date FROM sales WHERE ord_num = 'A2976' ORDER BY ord_num You can also use Convert() instead of Cast() Hope it helps, Mario What's the operator for string concatenation in SQL server - I'm trying: SET txtColumnName1 = txtColumnName1 + '#form.txtFormField1#' And that's throwing me an "Invalid operator for data type" error. Anyone help me out right quick? Adam.

They're the same type of data - the column data type is ntext. I've even tried this and still get "Invalid operator for data type. Operator equals add, type equals ntext": SET txtColumnName1 = Cast(txtColumnName1 AS ntext) + Cast('#form.txtFieldName1#' AS ntext) Adam. ----- Excess quoted text cut - see Original Post for more -----

I've also tried: txt_description = {fn CONCAT(txtColumnName1, '#form.txtFieldName1#')} it gives a similar error. I guess you just can't concatenate to this data type? Adam. ----- Excess quoted text cut - see Original Post for more -----

Adam, Here is a piece of the SQL 7.0 Books Online documentation about concatenation: + (String Concatenation) (T-SQL) An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator). Syntax expression + expression Arguments expression Is any valid Microsoft® SQL Server(tm) expression of any of the data types in the character and binary data type category, except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression. It seems as though you cannot do it with a image, ntext or text data type. Mario I've also tried: txt_description = {fn CONCAT(txtColumnName1, '#form.txtFieldName1#')} it gives a similar error. I guess you just can't concatenate to this data type? Adam. ----- Excess quoted text cut - see Original Post for more -----

You could try to Cast/Convert the ntext to varchar.  Might be tricky because of the length of the ntext field but it is worht a try. Mario I've also tried: txt_description = {fn CONCAT(txtColumnName1, '#form.txtFieldName1#')} it gives a similar error. I guess you just can't concatenate to this data type? Adam. ----- Excess quoted text cut - see Original Post for more -----

Yeh, that was it - I changed the datatype and now works fine. Thanks. Adam. ----- Excess quoted text cut - see Original Post for more -----

Adam, You could also try this is if you need the ntext data type: SELECT (cast(reaction_comments AS varchar) + '-------' + cast(attend_suggestions AS varchar)) AS test_concat FROM tbl_USER_SURVEY. The fields where text fields but it should work for ntext also. mario Yeh, that was it - I changed the datatype and now works fine. Thanks. Adam. ----- Excess quoted text cut - see Original Post for more -----

You might need to use #preserveSingleQuotes(form.txtFormField1)# ô¿ô Tony That should work assuming your datatypes work together. HEre's another example 'The price is ' + CAST(price AS varchar(12)) You may need to break out CONVERT() instead of CAST() E What's the operator for string concatenation in SQL server - I'm trying: SET txtColumnName1 = txtColumnName1 + '#form.txtFormField1#' And that's throwing me an "Invalid operator for data type" error. Anyone help me out right quick? Adam.

Question: I have video, and image library on our intranet and we want to have a hyperlink that will allow the user to download the file to their hard drive without having right click and select file save as. Is there an easy way to do this? Thanks, Brad Fetter webmaster@flir.com You might need to use #preserveSingleQuotes(form.txtFormField1)# ô¿ô Tony That should work assuming your datatypes work together. HEre's another example 'The price is ' + CAST(price AS varchar(12)) You may need to break out CONVERT() instead of CAST() E What's the operator for string concatenation in SQL server - I'm trying: SET txtColumnName1 = txtColumnName1 + '#form.txtFormField1#' And that's throwing me an "Invalid operator for data type" error. Anyone help me out right quick? Adam.

I believe that's the ansi SQL-92 standard, but in MSSQL it's +. My problem was that the datatype was ntext, which it appears MSSQL doesn't allow you to concatenate. This is just based on my experience from yesterday, I wasn't able to verify in the documentation. I changed to varchar and it works fine. That particular field didn't need to be ntext, so for the sake of clarity I didn't go with casting to a different datatype. Thanks for the replies. Adam. ----- Excess quoted text cut - see Original Post for more -----