如何从SQL中的字段显示特定字符？

发布于 2025-01-29 23:09:04 字数 951 浏览 2 评论 0原文

这是用于SSRS中的报告。当前，它在一个名为[ usersignupanswers ]的字段中显示一个相当长的数组，该字段看起来有点像这样：

<?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>

我只对上面的 userelectectValue 感兴趣。这来自用户选择的下拉列表（大约有6个选项）。

因此，我要么考虑从上述字段中选择第250个字符，要么大约是第260个字符，或者如果我可以在标签之间显示任何字符，那将是好的。

我一直在尝试Charindex和substring而没有任何成功。有什么想法吗？谢谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

抚笙 2025-02-05 23:09:04

如果您想在SSRS中作为表达式执行此操作，则可以使用此。19

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

指您想找到的第一个字符串的长度

是这确实有效。如果您想在SQL中进行此操作，那么您可以

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

再次进行以下操作，这可能是一种更优雅的方法

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

回复收藏 0 原文

~没有更多了~