Charindex backwards i.e from right to left

Question

Post reply

Charindex backwards i.e from right to left

rabisco

Ten Centuries

Points: 1032
More actions
May 3, 2012 at 9:35 pm

#99707

From the following string.....

Embedded link <a class="productLink href=";" macro="productLink" code1="1055288" uid="_13360741834971941">code1</a>sku

I'm trying to detemine the position of "<a class="productLink" working backwards from the position of "code1="

Thanks

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Try REVERSE()

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

rabisco (5/3/2012)

From the following string.....

Embedded link <a class="productLink href=";" macro="productLink" code1="1055288" uid="_13360741834971941">code1</a>sku

I'm trying to detemine the position of "<a class="productLink" working backwards from the position of "code1="

Thanks

select charindex(reverse('code1="',reverse(mycolumn),charindex(reverse('"<a class="productLink"'),reverse(mycolumn) )

you could also try

select charindex(''code1=',mycolumn)-charindex('"<a class="productLink"',mycolumn)

but without knowing what the "target" number is that you need in this problem i'm not sure which will give the correct value

MVDBA

rabisco Ten Centuries Points: 1032 More actions · Answer 3

michael vessey (5/4/2012)

rabisco (5/3/2012)

From the following string.....

Embedded link <a class="productLink href=";" macro="productLink" code1="1055288" uid="_13360741834971941">code1</a>sku

I'm trying to detemine the position of "<a class="productLink" working backwards from the position of "code1="

Thanks

select charindex(reverse('code1="',reverse(mycolumn),charindex(reverse('"<a class="productLink"'),reverse(mycolumn) )

you could also try

select charindex(''code1=',mycolumn)-charindex('"<a class="productLink"',mycolumn)

but without knowing what the "target" number is that you need in this problem i'm not sure which will give the correct value

Hi Mike,

thanks for you resonse and suggestions.

What I'm really trying to get at this.

I could have one or more of these link...



<p style="text-align: justify;">Embedded link <a class="productLink href=";" macro="productLink" code1="1055288" uid="_13360741834971941">code1</a>sku</p>

<p style="text-align: justify;">Embedded link <a class="productLink href=";" macro="productLink" code1="1055288" uid="_13360741834971941">code1</a>sku</p>

For every "code1" I need to detemine the position of "<a class="productLink - which is basically doing a charindex working backwards from the position of "code1"

Your suggestion



select charindex(''code1=',mycolumn)-charindex('"<a class="productLink"',mycolumn)

Returns the position of any <a class="productLink it finds.

Thanks.