Need to get instring from sql

Hello

I have a  column they that the data  is a very uniform web URL

<a href="http://www.blogname.com/2011/10/28/unbalanced-antidepressants-use/"
target="_newwindow">Unbalanced Antidepressants Use</a>

<a href="http://www.blogname.com/2011/10/20/a-stealth-contact-lens-recall/"
 target="_newwindow">A Stealth Contact Lens Recall</a>
...
...
...
<a href="http://www.blogname.com/2011/07/21/can-safety-be-taken-too-far/"
 target="_newwindow">Can Safety be Taken Too Far</a>

I need to get all the link  between the  target="_newwindow"> and the </a>

So I would need to list out


Unbalanced Antidepressants Use
A Stealth Contact Lens Recall
...
...
...
Can Safety be Taken Too Far



I am sure that the data will always be in that format

Thank You





Charles BaldoSoftware DeveloperAsked:
Who is Participating?
 
djon2003Commented:
Inversed two params.

Corrected version.
SELECT 
SUBSTRING(fieldToSearch, CHARINDEX ( '>', fieldToSearch ), LEN(fieldToSearch) - CHARINDEX ( '>', fieldToSearch ) - 1) AS LinkText 

FROM myTable

Open in new window

0
 
käµfm³d 👽Commented:
Try this:

SELECT SUBSTRING([columnName], CHARINDEX('>', [columnName]), CHARINDEX('<', [columnName], (CHARINDEX('>', [columnName]) + 1) - CHARINDEX('>', [columnName]))
FROM [tableName]

Open in new window

0
 
käµfm³d 👽Commented:
Add one more paren to the end of that:

SELECT SUBSTRING([columnName], CHARINDEX('>', [columnName]), CHARINDEX('<', [columnName], (CHARINDEX('>', [columnName]) + 1) - CHARINDEX('>', [columnName])))
FROM [tableName]

Open in new window

0
 
djon2003Commented:
Here is an example on how to do that.
SELECT SUBSTRING(fieldToSearch, CHARINDEX ( fieldToSearch , '>' ), LEN(fieldToSearch) - CHARINDEX ( fieldToSearch , '>' ) - 1) AS LinkText FROM myTable

Open in new window

0
 
Charles BaldoSoftware DeveloperAuthor Commented:
Thats very close.  I was having the same issue until you suggested inverse parameters.  I can get it now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.