CODEWITHSUNDEEP
sqlsql-server
Aiden
Aiden

Reputation: 460

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

Upvotes: 1

Views: 64

Answers (3)

Shiju Shaji
Shiju Shaji

Reputation: 1730

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)

Upvotes: 1

Chanukya
Chanukya

Reputation: 5883

create table #b
(
                     ID    int,     TEXT varchar(max)
                     )
                     insert into #b values
(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".....')

    SELECT SUBSTRING(text, PATINDEX('%[0-9]%', text), PATINDEX('%[0-9][^0-9]%', text + 't') - PATINDEX('%[0-9]%', 
                    text) + 1) AS Number from #b

output

Number
245764
245765

Upvotes: 1

Ullas
Ullas

Reputation: 11556

You can achieve this by using a combination of PATINDEX, CHARINDEX, RIGHT and LEFT string functions.

Query

select  left((right(@str, len(@str) -  patindex('%tkID="%', @str) - 5)), 
        charindex('"', 
        (right(@str, len(@str) -  patindex('%tkID="%', @str) - 5)), 1) - 1);

Upvotes: 1

Related Questions