User profile property values are stored in the database table UserProfile and profile property definitions in the table ProfilePropertyDefinition. Given an integer UserID and the profile property name, an SQL query similar to the following should work. Be sure to replace the {databaseOwner} and {objectQualifier} tokens with those used by your site. If you are running the query in the Host-->SQL dialog, checking the "Run as Script" box will automatically do the replacements for you.

DECLARE @UserID int
DECLARE @PropertyName nvarchar(50)

SET @UserID = 3
SET @PropertyName = 'City'

SELECT
     PropertyName,
     'PropertyValue' = Case When (PropertyValue Is Null) Then PropertyText Else PropertyValue End

FROM {databaseOwner}{objectQualifier}UserProfile UP
           Join {databaseOwner}ProfilePropertyDefinition PPD On UP.PropertyDefinitionID = PPD.PropertyDefinitionID

WHERE UP.UserID = @UserID AND PPD.PropertyName=@PropertyName

Note that the above query does not take into account that the profile property definition may have included a DefaultValue which should be returned for user's who have not supplied a value for the profile property.

I would also strongly caution against directly accessing core database tables as I have done in this example if you are retrieving the profile property value in a custom module. In that case, access to the profile property values should be done through the Profile property of the UserInfo object. The Profile property returns a ProfileProperty object which provides methods such as GetPropertyValue and SetProfileProperty. Because core tables and core stored procedures are subject to change with version upgrades, it is much more preferable to make use of core API's whenever possible. Only use direct access to core tables when you must design an SQL query such as when using the reports module.

I found this in another post -- worked great for me:
 
value = GetProfilePropertyValue(fieldYouWantToRetrieve).ToString()
Function GetProfilePropertyValue(ByVal propertyName As String) As String 

    Dim value As String 
    Dim ppd As ProfilePropertyDefinition = UserInfo.Profile.GetProperty(propertyName) 

    If ppd Is Nothing Then 
        value = "" 
    Else 
        value = ppd.PropertyValue 
        If value Is Nothing Then 
            If String.IsNullOrEmpty(ppd.DefaultValue) Then 
                value = String.Empty 
            Else 
                value = ppd.DefaultValue 
            End If 
        End If 
    End If
Return value
End Function