CHAR Datatype and ConCat in Oracle and SQL Server.

Char is a Fixed length data type, provided by database to stored Character Data.
It will allocate fix bytes as per length specified for CHAR datatype and will pad with Space for remaining chunks.

With Variable declare as CHAR(20), Byte allocated will be always 20.

DECLARE @Var CHAR(20)='Hello'
SELECT DATALENGTH(@Var) as DataLen
GO

Output :
DataLen
20

Now lets see how it behave in case of CONCAT for Oracle and SQL Server.

SQL Server:
We are declaring a CHAR Datatype with Length(20), and trying to ConCat.

DECLARE @Var CHAR(20)='Hello-'
SET @Var= @Var+'SQL Server'
SELECT @Var
GO

OUTPUT: "Hello- "

In Case of SQL Server, even after Concat it only displaying previous assign Data due to initial padded space.
With we Declare CHAR(20) and assign it “Hello-“, it is actualy Stored as

“Hello-00000000000000”

0 represent Space and total DataLength is 20.

So any Concat is Beyond length specified for @Var i.e. 20, Hence Concat is not possible.
But it wont throw any Exception or Error, it will whitewash any ConCat.

Oracle:

SET serveroutput ON;
DECLARE
var_1 CHAR(20000) := 'HELLO-';
BEGIN
var_1 := var_1 || 'ORACLE';
dbms_output.put_line(var_1);
END;

Output:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"

Oracle will Throw exception for such Concat with CHAR Datatype.
Unless we Use TRIM, Oracle will throw exception for any Concat with CHAR Datatype.

About Deepak Mahto

Having 10 Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in SQL Server Learning and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s