Connecting string values in SQL

Posted on 2006-04-26
Last Modified: 2006-11-18
When I run a query like this:

UPDATE MyTable SET MyFld ='Some Value' + MyFld WHERE ID = 100

It works on the SQL Server 2005, but not on 2000, where I get the following error:

Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals text.

What is the correct method of connecting the string values together the SQL command ?
Question by:vpekulas
    LVL 7

    Expert Comment

    If you add () it should work, like the following:

    UPDATE MyTable SET (MyFld ='Some Value' + MyFld) WHERE ID = 100
    LVL 68

    Expert Comment

    You cannot directly UPDATE text columns in SQL 2000, you must use UPDATETEXT -- which is not that easy and requires using a cursor and looping thru the rows.

    Author Comment

    That doesn't seem to work either, when I do that I get:

    Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near '('.

    Author Comment

    :( OK, I guess that explains it .... thanks.
    LVL 75

    Accepted Solution

    You need to use Updatetext / writetext for updating a  column

    The following example gives you an example

    Update  textCol1 by appending TextColumn2 and Text Col 3

    CREATE TABLE TextExample (i int identity(1,1), text1 text, text2 text, text3 text)
    INSERT INTO TextExample SELECT REPLICATE('a',7998), REPLICATE('b',7998), NULL

    DECLARE @txtPtr1 Varbinary(16)
    DECLARE @txtPtr2 Varbinary(16)
    DECLARE @txtPtr3 Varbinary(16)

    SELECT @txtPtr1 = TEXTPTR(text1)
    FROM TextExample

    SELECT @txtPtr2 = TEXTPTR(text2)
    FROM TextExample

    UPDATE TextExample
    SET Text3 = Text1
    WHERE i = 1

    SELECT @txtPtr3 = TEXTPTR(text3)
    FROM TextExample
    WHERE i =1

    FROM TextExample
    WHERE i =1

    UPDATETEXT TextExample.Text3 @txtPtr3 NULL 0 ' '

    FROM TextExample
    WHERE i =1

    UPDATETEXT TextExample.Text3 @txtPtr3 NULL 0 TextExample.Text2 @txtPtr2

    FROM TextExample
    WHERE i =1

    Expert Comment


    First create a function on your database.The function is as follows:

    create function ConcatStrings() returns varchar(max length of your data after concatenation) as
    return (select col1+col2+...(columns you want to concatenate) from your table name where condition)

    the usage of function in a query

    update your table set col=dbo.concatstrings()

    col will be updated according to the cols specified in your function

    this function is too small but you can change its contents easily

    Expert Comment

    if you can't change the functino according to your data then tell me i will help you to update it according to your function

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now