how can i display specific characters from a field in SQL?

user_2283164 0
Viewed 75

This is for a report in SSRS. currently it displays a rather long array inside a field called [userSignUpAnswers] which looks a bit like this:

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfSignUpQuestionInfo xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SignUpQuestionInfo><EventSignUpQuestionId>1002</EventSignUpQuestionId><QuestionName>What is the nature of your appointment?</QuestionName><Answers><EventSignUpQuestionOptionId>13</EventSignUpQuestionOptionId>
<UserSelectedValue>**Retirement**</UserSelectedValue>
<PriceAdjustment>0.0000</PriceAdjustment></Answers></SignUpQuestionInfo></ArrayOfSignUpQuestionInfo>

I am only interested in the userSelectedValue above. This comes from a drop down list which users select (there are about 6 options).

So I was either thinking of selecting the 250th character from the above field to about the 260th character or if i could display whatever characters are in between the tags that would be good.

i've been trying charindex and substring without any success. any ideas? thanks.

character substring reporting-services sql string
1 Answers

If you want to do this in SSRS as an Expression then you can use this..

=
LEFT (
    MID(Fields!myField.Value, Instr(Fields!myField.Value, "<UserSelectedValue>") + 19),
    Instr(MID(Fields!myField.Value, Instr(Fields!myField.Value, "<UserSelectedValue>") + 19), "</UserSelectedValue>")-1
    )

19 refers to the length of the first string you want to find <UserSelectedValue>

There might be a more elegant way but this does work. If you want to do it in sql then you could do the following

SELECT SUBSTRING(
                myField,
                CHARINDEX('<UserSelectedValue>', myField) + 19,
                CHARINDEX('</UserSelectedValue>', myField, CHARINDEX('<UserSelectedValue>', myField) + 19) - (CHARINDEX('<UserSelectedValue>', myField) + 19)
            )

again, probably a much more elegant way of doing it

user_17753890
Related