I want to update column in a SQL 7 database. I'm interfacing with the
database using Enterprise Manager. The particular example could be
this. Table Name=bugs
                name       type
                ant        1,2,4
                beetle     2
                cricket    1,2

I want to update bugs set bugs.type = '6'
where bugs.type = '2'

Obviously my question has to do with working around the commas. For
instance the outcome of the update I would like ant to have a type of
1,6,4, beetle a type of 6 and cricket a type of 1,6

---

Request for Question Clarification by mathtalk-ga on 17 Jun 2005 20:38 PDT

Hi, sm789-ga:

If you were to do this in SQL Server 2000, it would be simplified
slightly by the availability of a new string function
REPLACE(arg1,arg2,arg3):

[REPLACE -- MS Transact-SQL Reference]
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_76lh.asp

Under SQL Server 7.0 I'm afraid your will be stuck with some awkward
code that combines the use of patindex, substring, and string
concatenation to achieve mostly the same functionality.  One
significant difference is that REPLACE will handle substitutions in
multiple locations within a string, although that is perhaps not
important to the application you sketch.

If only a single digit is involved (needs replacing), then the problem
is simplified (because only a character has to be replaced in the
update).  But presumably the general problem will involve
comma-delimited strings containing integers of multiple digits.

In this case I could provide some sample code, making some assumptions
about the maximum length of the "type" field.  Please advise me if
there are some further aspects of the "update" that need to be
addressed.

regards, mathtalk-ga

---

Clarification of Question by sm789-ga on 27 Jun 2005 11:03 PDT

I ended up doing something like this:

update bugs
  set bugs.type = replace( type, '2', '6')
FROM  bugs 

It worked fine on SQL 7.

---

Request for Question Clarification by mathtalk-ga on 27 Jun 2005 15:15 PDT

Cool!  Keep in mind that as soon as the strings start mananging
multi-digit numbers, there's a risk of changing "12" to "16" when all
you really wanted was to change "2" to "6".  For greater safety, I'd
add a comma at the beginning and end of the target string
(temporarily, just as part of an expression) and do the replace on:

  ',2,' goes to ',6,'

Then "substring" away the beginning and ending commas to get the final expression.

best wishes, mathtalk-ga

It appears to me that you are going about this in the wrong way.
Ideally, you would have three tables, Bugs, Types, and Bug_Types
Where:
Bugs
BugID  int
Desc   Varchar

Types
TypeID  int
Desc    Varchar

Bug_types
BugID
TypeID

Where your query would be Update Bug_types set TypeID = 6 where TypeID = 2.
In general, you do not want to store 1 to many relationships in a
signle field -which is one of the basics for relational db
architecture.

I agree with vballguy-ga on the normalization principle.  It's a
invitation to heartache to maintain "repeating group" data in a single
field.

However if one must do this in SQL, then pattern matching and the
functions substring( ) and patindex( ) will be your tools of choice
(aka partners in crime).

regards, mathtalk-ga

I appreciate the lecture on database structure. That is certainly what
I paid for. I was traveling yesterday and did not have time to look
for an answer which I needed today.  If I had asked a question on
'sourcing wooden picnic benches for the desert southwest' would you
have told me that climate isn't suited for such material?

I have read your responses before and mathtalk you always come to
rescue. Vballguy, your impertinence unfortunately comes through in
your responses.

I appreciate MathTalks additional information to my comment.

A significant number of people ask questions without either the proper
background, or adaquate details.  Due to this deficiency, when someone
is doing something that is more or less the wrong way to do something,
I would expect and hope that commentors would point out that there is
a better way to do it.

I am sorry if you find it in-appropriate for commentors to point out
better ways to do something as opposed to just answering what was
asked.  Personally, that is one of the reasons that I like google
answers- as they often think of solutions, or additional points that
were not raised.

I hesistate to present an Answer at this time since it may have been
the implication that one was required within the first day or so of
posting.

So, a couple of points, given that the Question has not yet been
expired (closed).  When an Answer is needed in a short period of time,
esp. given the nature and pricing of a Question of this kind, stating
a deadline would be of great help in encouraging a Researcher to act
quickly.  It might otherwise be considered a good opportunity for the
Customer to first have the benefit of free Comments, with the
necessity of a paid Answer.

Which brings me to the second point.  No charge will be assessed for
the List Price assigned by the Customer unless a Researcher posts an
Answer.  Any Comments, such as appeared in this thread, are always
gratis.

best wishes, mathtalk-ga