Find and replacing numerical values of varying lenth

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table of over 50k entries that are linked to various categories by
the category ID number. I need to re-map this entries to new category ID's
the problem I have is that this database was set up using incremental ID
numbers, so if i try to replace categoy ID 5 with 1641, then every instance
of the number 5 is replaced, so you can imagine waht it does to 155
(116411641) so this is obviously going to bugger things up. Also these
entries are linked to multiply categories, which are displayed comma
delimited, i.e. 5,28,146,223.

Is there a query I can write that will only replace the exact ID number I
want and not every instance of that number inside any other ID number??
 
Presumably the category id is a field.

I would start by creating a table, say catfix:

old_cat_id
new_cat_id

and then run an update query, something like:

UPDATE catfix INNER JOIN Table1 ON catfix.old_cat_id = Table1.cat_id SET
Table1.cat_id = [new_cat_id];
 
David

The categories are stored in another table, but I do not wish to change
thier id, just the id numbers that the entries are linked to, these are
stored in one cell, comma delimited. I hope that makes sense??

--
Bewildered of Bristol


David F Cox said:
Presumably the category id is a field.

I would start by creating a table, say catfix:

old_cat_id
new_cat_id

and then run an update query, something like:

UPDATE catfix INNER JOIN Table1 ON catfix.old_cat_id = Table1.cat_id SET
Table1.cat_id = [new_cat_id];
Click to expand...
 
If the data is always entered with commas separating the items AND if there
are no spaces then you could use that to do your update. However, I would
suggest that if you can you need to normalize this data by moving this
repeating data into a separate table joined to the current table, so that
you end up with one record for each category

If you are using 2000 or later, then I would write a VBA function to handle
this. That function ***might*** look something like the following UNTESTED
code.

Public Function ReplaceCat(strIn, strOldValue As String, strNewValue As
String)
Dim vCat As Variant
Dim iLoop As Integer
Dim strOut As String

If Len(strIn & "") = 0 Then
ReplaceCat = strIn
Else
vCat = Split(strIn, ",")
For iLoop = LBound(vCat) To UBound(vCat)
If Trim(vCat(iLoop)) = strOldValue Then
strOut = strOut & strNewValue & ","
Else
strOut = strOut & vCat(iLoop) & ","
End If
Next iLoop
strOut = Left(strOut, Len(strOut) - 1)
ReplaceCat = strOut
End If

End Function
 
untested. Three passes, something like (sorry about all of the commas):

REPLACE("," & mystring & "," , ",5," , ",1641," )
IIF( LEFT(mystring,1) = "," , MID(mystring,2),mystring )
IIF (RIGHT(mystring,1) ="," , LEFT(mystring,LEN(mystring)-1) , mystring)

my brain hurts
 
Back
Top