All Forums Database
barani_sachin 141 posts Joined 01/12
02 May 2013
Please explain the different behavious of substr

Please explain how this wotks.
SEL SUBSTR(CAST(12345 AS VARCHAR(5)),1,3)
This gives "123" as OP
whereas
SEL SUBSTR(12345,1,3)
gives only "12", why does when passing integer/number it always reduces one explictly and gives output?

shilpa1 4 posts Joined 02/13
02 May 2013

For integer first byte is a Signed Byte, which is used for storing sign "+/-". Thats why for positive value, you are getting " 12" as output.

dnoeth 4628 posts Joined 11/04
02 May 2013

SUBSTR(CAST(12345 AS VARCHAR(5)),1,3)

This is an explicit cast to a VARCHAR using ANSI style syntax, which results in a left-aligned string, the result is the same as a SUBSTR('12345', 1, 3).

SUBSTR(12345,1,3)

This implicit cast to a VARCHAR using Teradata style syntax, which results in a right-aligned string based on the FORMAT. 12345 is a SMALLINT with a FORMAT '-(5)9', up to five digits and a leading sign, the result is the same as a SUBSTR(' 12345', 1, 3).
 
Dieter

Dieter

barani_sachin 141 posts Joined 01/12
02 May 2013

Thanks for the explanation :)

You must sign in to leave a comment.