query to remove null spaces

M

mccloud

I use a query to seperate a field.
SELECT Right([filler_0001],37) AS Item_no, Left([filler_0001],5) AS
Cus_type_CD, dbo_OEPRCFIL_SQL.prc_or_disc_1
FROM dbo_OEPRCFIL_SQL
ORDER BY Right([filler_0001],37);

Here is my issue, after seperation the (Item_no) field has trailing null
characters. How can I remove them?
 
D

Dirk Goldgar

mccloud said:
I use a query to seperate a field.
SELECT Right([filler_0001],37) AS Item_no, Left([filler_0001],5) AS
Cus_type_CD, dbo_OEPRCFIL_SQL.prc_or_disc_1
FROM dbo_OEPRCFIL_SQL
ORDER BY Right([filler_0001],37);

Here is my issue, after seperation the (Item_no) field has trailing
null characters. How can I remove them?
Click to expand...

When you say "null characters", do you really mean Chr(0)? Or do you
mean spaces? If it's just spaces, you could use

SELECT RTrim(Right([filler_0001],37)) AS Item_no

If it's just null characters, you could use

SELECT Replace(Right([filler_0001],37), Chr(0), "") AS Item_no

, providing that there are no embedded null characters, only leading or
trailing ones to be stripped off.

If it's some combination of null characters and spaces, you could use

SELECT RTrim(Replace(Right([filler_0001],37), Chr(0), "")) AS
Item_no
 
Top