I have created a dummy table and inserted one record in that table. When i simply select that record it gives me unwanted leading space in the answer set.
As Integer ranges from -2147483648 to +2147483647, So it needs 10 characters for digits and1 for sign. By default when you convert an integer to characters it is right justfied inside character space.
Here you have 986 as integer so when it is converted into characters it is right justified in 4 characters space like +986.
You can try cast function it will trim all leading spaces. SELECT '(' || CAST((a+1) AS CHAR(3)) || ')' FROM dummy;
He is already using the function to_char for which the output will be in characters rite? I think it has to be some other issue not the casting i guess.
You are right the output will be in character using TO_CHAR, But upto my knowledge the to_char do not perform in implicit truncation while CAST perofrms imiplicit truncation and removes spaces :)
Thanks for the informations. I don't think that it is some casting issue as when i connect to my PROD server and query the same , i dont get any leading space and if i disconnect and then connect to my TEST server on the same sql window. i am getting the leading space in my result..
i dont know why is this happening...
Thanks & Regards,
Thanks for your valuable suggestion.. i got the reason ... the machine which was showing result with leading spaces in answer set when using
to_char is because of one UDF with the same name 'to_char'. So whenever this function was calling at the same machine it using to_char UDF instead of to_char inbuilt function.... :)