SET ANSI_DEFAULTS Off -- change to ON to raise error

DROP TABLE #SAP_CT_Codes
CREATE TABLE #SAP_CT_Codes (old_store_reference VARCHAR(8))
INSERT INTO #SAP_CT_Codes (old_store_reference) VALUES ('FOUR    '),('FOURx   ')

SELECT old_store_reference, LEN(old_store_reference), DATALENGTH(old_store_reference) 
FROM #SAP_CT_Codes
	

DROP TABLE #SAP_CT_detail
CREATE TABLE #SAP_CT_detail ([sap_site] VARCHAR(4))
INSERT INTO #SAP_CT_detail ([sap_site]) 
SELECT old_store_reference
FROM #SAP_CT_Codes

SELECT sap_site, LEN(sap_site), DATALENGTH(sap_site)
FROM #SAP_CT_detail

SELECT
	st.old_store_reference --,
	--distinct (st.old_store_reference), LEN(st.old_store_reference)
	--,len (st.old_store_reference)
FROM SAP_CT_Codes s
INNER JOIN stocktake so 
	ON s.stocktake_code = so.stocktake_code
INNER JOIN stocktakecount stk 
	ON so.stocktake_code = stk.stocktake_code 
	AND s.stocktake_code = stk.stocktake_code
	AND stk.docstatus_ind = 'C'
INNER JOIN stocktakecountline stkl 
	ON stk.stocktakecount_code = stkl.stocktakecount_code
INNER JOIN store st WITH (nolock) 
	ON s.store_code = st.store_code
INNER JOIN itemcoloursize ics WITH (nolock) 
	ON stkl.itemcoloursize_id = ics.itemcoloursize_id
INNER JOIN itemcolour ic WITH (nolock) 
	ON ics.itemcolour_id = ic.itemcolour_id
WHERE LEN(st.old_store_reference) > 4