Script to loop and relace string

Question

Post reply

Script to loop and relace string

Naren-274001

Ten Centuries

Points: 1033
More actions
February 3, 2011 at 12:34 pm

#388777

Hello All...
Can anyone help me with implementing a script that does the following
search for html tag in one of the columns and empty them with blank
For example :
If the value in a column is == Featured in <a href="/back_to_school">Back to School</a>
I have to update this as == Featured in Back to School
Means, I have to remove ' <a href="/back_to_school"> </a> ' tag from column
I have to do this for all the rows of this column in the table.
I just tried this
SELECT *
FROM temp
WHERE data != ''
AND data like '%<a href=%'
Not sure how to proceed from here
Can some one help me with this

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

SELECT *,

REPLACE(REPLACE(data,'<a href="/back_to_school">',''),'</a>','')

FROM temp

WHERE data != ''

AND data like '%<a href=%'

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

skt5000 SSC-Addicted Points: 479 More actions · Answer 2

Don't have enough to really go on, but this will get ou started. You can pick off the remaining end tag

declare @xml varchar(100)

set @xml = 'Featured in <a href="/back_to_school">Back to School</a>'

select @xml,

charindex('>',@xml)

,stuff(@xml,12,charindex('>',@xml)-11 ,' ')

Jim

Naren-274001 Ten Centuries Points: 1033 More actions · Answer 3

But...

<a href="/back_to_school"> this string keep changing

for 1 column it is 'back_to_School' and for another it is 'doctos_report' etc

Only constant that chage is

<a href="/xxxxxxxxxxxx">xxxxxxxxxxxx</a>

where xxxxxxxxxxxx keeps changing from column to column

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

The column in this you care about is 'Final Removal'.

I broke it down so you can see what each component of the function is doing.

Please test and confirm this will work for you. Notice I work backwards through the string so I don't have to constantly repeat previous work (like removing the anchor tag).

IF OBJECT_ID( 'tempdb..#test') IS NOT NULL

DROP TABLE #test

CREATE TABLE #test (httpfield VARCHAR(500))

INSERT INTO #test

SELECT

'acbdsafsdfasd <a href="/blahblah"> blahblah </a> happy days are here again!' UNION ALL SELECT

'a12312340934241239837458923041029341092 <a href="/yummynummy"> yummynummy </a> xyzalkdfjsa'

SELECT

PATINDEX( '%<a href=%', httpfield ) AS StartingChar,

CHARINDEX( '>', httpfield, PATINDEX( '%<a href=%', httpfield ) +1) AS EndingBracket,

STUFF( httpfield,

PATINDEX( '%<a href=%', httpfield ),

CHARINDEX( '>', httpfield, PATINDEX( '%<a href=%', httpfield ) +1) - PATINDEX( '%<a href=%', httpfield ) + 1,

''

) AS RemovedFirstTag,

CHARINDEX( '</a>', httpfield, PATINDEX( '%<a href=%', httpfield )) AS AnchorTagStart,

STUFF( httpfield, CHARINDEX( '</a>', httpfield, PATINDEX( '%<a href=%', httpfield )) , 4, '') AS RemovedCorrectAnchor,

STUFF( STUFF( httpfield, CHARINDEX( '</a>', httpfield, PATINDEX( '%<a href=%', httpfield )) , 4, ''),

PATINDEX( '%<a href=%', httpfield ),

CHARINDEX( '>', httpfield, PATINDEX( '%<a href=%', httpfield ) +1) - PATINDEX( '%<a href=%', httpfield ) + 1,

''

) FinalRemoval

FROM

#test

Also, note how I created consumable test data at the beginning. This will help us help you in the future, if you do that.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA