How do I get the value of a lookup field stored in nvarchar field
Home > IT Answers > SQL Server > How do I get the value of a lookup field stor...
3300Metairie
35 pts.
0
Q:
How do I get the value of a lookup field stored in nvarchar field
SQL Server, SharePoint, SQL Server Query, NVARCHAR
I have a list with a lookup field which is being stored in an nvarchar column. I'm trying to access the value in a Sql Server query, but what comes back is this: ↓ ъ䅃㒳ꅉ
I've tried converting this column to every data type there is, and no success. Please help!! Thanks...
ASKED: Jun 30 2009  3:56 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
3300Metairie
35 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
I have actually found the answer to my own question. In Sharepoint I have a list where one of the values is a multi-value lookup field. I wanted to be able to create a query to return those values, and didn't know how to. Here is the answer:

select a.tp_id, --this is the company id
a.nvarchar1 Company_Name, --this is the company name
b.nvarchar1 --this is the service the company provides (multiple)
from UserData a,
UserDataJunctions j , -- For Industry Name
UserData b
where UPPER(a.tp_DirName) = 'LISTS/CLIENTS'
AND a.tp_LeafName = j.tp_LeafName
AND a.tp_DirName = j.tp_DirName
AND a.tp_SiteId = j.tp_SiteId
and a.tp_SiteId = b.tp_SiteID
AND j.tp_id = b.tp_id
and upper(b.tp_dirname) = 'LISTS/SERVICE LIST'
and j.tp_fieldid = 'a9b3e1dc-ef2f-4e05-add1-18d3dcbda86c'

In order to get the value of the tp_fieldid listed in the UserDataJunctions view, you have to query against the Lists view, which has a column called tp_fields. That column has one huge string where a description of each column is concatenated together. If you paste the value of tp_fields into notepad, you can search for the column display name. Once you find that, keep scrolling to the right in the data string until you find the ID="{a9b3e1dc-ef2f-4e05-add1-18d3dcbda86c}" and that value will be the tp_fieldid of your column.

select tp_fields
from lists
where TP_ID = 'E27F2AA2-49C9-4907-81BF-E593E90E4701' --List id for MAA Clients List


...I forgot to mention, the nvarchar value in my original question ended up having nothing to do with me finding the values. I thought they were somehow cryptically being stored in that column, but it doesn't matter, because I've found out how to do it.

THANKS!!!
Last Answered: Jul 1 2009  7:45 PM GMT by 3300Metairie   35 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Kccrosser   1850 pts.  |   Jul 1 2009  4:04PM GMT

We need some clarification here. Is your list stored as individual values (i.e., one value per row) or do you actually have a “list” embedded in one nvarchar field?

An NVARCHAR field is just a Unicode-enabled field. If you are storing Western text (i.e., USASCII characters) there should be no difference whether it is a VARCHAR or an NVARCHAR (except it can take twice the physical storage).

If your data was created and inserted using a foreign language that is not USASCII-compatible (e.g. Arabic, Chinese, Hebrew) and you are trying to represent it in ASCII, then you might see the funky characters.

What do you see if you simply execute a “select <column> from <table>” interactively? If it is displaying the funny characters, then that is what is actually stored in the table.

The last possibility (pretty unlikely) is that somehow you have a corrupted SQL Server install, where the Unicode support libraries are broken/missing.

 
0