Trim of varchar

[subrat]

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?

[ShaneMuir]

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.

[priyadarshikunal]

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')

[ShaneMuir]

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

[zhzhs]

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

[dsscholar]

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.

[chulett]

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.