Aligning Text Data - SQL Server

Pandians
Posted by Pandians under Sql Server category on | Views : 8626
Aligning Text Data - SQL Server

Scenario : I just want to align the table data when I use SELECT Statement in SQL SERVER.

1. Declaring Variables Required :
DECLARE @Align VARCHAR(1)
SELECT @Align = 'R' (or) 'L'

2. Script for aligning the table data :
SET NOCOUNT ON
SELECT CASE @Align WHEN 'R' THEN REPLICATE(' ',50-LEN(DataType)) + DataType WHEN 'L' THEN DataType + REPLICATE(' ',50-LEN(DataType)) ELSE 'Alighment Should be L / R' END 'Alignment' FROM SQL_DataTypes ORDER BY DataType

Note: Before we go to the scenario, We just want to chenge the Result To Text. I consideded that the Maximum length is 50
Query Menu --> Results To --> Results to Text
(or)
Press Ctrl + L

1. Creating Table and Sample Data:
CREATE TABLE SQL_DataTypes
(
DataType VARCHAR(50)
)
GO
INSERT SQL_DataTypes VALUES('BIGINT')
INSERT SQL_DataTypes VALUES('NUMERIC')
INSERT SQL_DataTypes VALUES('BIT')
INSERT SQL_DataTypes VALUES('SMALLINT')
INSERT SQL_DataTypes VALUES('DECIMAL')
INSERT SQL_DataTypes VALUES('SMALLMONEY')
INSERT SQL_DataTypes VALUES('INT')
INSERT SQL_DataTypes VALUES('TINYINT')
INSERT SQL_DataTypes VALUES('MONEY')
INSERT SQL_DataTypes VALUES('FLOAT')
INSERT SQL_DataTypes VALUES('REAL')
INSERT SQL_DataTypes VALUES('DATE')
INSERT SQL_DataTypes VALUES('DATETIMEOFFSET')
INSERT SQL_DataTypes VALUES('DATETIME2')
INSERT SQL_DataTypes VALUES('SMALLDATETIME')
INSERT SQL_DataTypes VALUES('DATETIME')
INSERT SQL_DataTypes VALUES('TIME')
INSERT SQL_DataTypes VALUES('CHAR')
INSERT SQL_DataTypes VALUES('VARCHAR')
INSERT SQL_DataTypes VALUES('TEXT')
INSERT SQL_DataTypes VALUES('NCHAR')
INSERT SQL_DataTypes VALUES('NVARCHAR')
INSERT SQL_DataTypes VALUES('NTEXT')
INSERT SQL_DataTypes VALUES('BINARY')
INSERT SQL_DataTypes VALUES('VARBINARY')
INSERT SQL_DataTypes VALUES('IMAGE')
INSERT SQL_DataTypes VALUES('CURSOR')
INSERT SQL_DataTypes VALUES('TIMESTAMP')
INSERT SQL_DataTypes VALUES('HIERARCHYID')
INSERT SQL_DataTypes VALUES('UNIQUEIDENTIFIER')
INSERT SQL_DataTypes VALUES('SQL_VARIANT')
INSERT SQL_DataTypes VALUES('XML')
INSERT SQL_DataTypes VALUES('TABLE')
GO

2.LEFT Aligning Data :
'L' - Stands for LEFT ( Other than 'R' will be considered as LEFT )
DECLARE @Align	VARCHAR(1)
SELECT @Align = 'L' -- (or) Other than 'R'
SET NOCOUNT ON
SELECT CASE WHEN @Align = 'R' THEN REPLICATE(' ',50-LEN(DataType)) + DataType WHEN LTRIM(@Align) <> 'R' THEN DataType + REPLICATE(' ',50-LEN(DataType)) END 'Alignment' FROM SQL_DataTypes ORDER BY DataType

3.RIGHT Aligning Data :
'R' - Stands for RIGHT
DECLARE @Align	VARCHAR(1)
SELECT @Align = 'R'
SET NOCOUNT ON
SELECT CASE WHEN @Align = 'R' THEN REPLICATE(' ',50-LEN(DataType)) + DataType WHEN LTRIM(@Align) <> 'R' THEN DataType + REPLICATE(' ',50-LEN(DataType)) END 'Alignment' FROM SQL_DataTypes ORDER BY DataType

Cheers

Comments or Responses

Login to post response