CODEWITHSUNDEEP
sqlsql-serverkey-value
Masna
Masna

Reputation: 223

getting info out a key-value field sql

I try to write a sql query to get some info out a "key-value" field.

Is it possible with a SQLfunction to select only the DocID?

Which function can I use?

Thx

Upvotes: 0

Views: 155

Answers (1)

Martin Smith
Martin Smith

Reputation: 453707

If you need to query the individual values contained in this column you should consider extracting the information and storing it in a relational format. Currently you are violating 1st Normal Form.

That said you can use substring and charindex for this.

DECLARE @key VARCHAR(20)
SET @key='DocID';

DECLARE @Search VARCHAR(30)
SET @Search='''' + @key + '''=';

WITH your_table(col)
     AS (SELECT 'A<1,?,''wfinitiation''=A<1,?,''DocID''=56581,''DocVersion''=0>>')

SELECT Substring(col, Charindex(@Search, col) + Len(@Search),
              Charindex(',',
              REPLACE(col, '>', ','), Charindex(@Search, col)) - (
              Charindex(@Search, col) + Len(@Search) )) AS value
FROM   your_table

Upvotes: 1

Related Questions