CODEWITHSUNDEEP
sqlstored-procedures
wotney
wotney

Reputation: 1046

Return a substring from a field in SQL

I'm hoping one of you knowledgable lot can help me out with this...

I have a field in my DB which contains HTML. Within this HTML, in each record there is a link with parameters included.

eg.

<a href='http://mysite.com/clickme.aspx?ctid=31' title='Click Here !' target='_blank'>Click this link</a>

All I want to return is the ID of the record, and the value of the parameter CTid (CTid could be anything between 2 digits and 12 digits long)

So, what I want returned is:

ID ¦ CTid ¦
73 ¦ 31   ¦

I've tried using PATINDEX but it's not doing the trick for me (because I have to specify the length of the string returned)

SELECT id, SUBSTRING(content, PATINDEX('%CTid%', content), 12) AS CTid
FROM ARTICLES
WHERE content LIKE '%CTid%'

returns:

   ID ¦ CTid         ¦
   73 ¦ ctid=31' tit ¦

Can anyone shed some light on a good way of doing this ?

I'll be returning several thousand rows so would rather stay away from cursors and intensive operations if possible.

Thanks a lot :)

Upvotes: 1

Views: 527

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

SELECT ID, SUBSTRING(content, PATINDEX('%CTid%', content) + 5, PATINDEX('%[^0-9]%', SUBSTRING(content, PATINDEX('%CTid%', content) + 5, 12)) - 1) AS CTid       
FROM ARTICLES
WHERE content LIKE '%CTid%'

Upvotes: 1

TechDo
TechDo

Reputation: 18629

How about this query, works in ms sql server:

SELECT ID, SUBSTRING(CTid, 0, PATINDEX('%''%', CTid))
FROM(
    SELECT ID, SUBSTRING(content, PATINDEX('%CTid%', content)+5, 12)+'''' AS CTid
    FROM ARTICLES
    WHERE content LIKE '%CTid%'
)X

Upvotes: 1

Luke94
Luke94

Reputation: 712

SELECT PARSENAME(REPLACE(PARSENAME(REPLACE(yourSQLString, "=", "."), 2),"'", "."),1)

This requires that yourSQLString starts at least 1 digit before the = and finishes at least 1 digit after '

Upvotes: 0

Related Questions