DSXchange

Hello

Just need a small clarification...

I have a field (lets suppose COL_A) which is of CHAR data type. I changed it to VARCHAR in a later stage.

Now if i am passing a values with leading or trailing zeros and using trim function[TRIM(COL_A)] on it, then what would be output of COL_A.

Is the leading and trailing zeros will removed?

Leading and trailing zeros will not be removed, unless you specifically ask them to be removed.

The syntax TRIM(COL_A) will trim only white space. To remove the leading zeros i think the syntax is TRIM(COL_A,'L',0') although I could have the L and the 0 around the wrong way. Similarly there is option for removing trailing characters.

actually 'L' and '0' should be placed other way.

trim(COL_A,'0','L') which will remove only the leading zeros.

to remove leading and trailing zeros use

trim(COL_A,'0','B')

Thought as much - didn't have a system handy to check. Thanks for the correction though.

TRIM(COL_A,"0","L") is right, i have tried it.

For char function only trim is needed right, do we need to use it for varchar function also.. As varchar will take exactly the space required for the character.

You can still have trailing characters (including spaces) in a varchar that need to be removed, but don't fall into the trap of trying to trim trailing spaces from a char.