Leading Spaces in answer set in SQLA

Database
Enthusiast

Leading Spaces in answer set in SQLA

Hello All,

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. 

CREATE TABLE dummy (a INTEGER);

INSERT INTO dummy VALUES (986);

SELECT '(' || TO_CHAR(a+1) || ')' FROM dummy;

result:

(     987)  <---- Notice the leading space in result

If i tried the same on different machine, the result is free from leading space. 

I am wondering if it is a bug or any setting in Teradata Sql Assistant. I tried it in all TD 12, TD13.10, TD 14 versions but did not get rid of this.


Any help would be definitely appreciated.

Thanks & Regards,

Sharib Hussain

8 REPLIES
Enthusiast

Re: Leading Spaces in answer set in SQLA

Hi,

I have ran the same query in my machine i am not getting any leading space. My version is TD14.

Enthusiast

Re: Leading Spaces in answer set in SQLA

Hi,

 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;

Khurram
Enthusiast

Re: Leading Spaces in answer set in SQLA

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.

Enthusiast

Re: Leading Spaces in answer set in SQLA

Hi Barani,

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

Khurram
Enthusiast

Re: Leading Spaces in answer set in SQLA

Thanks Khurram for the information :-)

Enthusiast

Re: Leading Spaces in answer set in SQLA

Hi All,

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,

Sharib Hussain

Enthusiast

Re: Leading Spaces in answer set in SQLA

What if you use TRIM ?

Enthusiast

Re: Leading Spaces in answer set in SQLA

Hi All,

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....  :)