I have a column that contains links. The problem is that the titles of the links are in the same column, so it looks like this:
linktitle|-|linkurl
I want link title and linkurl in separate columns.
I've created a new column for the urls, so I'm looking for a way to extract them and update the linkurl column with them. Is there any clever way to construct a query that does this?
views:
51answers:
4
A:
UPDATE TableName
SET LinkTitle = Substring(LinkColumn, 0, InStr(LinkColumn, '|-|') - 1),
LinkUrl = Substring(LinkColumn, InStr(LinkColumn, '|-|') + 3)
With LinkColumn being the currently existing column, and LinkTitle & LinkUrl being where you want to store the separated data.
DonaldRay
2010-03-26 15:37:52
Thanks replying so quickly! Your query works almost perfectly, but at the end of each title there's a | . Just out of curiosity, what's the +3 for?
Tommy
2010-03-26 15:53:53
InStr(LinkColumn, '|-|') returns the index of the first character of your delimiter, '|-|'. However, you need the index of the first character of the URL, which is 3 higher.Since the query works, you may want to accept this answer by clicking on the check mark to the left so that others with the same problem can quickly see the solution.
DonaldRay
2010-03-26 16:21:22
Ah, I didn't know about the checkmarks. I'll do that on my other questions that's been answered too. Thanks for explaining the InStr!
Tommy
2010-03-27 11:29:26
+1
A:
SELECT substring(field_name, 1, locate('|-|', field_name)-1) as title,
substring(field_name, locate('|-|', field_name)+3) as linkurl
a1ex07
2010-03-26 15:38:47
+1
A:
UPDATE tablename
SET linktitle = SUBSTRING_INDEX(link , '|-|', 1 )
linkurl = SUBSTRING_INDEX(link , '|-|', -1 )
douwe
2010-03-26 15:39:32
A:
The query that solved this problem looks like this:
UPDATE jos_fabrik_posesapp
SET linktitle = Left(poselink, InStr(poselink, '|-|')-1),
linkurl = Substring(poselink, InStr(poselink, '|-|') + 3)
I'm not quite sure what it means, but it worked. Thanks for all replies!
Tommy
2010-03-26 16:15:51