Substring-select or where

Transact-SQL
1

Trying to figure out how to extract the last character from column UDF_UNIQUEKEY (7 character field) and place in a new column SIZE_INDEX. Still playing in the design view of my DB to learn but, trying to insert a SUBSTRING function in the SELECT statement is not working for me.

SELECT        dbo.CI_Item.UDF_UNIQUEKEY, (dbo.CI_Item.UDF_UNIQUEKEY = SUBSTRING(dbo.CI_Item.UDF_UNIQUEKEY, 7, 1)) AS SIZE_INDEX, dbo.PO_PurchaseOrderHeader.PurchaseOrderNo, dbo.PO_PurchaseOrderHeader.VendorNo, dbo.PO_PurchaseOrderDetail.LineKey, 
                        dbo.PO_PurchaseOrderDetail.LineSeqNo, dbo.PO_PurchaseOrderDetail.ItemCode, dbo.PO_PurchaseOrderDetail.QuantityOrdered, dbo.PO_PurchaseOrderHeader.DateUpdated, dbo.PO_PurchaseOrderHeader.TimeUpdated
FROM            dbo.PO_PurchaseOrderHeader LEFT OUTER JOIN
                        dbo.PO_PurchaseOrderDetail ON dbo.PO_PurchaseOrderHeader.PurchaseOrderNo = dbo.PO_PurchaseOrderDetail.PurchaseOrderNo LEFT OUTER JOIN
                        dbo.CI_Item ON dbo.PO_PurchaseOrderDetail.ItemCode = dbo.CI_Item.ItemCode
WHERE        (dbo.PO_PurchaseOrderHeader.VendorNo = '0001904')
2

If all you need is the last character - you can use RIGHT(expression, 1). If you have a fixed length column then SUBSTRING(expression, start, 1) - or a non-fixed length you could use SUBSTRING(expression, LEN(expression) - 1, 1).

With that said - the issue you have here is this:

(dbo.CI_Item.UDF_UNIQUEKEY = SUBSTRING(dbo.CI_Item.UDF_UNIQUEKEY, 7, 1)) AS SIZE_INDEX

You want one of these:

SIZE_INDEX = SUBSTRING(dbo.CI_Item.UDF_UNIQUEKEY,7,1)
SUBSTRING(dbo.CI_Item.UDF_UNIQUEKEY,7,1) AS SIZE_INDEX

So either alias = expression or expression AS alias

BTW - I would recommend moving away from using the GUI to construct your queries. There are way too many issues with it and it is just easier to open a new query window and write the code yourself.

The GUI doesn't work well with table aliases - and I recommend you learn how to use table aliases and construct your queries to always use a table alias.

SELECT t.column
  FROM dbo.myTable AS t
 WHERE t.other = 'some value';