Hello All,
I'm looking for a way to update a column in SQL Server and the only
techniques available to me are VB Script of SQL Server tools. The problem is
I need a way to remove some text within a column, e.g.

Table = appdefs
Column = apptitle

Example of data:

apptitle = Adobe Acrobat [1]
apptitle = Adobe Acrobat [2]

The reason I needed the additional text at the end of the entry is because
the tool I'm using requires unique entries during an import session. The
database is not set-up so that the column is actually unique and the client
I'm working with wants the extra text at the end of the line removed to read
just Adobe Acrobat in both cases. So my question is, can I programmatically
remove the last characters, i.e. such as "[x]" for the apptitle column using
T-SQL or VB Script. I'm Ok with VB Script and know ways of removing the entry
but I need help with the connection to SQL Server, retrieving the text item
(multiple rows) and writing back to the database within a script of some
description

Thanks
Dave

Hi David,

Just a quick note; since you're calling a column a "field", I'm
assuming that your "text field" is actually a varchar column, and not a
text column.  A text datatype in SQL needs to be handled differently
than varchar.  If you're going to continue working with SQL Server, you
may want to examine the different datatypes and the appropriate syntax
for updating them.  Of course, if this is a one-off situation, then
life's too short to bother with that.

Anyway, try:

UPDATE appdefs
SET apptitle = RTRIM(LEFT(apptitle, CHARINDEX('[', apptitle)-1))
WHERE CHARINDEX('[', apptitle) > 0

This will update all rows of data that have a '[' in them.

HTH,
Stu

thanks Stu,
Yes, it's a varchar field within a table. Is this statement one which i can
run directly in SQL Server query analyser or DTS package?


Thanks
Dave

Stu wrote:
Show quoteHide quote

It'll run in Query Analyzer.

Stu

Toughy Normalization Problem
Timeout expired - on a simple SqlDataReader:Read() call
Is it possible to join two tables in a View/Query where the Second Table is based on a Name in the F
Primary Key Question
OBJECTPROPERTY problem