• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

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.
0
emi_sastra
Asked:
emi_sastra
1 Solution
 
Dale BurrellCommented:
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 BurrellCommented:
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
 
Dale BurrellCommented:
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now