The same data with different white space.

emi_sastra
emi_sastra used Ask the Experts™
on
Hi All,

I try  below script :

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

Open in new window


with result :

 BarangCode	SerialNo	length
SCUBA 717 1072                          	A HITAM - 00001                        	17
SCUBA 717 1072                          	A HITAM - 00001                         	15
SCUBA 717 1072                          	A HITAM - 00002                         	15
SCUBA 717 1072                          	B BW - 00001                             	14
SCUBA 717 1072                          	B BW - 00001                            	12
SCUBA 717 1072                          	B BW - 00002                            	12                  

Open in new window


What is happened ?

How to make it the same ?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sam JacobsDirector of Technology Development, IPM

Commented:
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

Open in new window

Ryan ChongSoftware Team Lead

Commented:
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))

Open 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 

Open in new window


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

Author

Commented:
Hi All,

Still have the same result.

Thank you.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ryan ChongSoftware Team Lead

Commented:
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 

Open 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"
Sam JacobsDirector of Technology Development, IPM

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

Author

Commented:
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.
Ryan ChongSoftware Team Lead

Commented:
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.
Director of Technology Development, IPM
Commented:
Create a user-defined function:
Create Function [dbo].[StripNonAlphaChars](@strInput VarChar(1000))
Returns VarChar(1000)
AS
Begin
    Declare @strOutput as varchar(1000)
    Declare @StripChars as varchar(50)
    Set @StripChars = '%[^a-z0-9- ]%'
    Set @strOutput = @strInput
    While PatIndex(@StripChars,@strOutput) > 0
        Set @strOutput = Stuff(@strOutput, PatIndex(@StripChars, @strOutput), 1, '')

    Return @strOutput
End

Open in new window


To remove the current non-alpha characters:
Update tmstokbarangdetil2018 set SerialNo = dbo.StripNonAlphaChars(SerialNo)

Open in new window


To prevent it from happening again, you can also call the strip routine when you insert the field into the database.

Author

Commented:
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.

Author

Commented:
Hi Sam,

Great.

Le me try it.

Thank you.
Ryan ChongSoftware Team Lead

Commented:
@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.
Sam JacobsDirector of Technology Development, IPM

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

Author

Commented:
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.

Author

Commented:
Hi Sam,

The latest udf is working just fine.

Thank you very much for your help.
Sam JacobsDirector of Technology Development, IPM

Commented:
You are most welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial