Please explain the different behavious of substr

Database
Enthusiast

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?

Tags (2)
3 REPLIES

Re: Please explain the different behavious of substr

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.

Junior Contributor

Re: Please explain the different behavious of substr

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

Enthusiast

Re: Please explain the different behavious of substr

Thanks for the explanation :)