Solved

# SELECT PROBLEM

Posted on 2011-10-08
190 Views
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
Question by:emi_sastra

LVL 21

Expert Comment

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

LVL 1

Author Comment

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

LVL 1

Author Comment

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

LVL 48

Expert Comment

Yes char columns fill up the missing chars with spaces (char #32, or 0x20)
0

LVL 1

Author Comment

- 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

LVL 21

Expert Comment

http://www.ascii-code.com/

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

Anyway that explains why rtrim doesn't work.
0

LVL 1

Author Comment

But where the 160 came from ?

Thank you.
0

LVL 19

Expert Comment

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

LVL 1

Author Comment

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

LVL 21

Accepted Solution

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

LVL 1

Author Closing Comment

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

### Suggested Solutions

Query using IF-THEN-ELSE and Variables 8 54
Parameterised queries 4 54
SQL Date from a string 4 38
SQL Select Query problems 10 33
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…