How To: Extract ASP.NET Membership profile properties in SQL Server 2008 T-SQL
Man, that title is a mouthful, but it works. There are other posts on this that work with all versions of SQL Server, but this is my take with a bonus. I have created a second function for the extraction of binary values, for those that need them. This code uses some new functionalities of SQL Server 2008 and will not work as written on previous versions of SQL Server. Also, all of the other posts out there are not 100% compiant with the format. They don't convert a value with a length of -1 to a NULL. Here is the script to return strings:
/****** Object: UserDefinedFunction [dbo].[fn_GetProfileStringElement] ******/
IF EXISTS(SELECT * FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_GetProfileStringElement]') AND [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetProfileStringElement];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER OFF;
GO
/******************************************************************************
** Schema: dbo
** Name: fn_GetProfileStringElement
** Desc:
**
** Auth: William Bosacker
** Date: 06/22/2009
**
** Parameters:
** ---------------------------------------------------------------------------
**
**
** Returns:
** ---------------------------------------------------------------------------
**
******************************************************************************/
CREATE FUNCTION [dbo].[fn_GetProfileStringElement] (
@PropertyName nvarchar(256),
@PropertyNames nvarchar(max),
@PropertyValuesString nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @SearchString nvarchar(259);
DECLARE @LeftPointer bigint;
DECLARE @RightPointer bigint;
DECLARE @ValueStart bigint;
DECLARE @ValueLength bigint;
IF @PropertyName IS NULL OR LEN(@PropertyName) = 0
OR @PropertyNames IS NULL OR LEN(@PropertyNames) = 0
OR @PropertyValuesString IS NULL OR LEN(@PropertyValuesString) = 0
BEGIN
RETURN NULL;
END;
SET @SearchString = @PropertyName + ':S:';
SET @LeftPointer = CHARINDEX(@SearchString, @PropertyNames, 0);
IF @LeftPointer != 0
BEGIN
-- Find the starting position of the value.
SET @LeftPointer = @LeftPointer + LEN(@SearchString);
SET @RightPointer = CHARINDEX(':', @PropertyNames, @LeftPointer);
SET @ValueStart = CAST(SUBSTRING(@PropertyNames, @LeftPointer, @RightPointer - @LeftPointer) AS bigint);
-- Find the length of the value.
SET @LeftPointer = @RightPointer + 1;
SET @RightPointer = CHARINDEX(':', @PropertyNames, @LeftPointer);
SET @ValueLength = CAST(SUBSTRING(@PropertyNames, @LeftPointer, @RightPointer - @LeftPointer) AS bigint);
IF @ValueLength = -1
BEGIN
RETURN NULL;
END;
RETURN SUBSTRING(@PropertyValuesString, @ValueStart + 1, @ValueLength);
END;
RETURN NULL;
END;
GO
Here is the script to return binary values:
/****** Object: UserDefinedFunction [dbo].[fn_GetProfileBinaryElement] ******/
IF EXISTS(SELECT * FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_GetProfileBinaryElement]') AND [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetProfileBinaryElement];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER OFF;
GO
/******************************************************************************
** Schema: dbo
** Name: fn_GetProfileBinaryElement
** Desc:
**
** Auth: William Bosacker
** Date: 06/22/2009
**
** Parameters:
** ---------------------------------------------------------------------------
**
**
** Returns:
** ---------------------------------------------------------------------------
**
******************************************************************************/
CREATE FUNCTION [dbo].[fn_GetProfileBinaryElement] (
@PropertyName nvarchar(256),
@PropertyNames nvarchar(max),
@PropertyValuesBinary varbinary(max)
)
RETURNS varbinary(max)
AS
BEGIN
DECLARE @SearchString nvarchar(259);
DECLARE @LeftPointer bigint;
DECLARE @RightPointer bigint;
DECLARE @ValueStart bigint;
DECLARE @ValueLength bigint;
IF @PropertyName IS NULL OR LEN(@PropertyName) = 0
OR @PropertyNames IS NULL OR LEN(@PropertyNames) = 0
OR @PropertyValuesBinary IS NULL OR LEN(@PropertyValuesBinary) = 0
BEGIN
RETURN NULL;
END;
SET @SearchString = @PropertyName + ':B:';
SET @LeftPointer = CHARINDEX(@SearchString, @PropertyNames, 0);
IF @LeftPointer != 0
BEGIN
-- Find the starting position of the value.
SET @LeftPointer = @LeftPointer + LEN(@SearchString);
SET @RightPointer = CHARINDEX(':', @PropertyNames, @LeftPointer);
SET @ValueStart = CAST(SUBSTRING(@PropertyNames, @LeftPointer, @RightPointer - @LeftPointer) AS bigint);
-- Find the length of the value.
SET @LeftPointer = @RightPointer + 1;
SET @RightPointer = CHARINDEX(':', @PropertyNames, @LeftPointer);
SET @ValueLength = CAST(SUBSTRING(@PropertyNames, @LeftPointer, @RightPointer - @LeftPointer) AS bigint);
IF @ValueLength = -1
BEGIN
RETURN NULL;
END;
RETURN SUBSTRING(@PropertyValuesBinary, @ValueStart + 1, @ValueLength);
END;
RETURN NULL;
END;
GO
And last, but not least, here is a sample of how to use the functions:
SELECT
U.[Username],
[dbo].[fn_GetProfileStringElement]('FirstName', P.[PropertyNames], P.[PropertyValuesString]) [FirstName],
[dbo].[fn_GetProfileStringElement]('LastName', P.[PropertyNames], P.[PropertyValuesString]) [LastName],
[dbo].[fn_GetProfileBinaryElement]('Avatar', P.[PropertyNames], P.[PropertyValuesBinary]) [Avatar]
FROM
[dbo].[aspnet_Users] U
JOIN [dbo].[aspnet_Profile] P ON P.[UserId] = U.[UserId]
Posting code in this blog takes a lot of work, and it is possible that I screwed it up. Please post a comment if you find a problem with the code.