Aiden Aiden - 15 days ago 5
SQL Question

How to get value of variable?

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

Answer

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) 
Comments