如何获得变量的价值？(How to get value of variable?)

如何获得变量的价值？(How to get value of variable?)

我有一个行数据

对于每行我想获取tkID的值，即查询应该返回245764，如何在SQL Server中执行此操作？ 另一个字符串将始终保持不变，只有tkID会有所不同。

我试过SELECT * FROM PERSON WHERE SUBSTRING(1,)...请帮忙

Table SAMPLE DATA PERSON ID TEXT 1 <com.innovation.jsp.beans.structTaskContext obj-name="tkDetails" descriptionKey="B.Tk.Undefined" tkID="245764" abc="1".... 2 <com.innovation.jsp.beans.structTaskContext obj-name="tkDetails" descriptionKey="B.Tk.Undefined" tkID="245765" abc="2".....

输出应该是：

ID tkID 1 245764 2 245765

谢谢，

艾登

I have a row data as

For EACH row I want to get the value of tkID i.e. the query should return me 245764, how to do that in SQL Server ? The other string will always remain same, only tkID will be different.

I have tried SELECT * FROM PERSON WHERE SUBSTRING(1,)...Please help

Table SAMPLE DATA PERSON ID TEXT 1 <com.innovation.jsp.beans.structTaskContext obj-name="tkDetails" descriptionKey="B.Tk.Undefined" tkID="245764" abc="1".... 2 <com.innovation.jsp.beans.structTaskContext obj-name="tkDetails" descriptionKey="B.Tk.Undefined" tkID="245765" abc="2".....

Output should be:

ID tkID 1 245764 2 245765

Thanks,

Aiden

最满意答案

尝试这个。

declare @text nvarchar(1000)='<com.innovation.jsp.beans.structTaskContext obj-name="tkDetails" descriptionKey="B.Tk.Undefined" tkID="245765" abc="2".....'

如果以上格式对所有记录都相同

select substring(@text, charindex('tkID=', @text)+6,charindex('abc=', @text)-(charindex('tkID=', @text)+8))

如果tckId的长度为6

select substring(@text, charindex('tkID=', @text)+6,6)

Try this.

declare @text nvarchar(1000)='<com.innovation.jsp.beans.structTaskContext obj-name="tkDetails" descriptionKey="B.Tk.Undefined" tkID="245765" abc="2".....'

If the above format is same for all records

select substring(@text, charindex('tkID=', @text)+6,charindex('abc=', @text)-(charindex('tkID=', @text)+8))

If tckId is of length 6

select substring(@text, charindex('tkID=', @text)+6,6)