Link to home
Start Free TrialLog in
Avatar of Charles Baldo
Charles BaldoFlag for United States of America

asked on

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





Avatar of kaufmed
kaufmed
Flag of United States of America image

Try this:

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

Open in new window

SOLUTION
Avatar of kaufmed
kaufmed
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Charles Baldo

ASKER

Thats very close.  I was having the same issue until you suggested inverse parameters.  I can get it now