The same data with different white space.

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.
LVL 1
emi_sastraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sam JacobsDirector of Technology Development, IPMCommented:
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 LeadCommented:
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
emi_sastraAuthor Commented:
Hi All,

Still have the same result.

Thank you.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongSoftware Team LeadCommented:
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, IPMCommented:
Yep ... it's probably other (non-printable) characters.
emi_sastraAuthor 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 LeadCommented:
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.
Sam JacobsDirector of Technology Development, IPMCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor 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.
emi_sastraAuthor Commented:
Hi Sam,

Great.

Le me try it.

Thank you.
Ryan ChongSoftware Team LeadCommented:
@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, IPMCommented:
Make sure you take the latest version ... I just updated the UDF to allow spaces and a dash.
emi_sastraAuthor 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.
emi_sastraAuthor Commented:
Hi Sam,

The latest udf is working just fine.

Thank you very much for your help.
Sam JacobsDirector of Technology Development, IPMCommented:
You are most welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.