CODEWITHSUNDEEP
sqldatabaseinformix
CuriousCoder
CuriousCoder

Reputation: 1600

Cast from numeric to char with leading zeros in Informix

I am having problems trying to do a cast on a SQL query in Informix. I have a table with a column called ponbr that I am querying on. Its data type is CHAR(8).

Here is my query that works fine and returns records

select * from xxx_shp where '02573569' = ponbr

However, if I type this query, it returns nothing:

select * from xxx_shp where to_char(02573569) = ponbr

This also returns nothing: 

select * from xxx_shp where ponbr = CAST (02573569 AS char(8))

What am I doing wrong here?

Upvotes: 1

Views: 6612

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I'm pretty sure your problem is leading zeros. If you know that ponbr is a number, then do the comparison as a number:

where 02573569 = cast(ponbr as int)

Otherwise, include the leading zeros:

where to_char(02573569, '&&&&&&&&') = ponbr

Upvotes: 3

dnoeth
dnoeth

Reputation: 60462

Simply check the result returned by to_char(02573569) and CAST (02573569 AS char(8)), it's probably '2573569'

Assuming that Informix TO_CHAR works similar to Oracle you need to apply a format with leading zeroes:

where to_char(02573569, '00000000') = ponbr

Upvotes: 1

Related Questions