Unable to filter the data because of spaces in data in SQL Server 2008 [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

select DISTINCT rtrim(ITEMID)   
FROM [MIS-028].[AX-DW-QTY].[dbo].ITEMQTYHISTORY_2   
where ITEMID = 'S991071215'

This record is present in table but when im going to filter with this id not getting any record because in this id there is some space than i am use ltrim,rtrim after that also im not ge开发者_如何学编程tting any record so plz tell what i have to do

---

Try trimming the filter condition:

select DISTINCT rtrim(ITEMID) 
FROM [MIS-028].[AX-DW-QTY].[dbo].ITEMQTYHISTORY_2 
where RTrim(ITEMID) = 'S991071215'

or even:

select DISTINCT rtrim(ITEMID) 
FROM [MIS-028].[AX-DW-QTY].[dbo].ITEMQTYHISTORY_2 
where LTrim(RTrim(ITEMID)) = 'S991071215'

Note: Doing this makes the filter non-sargable, and will not be able to utilise an index if one is present.

---

The issue is where you are applying the RTRIM.

SELECT DISTINCT RTRIM(ItemID)   
FROM   [MIS-028].[AX-DW-QTY].[dbo].ITEMQTYHISTORY_2   
WHERE  ItemID = 'S991071215'

The query above says 'Get all item qty history where the ItemID = 'S991071215', then remove all spaces to the right of ItemID, then get only the distinct ones'.

SELECT DISTINCT ItemID   
FROM   [MIS-028].[AX-DW-QTY].[dbo].ITEMQTYHISTORY_2   
WHERE  RTRIM(ItemID) = 'S991071215'

The query above says 'Get all the ItemID from the item qty history, then remove all spaces to the right of ItemID, then filter the results to only get records where ItemID = 'S991071215', then get only the distinct ones'.

Essentially, the functions in the first line are applied after the results are retrieved, whilst functions in the WHERE clause are applied whilst getting the results. Also, as Mitch suggests, LTRIM(RTRIM(ItemID)) will remove all spaces to the right, then use that result to remove all spaces to the left.