How do I get the text value (versus the stored value) from a single/multi select attribute value in SQL?

Question

Originally posted by Unknown

I am writing a SQL query that returns attribute values from an attribute that is a single or multi select. The problem is the displayed text and stored value are different, like this:

So when I try to query the values in SQL it looks like this:

Is there a way to get the text value in SQL?

Answer 1

Originally posted by Unknown

You can use the following sub-query to get the text value:

(   SELECT [Text]
    FROM ( 
        SELECT 
               TRIM(LEFT(Value,CHARINDEX('^',Value)-1)) AS [Value]
           ,TRIM(RIGHT(Value,LEN(Value)-CHARINDEX('^',Value))) AS [Text]
        FROM string_split((
           SELECT AQ.[Value]
           FROM Attribute A
                  INNER JOIN AttributeQualifier AQ ON AQ.AttributeId = A.Id
           WHERE A.Id = AV.[AttributeId]
                      AND AQ.[Key] = 'values'), ',')
              ) T
    WHERE [Value] = AV.[Value] ) AS [TextValue]

If you are using an table alias different than "AV" for your Attribute Value table, you'll need to change that in the two places it appears in the sub query (AV.[AttributeId] and AV.[Value])

Comments

(Originally by Unknown) Thanks!

---

(Originally by Unknown) @MasonKinyon You're welcome