February 4, 2009 at 8:05 am
Hi!
I would like to know if there's any way to replace a value in a column.
I have one table with some HTML Tags and I have to decode a string inside a column with new one.
Could everyon help me??
Thanks a lot.
Alex
Ex.
Col | Body
-----------------------------------
1 | <html ....<a href="Test/Test.pdf" ....
2 | <html .... <a href="Test/Test2.pdf" ....
3 | <html ....<a href="Test/Test3.pdf" ....
I have to change the path of all "<a href=", of the Body column, into new one.
ex.:
Col | Body
-----------------------------------
1 | <html ....<a href="NewTest/Test/Test.pdf" ....
2 | <html ....<a href="NewTest/Test/Test2.pdf" ....
3 | <html ....<a href="NewTest/Test/Test3.pdf" ....
ENCLOSED THERE'S A FILE WITH DEFITION OF THE TABLE AND INSERT.
Thanks
February 4, 2009 at 8:26 am
You could check Books Online for the Update and Replace() Commands
Used together I think you could very easily come up with a solution to your problem.
Without some better sample data and DDL I can't be any more specific. Check out this article on how to post data to help us help you...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-Luke.
February 4, 2009 at 8:28 am
Agree with Luke I do, not enough info to help.
February 4, 2009 at 8:40 am
Thanks for you prompt answer !!!
What I'm try to do is a SP or UDF to change the insert of one line in the table by using the CHARINDEX to change the path of the HREF...
One things, in the same column could be many HREF to change... I have to change it all !!! :crazy:
Thanks
Alex
February 4, 2009 at 8:45 am
Do you really have links that are like test\test1.pdf?
You may need to get pretty creative with your Charindex statements, to make sure you only change test once and not more than once, or perhaps the next time the procedure is run...
That's why we need to see better sample data... if you have multiple hrefs in a single row we need to see samples that show that,otherwise you will get an incomplete answer...
-Luke.
February 4, 2009 at 9:37 am
excuse me.... how can I post you the insert statement? I don't know why but when I try to post the line with HTML tags... the form transform the statement... :ermm:
I've tried more than one with preview... but the Message form always change the code !!!
Anyway the Body column is TEXT column but QUOTENAME function doesn't work.... (I've read your link: http://www.sqlservercentral.com/articles/Best+Practices/61537/ )
CREATE TABLE [HTMLART] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[BODY] VARCHAR(8000) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
SELECT '1',' <a href="Test/145_test.pdf" title="Test" target="_'
SELECT '2',' <a href="Test/187_test.pdf" title="Test" target="_'
February 4, 2009 at 11:02 am
OK now we're getting somewhere, you posting a bit better data helps. I know there are limitations to what characters you can put together on this site, but I don't know what they are. an alternative when that type of thing happens is to put your code into a text file and upload it as an attachment. and yeah the quotename bit prolly won't work with an href tag like that because it already contains quotes... sometime you just have to do it the hard way...
My question still remains though, is your structure really a href="Test/145_test.pdf" title="Test" target="_ Because if you try to replace 'test' with 'newtest' it will happen 3 times, a href="newTest/145_newtest.pdf" title="newTest" target="_
Does you actual data follow this same naming convention or would it be more fair to say it looks more like
a href="MyFolder/145.pdf" title="Descriptivetitle" target="_
Either way, here's an idea of how I would approach it, but remember that you need to pay attention to your string matching inside the replace function, or you could really screw yourself.
--Create Table
CREATE TABLE #HTMLART (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[BODY] VARCHAR(8000) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
--Add Sample Data
INSERT INTO #HTMLART
SELECT ' <a href="Test/145_test.pdf" title="Test" target="_'
UNION ALL
SELECT ' <a href="Test/187_test.pdf" title="Test" target="_'
--What we started with
SELECT *
FROM #HTMLART
--modify existing rows
UPDATE #HTMLART
SET Body = REPLACE(Body, '"Test/', '"NewTest/Test/')
--what we ended with
SELECT *
FROM #HTMLART
--Cleanup
DROP TABLE #HTMLART
Hopefully that can point you in the right direction...
-Luke.
February 5, 2009 at 2:21 am
Thanks Luke,
this is very interesting solution. The problem is that I don't have the plain control of what I'm going to insert in the body column. I'm going to try to explain better: I have to make the insert in this table of some informations that will arrive from a JS script, what I have to do is to change the Path of all the "<a href" string before doing the insert. This means that I don't know the really path, I have to check all the "<a href=" string and adding a new sentence before the content of the href.. this means that I have to take all the href sentence one by one, than identify what is inside this tag and than change in new one...
ex.
href="path/NameOfapplication" will have to change to href="NewPath/path/NameOfapplication". The NewPath sentence will be the same in all the href that I'll change.
The problem is that I can't use the REPLACE because I don't know what I have to change. The only thing that I know is that I have to change what is inside the href="xxxx"
Thanks Luke.
THIS IS THE TXT ATTACHMENT AS U SUGGEST WITH THE ORIGINAL QUERY AND WITH THE QUERY THAT I'M LOOKING FOR...
Alex
February 5, 2009 at 6:23 am
So if they'll all be the same "NewPath" just prepended to the original, why not replace
'a href="' with 'a href="MyPath/' ?
This should take care of records with multiple links in the same row, the only problem is that it's a once and done type of thing... running it more than once with create things like
'''a href="MyPath/MyPath/'
You could pehaps use patindex but that would create the same issues and I think this will run more quickly.
-Luke.
February 5, 2009 at 6:57 am
Thanks Luke, great solution!!
when I execute this script, it return an error :crying:
UPDATE HTMLART
SET Body = REPLACE(Body, 'a href="', 'a href="MyPath/')
WHERE ID = 1
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.
I think that the reason is why the BODY column is a TEXT datatype.
CREATE TABLE [HTMLART] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[BODY] TEXT COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Have u suggestion?
Thanks a lot !!
Alex
February 5, 2009 at 7:05 am
what's the longest column length of your body column? This will work on a varchar(8000), but I don't think it likes text columns because of how they are stored.
If you don't have anything over 8000 characters and never will you could perhaps look at modifying your table structure, or perhaps converting to varchar before the replace statement. Just remember the convert will truncate anything over 8000 characters.
-Luke.
February 5, 2009 at 7:31 am
Hi Luke!
I couldn't change the lenght of the table, it must be a TEXT column 'cause as I said this table is populated by a JS that is an online editor and I have to take all the datas... i couldn't truncate the column...
I have read about UPDATETEXT instruction.. do u have some tips to use it?
Alex
February 5, 2009 at 7:52 am
No ideas about working with text columns as I've never really had to do much of that.
All I can say is read the appropriate section in BOL and try it in a test environment. Perhaps someone else will have additional information to add.
-Luke.
February 5, 2009 at 8:07 am
Thanks Luke,
I really appreciate your help !
😀
Alex
February 8, 2009 at 4:00 am
hi
i think you better use html.encode in your application code before saving
the data
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply