The same data with different white space.

You may have spaces at the end of one or more records ... try:

select distinct BarangCode, SerialNo, len(Trim(serialno)) as length
from   tmstokbarangdetil2018
where barangcode = 'SCUBA 717 1072'
order by SerialNo

Select allOpen in new window

based on your result, it's obvious to say there are spaces for your field: SerialNo

to make it non-duplicate in your results, you can try:

1. Trim the values of field: SerialNo, and then re-execute the same Select SQL statement.

To trim the values:

Update tmstokbarangdetil2018 set SerialNo = Ltrim(Rtrim(SerialNo))

Select allOpen in new window

2. Trim the values in your Select SQL statement

select
distinct
BarangCode  
, Ltrim(Rtrim(SerialNo)) SerialNo 
, len(Ltrim(Rtrim(SerialNo))) as length
from tmstokbarangdetil2018
where barangcode = 'SCUBA 717 1072'
order by SerialNo 

Select allOpen in new window

the Trim function that Sam have suggested is available in MS SQL starting with 2017

Hi All,

Still have the same result.

Thank you.

what's the results when you run:

select
distinct
BarangCode  
, Ltrim(Rtrim(SerialNo)) SerialNo 
, len(Ltrim(Rtrim(SerialNo))) as length
from tmstokbarangdetil2018
where barangcode = 'SCUBA 717 1072'
order by SerialNo 

Select allOpen in new window

or the one suggested by Sam ?

if it didn't work so it probably mean the extra chars are not "white space"

Yep ... it's probably other (non-printable) characters.

Yes, both Sam and Ryan has the same result.

How to get rid of those white spaces ?
How to avoid this kind of things happen again ?

Thank you.

How to get rid of those white spaces ?

you got to identify what's the ascii of the char in the values, so then you can clean your data, probably by executing some replacing functions.

How to avoid this kind of things happen again ?

it depends on how these data were first being inserted, via some data sources? the data source need to be keyed in correctly and probably not being copy and paste from another data source, like Word, etc.

Hi Ryan,

How to get rid of those white spaces ?

 - you got to identify what's the ascii of the char in the values ?
Could we just replace if it is not char or number ?

-  so then you can clean your data, probably by executing some replacing functions.
How to do it ?

Thank you.

Hi Sam,

Great.

Le me try it.

Thank you.

@emi_sastra

Could we just replace if it is not char or number ?

How to do it ?

you probably can try the UDF that posted by Sam above and see if it's sufficient to trim the "white space" in your case.

Make sure you take the latest version ... I just updated the UDF to allow spaces and a dash.

Hi Sam,

I try this :

select
distinct
BarangCode  
, SerialNo, LEN(SerialNo) AS LenSeialNo
from tmstokbarangdetil2018
where barangcode = 'SCUBA 717 1072'
AND SerialNo LIKE '%A HITAM - 00001%'
order by SerialNo

The data are gone ?

Thank you.

Hi Sam,

The latest udf is working just fine.

Thank you very much for your help.

You are most welcome.