Richard Edwards posted on March 09, 2005 20:41
The last UDF we need, is one that will take the field values from aspnet_Profile and parse them to return the proper data. This one, named fn_GetProfileElement also takes 3 parameters; @fieldName (the profile fieldname we want to grab), @fields (the string from PropertyNames), and @values (the string values from PropertyValuesString).
CREATE FUNCTION dbo.fn_GetProfileElement
(
@fieldName AS NVARCHAR(100),
@fields AS NVARCHAR(4000),
@values AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
-- If input is invalid, return null.
IF @fieldName IS NULL
OR LEN(@fieldName) = 0
OR @fields IS NULL
OR LEN(@fields) = 0
OR @values IS NULL
OR LEN(@values) = 0
RETURN NULL
-- locate FieldName in Fields
DECLARE @fieldNameToken AS NVARCHAR(20)
DECLARE @fieldNameStart AS INTEGER, @valueStart AS INTEGER, @valueLength AS INTEGER
-- Only handle string type fields (:S:)
SET @fieldNameStart = CHARINDEX(@fieldName + ':S',@Fields,0)
-- If field is not found, return null
IF @fieldNameStart = 0 RETURN NULL
SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 3
-- Get the field token which I've defined as the start of the field offset to the end of the length
SET @fieldNameToken = SUBSTRING(@Fields,@fieldNameStart,LEN(@Fields)-@fieldNameStart)
-- Get the values for the offset and length
SET @valueStart = dbo.fn_getelement(1,@fieldNameToken,':')
SET @valueLength = dbo.fn_getelement(2,@fieldNameToken,':')
-- Check for sane values, 0 length means the profile item was stored, just no data
IF @valueLength = 0 RETURN ''
-- Return the string
RETURN SUBSTRING(@values, @valueStart+1, @valueLength)
END
Now, all we have to do is provide the proper call in our T-SQL query to this function and we should have our data. To do this, simply call it as follows:
SELECT dbo.fn_GetProfileElement('PostalCode',PropertyNames,PropertyValuesString) FROM aspnet_Profile
In this example, I'm grabbing only the PostalCode field, but you could grab any field that is stored in the profile data. There are many other fields you can grab as shown in the following list: Unit, Street, City, Region, PostalCode, Country, Telephone, Fax, Cell, Website, IM, TimeZone and PreferredLocale.