Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to loop and relace string Expand / Collapse
Author
Message
Posted Thursday, February 3, 2011 12:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 21, 2013 1:47 AM
Points: 114, Visits: 209
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

Post #1058310
Posted Thursday, February 3, 2011 1:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 7,021, Visits: 12,920
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1058349
Posted Thursday, February 3, 2011 1:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 7:48 AM
Points: 67, Visits: 364
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
Post #1058357
Posted Thursday, February 3, 2011 2:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 21, 2013 1:47 AM
Points: 114, Visits: 209
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


Post #1058389
Posted Thursday, February 3, 2011 2:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 6,147, Visits: 7,202
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1058403
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse