Script to get properties out of ASP.NET Profile Property Key Value data based on the key.

GetProperty.sql

begin
	-- These first three would be the function arguments.
	declare @property_name nvarchar(32) = 'CurrentAcct'
	declare @prop_encoding nvarchar(64) = 'CurrentTN:S:0:10:CurrentSite:S:10:3:CurrentAcct:S:13:8:' --'CurrentSite:S:0:3:CurrentAcct:S:3:8:'
	declare @properties nvarchar(32) = '501463909143229127406' --'43731870303'

	declare @propval_index bigint = PATINDEX('%' + @property_name + ':%', @prop_encoding) + LEN(@property_name) + 1
	declare @propval_start_index bigint = CHARINDEX(':', @prop_encoding, @propval_index) + 1
	declare @propval_start_index_len bigint = CHARINDEX(':', @prop_encoding, @propval_start_index) - @propval_start_index
	declare @propval_len_index bigint = @propval_start_index + @propval_start_index_len + 1
	declare @propval_len_index_len bigint = CHARINDEX(':', @prop_encoding, @propval_len_index) - @propval_len_index

	-- Transform from ASP's 0 based index to T-SQL's 1 based index.
	declare @propval_start bigint = CAST(SUBSTRING(@prop_encoding, @propval_start_index, @propval_start_index_len) AS bigint) + 1
	declare @propval_len bigint = CAST(SUBSTRING(@prop_encoding, @propval_len_index, @propval_len_index_len) AS bigint)

	declare @propval nvarchar(64) = SUBSTRING(@properties, @propval_start, @propval_len)

	select	@propval as PropertyValue,
			@propval_index as propval_index,
			@propval_start_index as propval_start_index,
			@propval_start_index_len as propval_start_index_len,
			@propval_len_index as propval_len_index,
			@propval_len_index_len as propval_len_index_len,
			@propval_start as propval_start,
			@propval_len as propval_len
end