getting info out a key-value field sql

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

  • Example of field: A<1,?,'wfinitiation'=A<1,?,'DocID'=56581,'DocVersion'=0>>

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

  • Result in the example would be: 56581

Which function can I use?

Thx

Answers


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  

Need Your Help

Loading screen for Google Maps

php javascript google-maps google-maps-api-3

I've noticed that Google Maps is taking longer than usual to load these days, and causes the browser to refresh many times before loading. Either way, this has led me to wonder how I can create a l...

C++11 Delete Class Type?

c++ c++11

In C++11 N3485 5.3.5.1 it says:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.