Extracting data using substring and charindex?

Question

Extracting data using substring and charindex?

James Millar-305032

Ten Centuries

Points: 1047
More actions
June 2, 2006 at 4:46 am

#113214

Hi guys,
I am creating a view to be used for reporting and need some help extracting data from text field in a table. I have tried using substring and charindex but cannot get it to work. I think this is because i need specific data that is in the middle of the text field - see the extract below for example:
<?xml version="1.0" encoding="UTF-8"?> <DOL_BookRQ TestBooking="false" Version="2.0"><SecurityInfo Password="supercorra" Username="corrax"/><BookRQ SearchResultID="5494171"><CustomerForBooking><DirectCustomer><Customer><PersonName><GivenName>Viaggiare srl<
I need to extract the text after 'TestBooking="' (i.e. false) highlighted in red. The problem is that this could read 'true' so I cannot depend on the length of text being 5.
Hope this makes sense, if not let me know!!!
Thanks,
James

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLZ SSChampion Points: 12872 More actions · Answer 1

James try this:

select substring(mycolumn, charindex('TestBooking="', mycolumn) + 13, charindex('"',mycolumn,charindex('TestBooking="', mycolumn) + 13) -

(charindex('TestBooking="', mycolumn) + 13))

This will only work if TestBooking=" will always be a fixed length of 13 characters (i.e. you cannot have spaces in between the equals sign).

By the way, since you're extracting XML data, couldn't you use OPENXML to extract it into a table and then do your select statement. Not sure about your specific situation but thought I'd mention it just in case it's a suitable option.

Hope that helps,

Karl
source control for SQL Server

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 2

Hi Karl,

Thanks for that, but i get the error "Invalid length parameter passed to the substring function." Does this make any sense to you? The only error i've come across when attempting complex substring functions is "The substring function requires 3 arguments."???

Thanks,

James

P.S. XML is alien to me, so i am checking with my guys now with regards to your OPENXML suggestion!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

You probably get the 'invalid length' message because you have data where charindex finds no match. Since the substring endpoint is calculated, then it gets an invalid value.

To fix it, just hardcode the ending value to the max width of the column. substring don't mind if the last length parameter is bigger then the column length even. Anyway, the point is that you don't then get negative values for length due to pattern not found.

/Kenneth

stax68 SSChampion Points: 11711 More actions · Answer 4

What datatype is the field? Text or a character datatype? Charindex doesn't work on text.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Vladan SSC-Insane Points: 22072 More actions · Answer 5

I need to extract the text after 'TestBooking="' (i.e. false) highlighted in red. The problem is that this could read 'true' so I cannot depend on the length of text being 5.

Well, I the number of characters (true=4, false=5) is not necessarily a problem... from what you wrote I understood you already have a solution, but it doesn't work because of this difference. Could you post this solution, please?

I also suppose this parameter can only have values TRUE or FALSE (and maybe some type of "unknown" - like n/a, null or empty string)?