Jeffrey Jeffrey - 3 months ago 13
SQL Question

T-SQL to text: set column width as required

I've got a piece of sql script which I run with the output set to text.
The column it returns is set to

varchar(255)
, so in the text output the column is 255 chars wide.

I know I can use

Cast(ColumnName As VarChar(75))) As ColumnName


to restrict it down to 75 characters width, but what I want to achieve is that it automatically sets the width to the widest result it returns. Does anyone know if this is possible?

Answer

Use dynamic query to achieve the result.

See one sample script below.

CREATE TABLE #temp
(Column1 VARCHAR(50))

INSERT INTO #temp (Column1) VALUES ('1111-123'),('15454454aa-4545')

DECLARE @Delimit INT
DECLARE @queryString VARCHAR(MAX)

SELECT @Delimit=MAX(LEN(Column1)) FROM #temp --Getting the Length of largest column data

SET @queryString='SELECT CAST(Column1 AS VARCHAR('+CONVERT(VARCHAR(50),@Delimit)+'))Column1 
                  FROM #temp'
EXEC(@queryString)