SELECT PROBLEM

Hi All,

I have below query :

1.  SELECT * FROM THSTATUSFORM

 WHERE Sandi_Laporan = 'LB'
 AND Sandi_Formulir LIKE '01%'
 AND Bulan_Data = '09'
 AND Tahun_Data = '2011'

This show one data

2. SELECT * FROM THSTATUSFORM
 
 WHERE Sandi_Laporan = 'LB'
AND RTRIM(Sandi_Formulir) = '01'
 AND Bulan_Data = '09'
 AND Tahun_Data = '2011'

This query show no data.

Sandi_Formulir Char(4).

What's the problem ?

Thank you.
LVL 1
emi_sastraAsked:
Who is Participating?
 
Dale BurrellConnect With a Mentor DirectorCommented:
The following converts the column to a varchar so the empty chars can be removed, and then removes all spaces and all non-breaking spaces.

and replace(replace(convert(varchar(4),Sandi_Formuliar), ' ', ''), char(160), '') = '01'
0
 
Dale BurrellDirectorCommented:
rtrim only removes whitespace... that field must have other characters in it... what is the data in the 1 record that shows from the first select?
0
 
emi_sastraAuthor Commented:
Here is the data :

LB      01        09      2011                    ADMINIST        10/8/2011 2:16:44 PM      ADMINIST        10/8/2011 2:16:44 PM

Thank you.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
emi_sastraAuthor Commented:
After I change Sandi_Formulir Char(4) to Sandi_Formulir VarChar(4) then the problem is gone.

Could we know the ASCI of the white space ? I want to know it space or other data.

Thank you.
0
 
hernst42Commented:
Yes char columns fill up the missing chars with spaces (char #32, or 0x20)
0
 
emi_sastraAuthor Commented:
- Yes char columns fill up the missing chars with spaces (char #32, or 0x20)
Not really

Here is the query for '01  '

(No column name)      (No column name)      (No column name)      (No column name)
48      49      32      160

Thank you.
0
 
Dale BurrellDirectorCommented:
http://www.ascii-code.com/

32 = space
160 = non-breaking space? Weird!

Anyway that explains why rtrim doesn't work.
0
 
emi_sastraAuthor Commented:
But where the 160 came from ?

Thank you.
0
 
Bhavesh ShahLead AnalysistCommented:
hi,

32 is for space and
160 is extended ascii code.

You can view at
http://www.asciitable.com/


you can change query to LEFT(Sandi_Formuliar,2)='01'
0
 
emi_sastraAuthor Commented:
I know the ascii code

 -you can change query to LEFT(Sandi_Formuliar,2)='01'
We can not do that, since some time it has 3 or 4 digits.

Thank you.

0
 
emi_sastraAuthor Commented:
Hi Dale,

It works.

Just for your info, the ascii 160 might came from the source when we copy paste  :

1. Web page.
2. Microsoft Office or others.

Thank you very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.