How to concate the two strings?

What I need is to concate two strings:

Update table1
set field1 = CASE table2.flag_field WHEN 1 THEN fields1 + table2.field2 ELSE field1 END
FROM table2
WHERE table1.id = table2.id and table1.status = 1

is this statement right on concating two strings?




LVL 1
chuang4630Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Yes, it is correct
0
chuang4630Author Commented:
Complain from SQL:

Invalid operator for data type. Operator equals add, type equals text.

--> I cannot use '+'.

What should I use then?
0
Aneesh RetnakaranDatabase AdministratorCommented:
You can try it like this

BEGIN TRANSACTION

SELECT Field1 FROM TABLE1 WHERE table1.id = table2.id and table1.status = 1
Update table1
set field1 = CASE table2.flag_field WHEN 1 THEN fields1 + table2.field2 ELSE field1 END
FROM table2
WHERE table1.id = table2.id and table1.status = 1

SELECT Field1 FROM TABLE1 WHERE table1.id = table2.id and table1.status = 1

ROLLBACK TRANSACTION


once you find that it is working then you can go for the original updation, i.e. without BEGIN & ROLLBACK
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Aneesh RetnakaranDatabase AdministratorCommented:
If table2.Field2 is otherthan varchar, you should convert it to varchar

set field1 = CASE table2.flag_field WHEN 1 THEN fields1 + CAST(table2.field2 AS varchar(30)) ELSE field1 END
0
Aneesh RetnakaranDatabase AdministratorCommented:
chuang4630,
> Invalid operator for data type. Operator equals add, type equals text.

> --> I cannot use '+'.

Send me the datatypes of both the columns
0
chuang4630Author Commented:
It complains the samething.

Is there anyway that I do not have to use  '+' to concate two strings?
0
Aneesh RetnakaranDatabase AdministratorCommented:
In order to update a text field, you cannot use '+', you need to use  'UPDATETEXT' or 'WRITETEXT'..
0
chuang4630Author Commented:
field1 text
field2 varchar 255

that coud be the issue. But how do i resolve it without changing the field type?
0
HuyBDCommented:
Could you change datatype to use (+)

Update table1
set field1 = CASE table2.flag_field WHEN 1 THEN cast(subtring(fields1,1,5000) as varchar(5000))+cast(subtring(fields1,5001,10000) as varchar(5000))... + table2.field2 ELSE field1 END
FROM table2
WHERE table1.id = table2.id and table1.status = 1



0
chuang4630Author Commented:
I used Convert(varchar(8000), field1) + field2
0
Aneesh RetnakaranDatabase AdministratorCommented:

try this, this is not optimized, i code it in a hurry... Not tested also

-- create table table1 ( id int identity, Field1 text, status bit )
-- create table table2 (id int identity, Field2 varchar(255))

-- insert into table1 SELECT replicate('a',20),1
-- insert into table1 SELECT replicate('b',20),1
-- insert into table1 SELECT replicate('c',20),1
-- update table2 set flag_filed =1
--insert into table2 select ' and b'
-- insert into table2 select ' and c' ,0
-- insert into table2 select ' and d' ,1

-- Update table1
-- set field1 = CASE table2.flag_filed WHEN 1 THEN field1 + CAST(table2.field2 as TEXT) ELSE field1 END
-- FROM table2
-- WHERE table1.id = table2.id and table1.status = 1

DECLARE @field2 varchar(255)
DECLARE @flag_filed bit

DECLARE @txtPtr1 Varbinary(16)
set @txtPtr1 = 0
IF OBJECT_ID('tempdb..#tabe') IS NOT NULL
    DROP TABLE #tabe
CREATE TABLE #tabe  (i int )
declare @id int

INSERT INTO #tabe
SELECT table1.id
FROM table2, table1
WHERE table1.id = table2.id and table1.status = 1


WHILE @txtPtr1 IS NOT NULL
BEGIN
    SET @txtPtr1 = NULL
    SELECT  TOP 1 @txtPtr1 = TEXTPTR(Field1),
        @flag_filed =flag_filed,
        @field2     = Field2,
        @id = table1.id
    FROM table2, table1
    WHERE table1.id = table2.id and table1.status = 1
    AND table1.id in (SELECT I FROM #tabe )

    IF @txtPtr1 IS NOT NULL
    BEGIN
        IF @flag_filed = 1
            UPDATETEXT table1.Field1 @txtPtr1 NULL 0 @field2
        ELSE
            WRITETEXT table1.Field1 @txtPtr1 @field2
   

    END
    DELETE FROM #Tabe
    WHERE i = @id
END

DROP TABLE #tabe

0
Aneesh RetnakaranDatabase AdministratorCommented:
Ok, i've tested this Code and its working fine .....

-- create table table1 ( id int identity, Fields1 text, status bit )
-- create table table2 (id int identity, Fields2 varchar(255),flag_field bit)

-- insert into table1 SELECT replicate('a',20),1
-- insert into table1 SELECT replicate('b',20),1
-- insert into table1 SELECT replicate('c',20),1
-- insert into table1 SELECT replicate('d',20),1
-- insert into table1 SELECT replicate('e',20),1
-- insert into table1 SELECT replicate('f',20),1
-- insert into table2 select ' and b',1
-- insert into table2 select ' and c' ,0
-- insert into table2 select ' and d' ,1
-- insert into table2 select 'and no',1

SET NOCOUNT ON

BEGIN TRAN
SELECT * from table1

DECLARE @Fields2 varchar(255)
DECLARE @flag_field bit

DECLARE @txtPtr1 Varbinary(16)
set @txtPtr1 = 0
IF OBJECT_ID('tempdb..#tabe') IS NOT NULL
    DROP TABLE #tabe
CREATE TABLE #tabe  (i int )
declare @id int

INSERT INTO #tabe
SELECT table1.id
FROM table2, table1
WHERE table1.id = table2.id and table1.status = 1


WHILE @txtPtr1 IS NOT NULL
BEGIN
    SET @txtPtr1 = NULL
    SELECT  TOP 1 @txtPtr1 = TEXTPTR(Fields1),
        @flag_field =flag_field,
        @Fields2     = Fields2,
        @id = table1.id
    FROM table2, table1,#tabe t3
    WHERE table1.id = table2.id and table1.status = 1 and  table1.id = t3.i

    IF @txtPtr1 IS NOT NULL
    BEGIN
        IF @flag_field = 1
            UPDATETEXT table1.Fields1 @txtPtr1 NULL 0 @Fields2
        ELSE
            WRITETEXT table1.Fields1 @txtPtr1 @Fields2
   

    END
    DELETE FROM #Tabe
    WHERE i = @id
END

DROP TABLE #tabe
SELECT * from table1
ROLLBACK TRAN
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J2811Commented:
You can't use the "+" operator for text field. Extracts from Help file on "+" operator, below.

-------------------------------------------------------------------------
expression + expression

Arguments:
expression
Is any valid expression of any one 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.

An explicit conversion to character data must be used when concatenating binary strings and any characters between the binary strings. The following example shows when CONVERT, or CAST, must be used with binary concatenation and when CONVERT, or CAST, does not have to be used.
-------------------------------------------------------------------------

It will not work for null either. So in your queries must check first if it is null, if not null then cast them into the same data type and concat again.

BUT IMPORTANT NOTE, read the following extract from SQL Server 2005 documentation. For you to ponder,

------------------------------------------------------------------------------------------
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
------------------------------------------------------------------------------------------

It is NOT going to be supported, I advice you make plans to change the table column from text to varchar or something else.

Let me know if you need further help.

Regards,


0
srafi78Commented:

Update Table1
set field1 =
(CASE Table2.flag_field WHEN 1 THEN field1 + Table2.field2 ELSE field1 END)
FROM Table2 where
Table1.id = Table2.id and Table1.status = 1


I think this one should work fine for you....

Table1
id        field1             status
1      2      1
2      4      1
3      6      1
4      8      1
5      10      1


Table2
id        field2          flag_field
1      1      1
2      2      1
3      1      0
4      2      1
5      3      0

Table 1 after executing the above query
id        field1             status
1      3      1
2      6      1
3      6      1
4      10      1
5      10      1


HTH
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.