Parse Microsoft Core Providers in SQL Server

Created: December 21, 2014 Tagged As: SQL Server, T-SQL Share:

The Microsoft Core Providers package provides useful built-in security and user-profile support for ASP.net and MVC applications. Given the flexibility of the Profile provider, Microsoft chose to store the data in SQL Server in a modified name/value pair system. This is not a problem if you are using the information only in your code. But sometimes, it is convenient to pull a piece or two of profile data as part of a T-SQL query. I faced this problem a few days ago and came up with the following solution.

I created a scalar user-defined function (UDF) that accepts the name of the property you want and returns it as an nvarchar.

CREATE FUNCTION dbo.[Profile_GetToken]
(
 @property nvarchar(100),
 @names nvarchar(MAX),
 @values nvarchar(MAX)
)
RETURNS nvarchar(1000)
AS
   BEGIN
      DECLARE @ret nvarchar(1000)
      IF COALESCE(@names,'') = '' OR COALESCE(@values, '') = ''
         RETURN N''

      -- see if property actually exists in the profile
      DECLARE @propIdx int
      SET @propIdx = CHARINDEX(@property, @names, 1)
      IF @propIdx = 0
         RETURN N''

      -- get the property's starting and ending index
      SET @ret = SUBSTRING(@names, @propIdx + LEN(@property) + 1, 2147483647)
      DECLARE @startPos nvarchar(20), @endPos nvarchar(20), @tokenCount int, @lp int, @chr char(1)
      DECLARE @token nvarchar(1000)
      SET @tokenCount = 0
      SET @lp = 1
      SET @startPos = N''
      SET @endPos = N''
      WHILE @lp <= LEN(@ret)
         BEGIN
            SET @chr = SUBSTRING(@ret, @lp, 1)
            IF @chr = N':'
               BEGIN
                  SET @tokenCount = @tokenCount + 1
                  IF @tokenCount >= 2
                     BREAK
               END
            ELSE
               BEGIN
                  IF @tokenCount = 0
                     SET @startPos = @startPos + @chr
                  ELSE
                     SET @endPos = @endPos + @chr
               END
            SET @lp = @lp + 1
         END
      IF CONVERT(int, @endPos) < 1
         RETURN N'' 
      SET @ret = SUBSTRING(@values, CONVERT(int, @startPos) + 1, CONVERT(int, @endPos))
      RETURN @ret
   END