CODEWITHSUNDEEP
sqlsql-server
dee
dee

Reputation: 609

How can I return a substring based on CharIndex for starting and end points from a field in a table?

I've been working on this problem most of the morning and I think I have the solution mostly but I must have something wrong somewhere.

I have a table that keeps as a column a long html file.

I need to search that column and return a part of that html file in mssql but I do not have index numbers to use.

My query so far:

SELECT SUBSTRING(ColName, CHARINDEX('TxTStart', ColName), CHARINDEX('TxTEnd', ColName))
FROM htmlTable ht
WHERE ht.Date_ = '2009-01-01'

My thinking is that the length in this query will not be correct to obtain the whole substring from txtStart to TxtEnd.

Should I possibly use RTRIM, just take all the string and then cut off what I need after ? Should the above actually work and I just have something else wrong?

Or am I completely off base here?

Thanks in advance for any help or hints.

Upvotes: 0

Views: 737

Answers (1)

Brett Wait
Brett Wait

Reputation: 397

The 3rd parameter is length, so you need to subtract the end and start to get the length. try this

SELECT SUBSTRING(ColName, CHARINDEX('TxTStart', ColName), CHARINDEX('TxTEnd', ColName) - CHARINDEX('TxTStart', ColName))
FROM htmlTable ht
WHERE ht.Date_ = '2009-01-01'

Upvotes: 4

Related Questions