Unable to find exact length of the number in Sybase IQ, when the data type is CHAR- Need workaround

[VIJSQL01]

hi,

i have a table in Sybase IQ and one of the column data type is defined as CHAR(5). The column has numbers which have 5 digits as well as 4. When i query the length of each row from that column, the length is returned as 5, even for the numbers which are actually 4 digits. But this is not the case with SQL server. SQL server gives exact length of the row.
Do we have any workaround for this problem. I tried to convert it to other data types dynamically while querying. None of the tricks worked out.
Please let me know, how to get exact count in SYBASE IQ.

 

[Olaf Doschke]

What are you using for querying Sybase in MSSQL? Or are you using a third party tool (such as a reporting tool) to query both Sybase and MSSQL. From what you describe the Sybase data type rather is a constant width char field, while the MSSQL analogin is varchar. Varchar lenggths will be variable, char lengths are constant. That also applies within MSSQL, you can have both of these types.

Bye, Olaf.

 

[johnherman]

The TRIM function, or RTRIM or LTRIM will remove spaces. If it is a leading zero, that can also be removed with a combination of INSTRING and SUBSTRING.

==================================
adaptive uber info galaxies (bigger, better, faster than agile big data clouds)

 

[VIJSQL01]

Thank you very much for all your suggestions. The RTRIM function has sufficed my requirement. i am using below query for checking the exact length of the string,

select zip_code, len(rtrim(zip_code)) from test