Extract columns from microsoft SqlProfileProvider

If you want to select data from the asp.net membership api database but you are having problems because the PropertyValuesString / PropertyNames / PropertyValuesBinary values are all delimited / separated by a colon but you want to but cant use a split function on it. Below is a solution. There are also UserId LastUpdatedDate columns.

Step 1. Execute the sql from HERE to create some functions to extract your data.
Step 2. Create a view like the example below
Step 3. (optional) index your view.

create my_cusomers_vw view as
SELECT
dbo.fn_GetProfileElement('Phone',PropertyNames,PropertyValuesString) AS Company,
dbo.fn_GetProfileElement('ZipCode',PropertyNames,PropertyValuesString) AS ZipCode,
dbo.fn_GetProfileElement('State',PropertyNames,PropertyValuesString) AS State
FROM dbo.aspnet_Profile
GO


select * from my_customers_vw

--------------------------------------------

fn_GetElement fn_GetProfileElement FUNCTION