CHAR(n) - Truncating/Padding Strings in SQL Server Transact-SQL

Q

How Fixed Length Strings Are Truncated and Padded in SQL Server Transact-SQL?

✍: FYIcenter.com

A

When the length of the input string does not match the storage size of the fixed length string data type CHAR(n). SQL Server will:

  • If the input string of CHAR(n) has less than n bytes, it will be padded with space characters to become n bytes.
  • If the input string of CHAR(n) has more than n bytes, it will be truncated to n bytes.

The tutorial exercise shows you good examples of truncating and padding fixed length character strings:

-- Length matches the data type size
DECLARE @msg CHAR(36);
SET @msg = 'Welcome to FYIcenter.com SQL Server!';
PRINT '('+@msg+')';
----------------------------
(Welcome to FYIcenter.com SQL Server!)

-- Length is bigger than the data type size - truncated
DECLARE @msg CHAR(24);
SET @msg = 'Welcome to FYIcenter.com SQL Server!';
PRINT '('+@msg+')';
----------------------------
(Welcome to FYIcenter.com)

-- Length is smaller than the data type size - padded
DECLARE @msg CHAR(46);
SET @msg = 'Welcome to FYIcenter.com SQL Server!';
PRINT '('+@msg+')';
----------------------------
(Welcome to FYIcenter.com SQL Server!          )

 

Unicode String Literals in SQL Server Transact-SQL

Casting Strings to Wrong Code Pages in SQL Server Transact-SQL

Data Literals in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2017-05-13, 4673🔥, 0💬