Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Connecting string values in SQL

Posted on 2006-04-26
9
Medium Priority
?
241 Views
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 ?
0
Comment
Question by:vpekulas
7 Comments
 
LVL 7

Expert Comment

by:MessHallMan
ID: 16547553
If you add () it should work, like the following:

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

Expert Comment

by:Scott Pletcher
ID: 16547652
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.
0
 

Author Comment

by:vpekulas
ID: 16547656
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 '('.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:vpekulas
ID: 16547670
:( OK, I guess that explains it .... thanks.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 16549580
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)
go
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

SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1

UPDATETEXT TextExample.Text3 @txtPtr3 NULL 0 ' '

SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1

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

SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
0
 

Expert Comment

by:korayboluk1
ID: 16668048
Hi,

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
begin
return (select col1+col2+...(columns you want to concatenate) from your table name where condition)
end

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
0
 

Expert Comment

by:korayboluk1
ID: 16668056
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
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

581 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