How do I get the value of a lookup field stored in nvarchar field

35 pts.
Tags:
NVARCHAR
SharePoint
SQL Server
SQL Server Query
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...

Answer Wiki

Thanks. We'll let you know when a new response is added.

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!!!

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    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.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following