February 2, 2010 at 11:46 pm
Hello All,
I'm having a heck of a time figuring this one out. I have a text field that I'm storing the html for web pages. Some tags were entered incorrectly and I need to replace some characters with an underscore. In every block of text that begins with <a href="# and ends with </a>, I have to replace each instance of %20 with an underscore.
For example, if I have: <a href="#Employee%20Manual">Employee Manual</a>
I need it to read: <a href="#Employee_Manual">Employee Manual</a>
Does anyone have an idea of how to accomplish this?
Thanks in advance!
Ruprecht
February 2, 2010 at 11:56 pm
Ruprecht (2/2/2010)
Hello All,I'm having a heck of a time figuring this one out. I have a text field that I'm storing the html for web pages. Some tags were entered incorrectly and I need to replace some characters with an underscore. In every block of text that begins with <a href="# and ends with </a>, I have to replace each instance of %20 with an underscore.
For example, if I have: <a href="#Employee%20Manual">Employee Manual</a>
I need it to read: <a href="#Employee_Manual">Employee Manual</a>
Does anyone have an idea of how to accomplish this?
Thanks in advance!
Ruprecht
Is this HTML stored in a table in SQL Server database? It was little unclear from your post. If it is,
then this is how you will use replace function in the SQL statment.
REPLACE(<yourfieldname>,'%20','_')
February 3, 2010 at 12:10 am
Yes, the html is stored in a SQL 2005 database in a text column. My problem is, I can't use the REPLACE function the way you describe because the string is never the same. I have to parse the text and find strings that begin with <a href="# and end with </a>. I can only replace the %20 occurrences that meet this criteria because %20 shows up hundreds of times elsewhere.
February 3, 2010 at 7:42 am
If you store that string in a column why just not do:
UPDATE my_table SET my_html_column = REPLACE(my_html_column,'%20','_')
WHERE my_html_column LIKE '<a href="#%</a>'
% character means any string that starts with <a href="# and ends with </a>
February 3, 2010 at 10:02 am
I don't simply store the tag in the text column, I store entire web pages. I need to sift through 5,000 lines of text to replace %20 with an underscore every time it's contained in a line that begins with <a href="# and ends with </a>. I can't replace every instance of %20, just the ones between the tags I just mentioned.
Is this just not possible or am I not explaining my situation well enough?
February 3, 2010 at 11:18 am
Ruprecht I'm halfway there from a sinlge command solution, but i wanted to post an immedite fix right away; if this is a one-time solution, this would give you everything you need so you could create a simple REPLACe command to run to fix all the links;
you didn't provide any example data, so i adapted a different example.
basically, i'm using a tally table to find ever "DesiredString" between two delimiters, namely{a href="} and {">}
this tally example shos that the desired string might occur multiple times in the string. for example, my DesiredString returns values like :
#Employee%20Manual
#Another%20Example
#Employee%20Manual
#ANonQualifyingLink
--produces final results like
UPDATE #Inventory_Trans SET Description = REPLACE(Description,'#Another%20Example','#Another_Example')
UPDATE #Inventory_Trans SET Description = REPLACE(Description,'#Employee%20Manual','#Employee_Manual')
I'm using the results to build a suite of REPLACE Statements dynamically for anything that contained '%20' explicitly between the htmltag delimiters i selected
try this on a test table, and see if it does what you want:
Create table #Inventory_Trans (OtherId int,Description varchar(max) )
insert into #Inventory_Trans SELECT 1,'Transfer to location S034R6/MAIN RCVR ID 55310-1 <a href="#Employee%20Manual">Employee Manual</a>
Transfer to location E/MACHINE <a href="#Employee%20Manual">Employee Manual</a>
SHOP RCVR ID 55334-1<a href="#Another%20Example">Another 20Example</a><a href="#ANonQualifyingLink">Non Qualifying Link</a>
Transfer to <a href="#ANonQualifyingLink">Non Qualifying Link</a>location F/MACHINE <a href="#Employee%20Manual">Employee Manual</a>
SHOP RCVR ID 55329-1<a href="#Another%20Example">Another 20Example</a>'
UNION ALL SELECT 2,'Transfer to location <a href="#Employee%20Manual">Employee Manual</a>
S034R6/MAIN RCVR ID 77310-1<a href="#Another%20Example">Another 20Example</a>
Transfer <a href="#ANonQualifyingLink">Non Qualifying Link</a>to location E/MACHINE <a href="#Employee%20Manual">Employee Manual</a>
SHOP RCVR ID 77334-1<a href="#Another%20Example">Another 20Example</a>
Transfer to location E/MACHINE SHOP RCVR ID 6544-1
Transfer to location <a href="#ANonQualifyingLink">Non Qualifying Link</a>E/MACHINE SHOP RCVR <a href="#Employee%20Manual">Employee Manual</a>
ID 77378-1<a href="#Another%20Example">Another 20Example</a>
Transfer to <a href="#Employee%20Manual">Employee Manual</a><a href="#ANonQualifyingLink">Non Qualifying Link</a>
location E/MACHINE SHOP RCVR ID 77334-9
Transfer to location F/MACHINE <a href="#ANonQualifyingLink">Non Qualifying Link</a>SHOP RCVR <a href="#Employee%20Manual">Employee Manual</a>
ID 77329-1<a href="#Another%20Example">Another 20Example</a>'
--now get the ID's using a tally table
declare
@pre varchar(10),
@post varchar(10),
@pr int,
@po int,
@st int
set @pre = 'a href="'
set @post = '">'
set @pr = len(@pre)
set @po = len(@post)
set @st = 1
;WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
SELECT top 30
T1.N,
T2.*,
SUBSTRING( T2.Description,
( CASE T1.N WHEN 1 THEN 1 ELSE T1.N + @pr END ),
( CASE CHARINDEX( @post, T2.Description, T1.N + @pr ) WHEN 0 THEN LEN( T2.Description ) - T1.N + @pr
ELSE CHARINDEX( @post, T2.Description, T1.N + @pr ) - ( T1.N + @pr ) END ) ) AS DesiredString ,
Description as Description2
Into #Results --capture into an intemediary table
FROM Tally T1
CROSS APPLY #Inventory_Trans T2
WHERE N <= LEN( T2.Description )
AND SUBSTRING( T2.Description, T1.N, @pr ) = @pre
drop table #Inventory_Trans
SELECT * from #Results
select distinct 'UPDATE #Inventory_Trans SET Description = REPLACE(Description,''' + DesiredString + ''',REPLACE(DesiredString ,'%20','_')) '
from #Results
WHERE DesiredString LIKE '%!%20%' ESCAPE '!'
--drop table #Results
Lowell
February 3, 2010 at 12:23 pm
Lowell, you sir, are a genius. Your solution worked perfectly. I can't thank you enough. Sorry I didn't include an example table and data, I'll be sure to remember next time.
February 3, 2010 at 1:20 pm
Rup you might want to check for other iterations based on html differences; for examples spaces before/after the equals sign just to be sure;
i'm glad this worked for you.
--original:
set @pre = 'a href="'
set @post = '">'
--maybe:
set @pre = 'a href= "' --or 'a href ="' or 'a href = "'
set @post = '">' -- or '" >'
also you removed the limitation for TOP 30 right? when i tested it on a big table, i didn't need a million rows of results...but you'll need em all, so take that out.
Lowell
February 3, 2010 at 3:20 pm
Yep, I removed the TOP 30 limitation and I had to make a few minor changes to the script that generates the UPDATE statements, but other than that it worked like a charm. I checked and all of the tags were formed the same way, so I didn't have to worry about oddly placed spaces.
Thanks again!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply