?
Solved

How to concate the two strings?

Posted on 2006-04-03
14
Medium Priority
?
439 Views
Last Modified: 2012-05-05
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?




0
Comment
Question by:chuang4630
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16367345
Yes, it is correct
0
 
LVL 1

Author Comment

by:chuang4630
ID: 16367354
Complain from SQL:

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

--> I cannot use '+'.

What should I use then?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16367362
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16367370
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16367373
chuang4630,
> Invalid operator for data type. Operator equals add, type equals text.

> --> I cannot use '+'.

Send me the datatypes of both the columns
0
 
LVL 1

Author Comment

by:chuang4630
ID: 16367379
It complains the samething.

Is there anyway that I do not have to use  '+' to concate two strings?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16367388
In order to update a text field, you cannot use '+', you need to use  'UPDATETEXT' or 'WRITETEXT'..
0
 
LVL 1

Author Comment

by:chuang4630
ID: 16367391
field1 text
field2 varchar 255

that coud be the issue. But how do i resolve it without changing the field type?
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 16367490
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
 
LVL 1

Author Comment

by:chuang4630
ID: 16367506
I used Convert(varchar(8000), field1) + field2
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16367515

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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 16367872
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
 
LVL 3

Assisted Solution

by:J2811
J2811 earned 1000 total points
ID: 16367904
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
 
LVL 8

Expert Comment

by:srafi78
ID: 16367931

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

755 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