Copy specific part in database

Cut out specific part from column

[lazerzubb]

I have an access database which i need to copy certain parts from and put in another column.

(I did not design the db structure!)

<A HREF="/url.asp?site=http://www.domain">http://www.domain.com</A> <A href="get4.asp?id=11111">info</A>

Above is the example of a value, do anyone know how i can copy all instances of
<A href="get4.asp?id=11111">info</A>
and put that in to a seperate column, and remember i can't loss the ID.

Not all ID's have a value in the specific column, and some only have one of the 2 links above, any ideas?
I can't do this by hand due to the size of the db.
I realise the above might be a bit hard to understand

[aspdaddy]

>I realise the above might be a bit hard to understand

Yep :)
<added>

On second read I think this is what you need:

Update Table1
Set newColl = iif( instr(oldColl, '<A href="get4.asp?id=') <>0, mid ( oldColl, instr(oldColl, '<A href="get4.asp?id='),27),"")
where instr( oldColl ,'<A href="get4.asp?id=') <> 0

</added>

[ant3eye]

If I understand you correctly, you have a column in a table with 1 or 2 links in it and you need to move the 2nd link to another column of it's own.

Give this long winded solution a try. :)

1. Create a new Module and paste in the following function

Public Function Get2ndLink(sString As String) As String
On Error GoTo Get2ndLink_Error

Dim sReturn As String
Dim nPos As Integer

If sString <> "" Then
nPos = InStr(sString, "</A> ")
If nPos > 0 Then
sReturn = Mid$(sString, nPos + 5)
Else
sReturn = ""
End If
End If
Get2ndLink = sReturn
Exit Function

Get2ndLink_Error:
Get2ndLink = ""
Exit Function

End Function

2. Then add an extra column to your original table (let's call it New_Col)

3. Create a new query using the following SQL:
UPDATE
TableName
SET
TableName.New_Col = Get2ndLink([Original_Col]);

4. Run the query (You have to hit the! on the toolbar to run it, switching to the data grid will not do anything)

The function will run once for every row in the table so it will probably be slow and not something you'd want to do on a regular basis. But for a one time data cleanup it shouldn't be a problem.

Hope I didn't confuse you more but if I did it's because I'm new around here. Good luck.

[lazerzubb]

I found a solution maybe not the best one but here it is.

Copy the ID in to a excel sheet, then copy the "kolumn" in to B in the same sheet, then replace the first <A with ",<A"
The copy the sheet in to a txt file, replace the big gap between ID and kolumn with a "," then save it.
Then import it in access and seperate columns by "," so even if only 1 <A> value existed it will still be in the right column.

Hehe as i said probably not the smartest solution.

[andreasfriedrich]

Perhaps you could throw in a bit of Word and Powerpoint, too. ;) Make use of all the components of MS Office. ;)

Andreas

[txbakers]

it's a simple solution. - at least one way.

Read the data from the database as it comes into the page with that id.

Then parse it however you want to parse it using VB or JS, or whatever language you like.

Then write an update command to send both parts back to the db.

All this excel, text file, manual labor is not a solution. It might work if you only have to do this 10 times, but if it should grow to 1000 or 10,000 times a day, do you really want to do it manually?

[aspdaddy]

Why do developers always insist on a solution using code/sql . Surley the easiest/fastest solution is better for something like this.

When you consider the time thinking about it, designing & writing code/sql, debugging it etc sometimes manual labour makes much more sense :)

[txbakers]

Not at all. The code/sql solution uses logic to replace manual entries. Sure, you have to think about it once and test/debug it, but once it's written, it's written forever and will work every time.

If you have to do a manual routine more than once, it's a waste of valuable time and it introduces the "human factor" into the process. We humans tend to make mistakes quite often.

Surley the easiest/fastest solution is better for something like this.

For me, the easiest/fastest solution is through a little bit of programming code.