locked
Select data and format output such that each output field. RRS feed

  • Question

  • I have a requirement where the output of a query I must develop has to be formatted very precisely to be used by another application that I cannot transfer data readily to using SQL Server.  

    What I need to know how to do using Transact SQL is to format the output of a string variable such that its length may be increased or decreased in the query result and how to cast the output of a query of a numeric variable to string and similarly vary the length of the output string.

    Please help if you can!

    Wednesday, April 1, 2015 6:10 PM

Answers

  • This should give you a start

    declare @varylenghth varchar
    DECLARE @Query NVARCHAR(4000)
    
    SET @varylenghth=CAST(10 AS VARCHAR(2))
    
    SET @Query='SELECT CAST(1 AS VARCHAR('+ @varylenghth+')), cast(2 AS varchar(10)), ''test'''
    
    EXECUTE SP_executeSQL @Query
    use Cast/convert function to change the output format of a result. if the length keeps varying then use a dynamic SQL and build the SQL Query with required length and conversions before executing it. Hope this helps.

    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:12 AM
    Wednesday, April 1, 2015 6:26 PM
  • Just a starting point...
    DECLARE @TestString varchar(20) = 'test',
    @TestInt int = 44;
    
    
    WITH cte AS
    (
    SELECT CAST(@TestString AS char(90)) AS TS, CAST(@TestInt AS char(90)) AS TI
    )
    
    SELECT DATALENGTH(TS) AS LengthTS, DATALENGTH(TI) AS LengthTI
    FROM cte


    If there was a problem - Yo, I'll solve it

    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:12 AM
    Wednesday, April 1, 2015 6:26 PM
  • Hi desgordon,

    In addition to above suggestion, before casting a number, you'd better notice the below conversion mistake.

    DECLARE @num FLOAT = 123.456 --this will cause an Arithmetic overflow error SELECT CAST(@num AS VARCHAR(6)) --to achieve the expected output, you have to convert the numeric to string type SELECT CAST(CAST(@num AS VARCHAR(MAX)) AS VARCHAR(6)) --Also you should notice the difference between VARCHAR and CHAR type --a fixed length CHAR will be right padding with space(' ') if the max length is not meet SELECT CAST('abc' AS CHAR(4)) AS [Str] UNION ALL SELECT CAST('abc' AS VARCHAR(4))

    --output Str 'abc ' --one space padded 'abc'


    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Thursday, April 2, 2015 9:41 AM
    • Proposed as answer by Eric__Zhang Monday, April 13, 2015 1:20 AM
    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:11 AM
    Thursday, April 2, 2015 9:39 AM

All replies

  • This should give you a start

    declare @varylenghth varchar
    DECLARE @Query NVARCHAR(4000)
    
    SET @varylenghth=CAST(10 AS VARCHAR(2))
    
    SET @Query='SELECT CAST(1 AS VARCHAR('+ @varylenghth+')), cast(2 AS varchar(10)), ''test'''
    
    EXECUTE SP_executeSQL @Query
    use Cast/convert function to change the output format of a result. if the length keeps varying then use a dynamic SQL and build the SQL Query with required length and conversions before executing it. Hope this helps.

    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:12 AM
    Wednesday, April 1, 2015 6:26 PM
  • Just a starting point...
    DECLARE @TestString varchar(20) = 'test',
    @TestInt int = 44;
    
    
    WITH cte AS
    (
    SELECT CAST(@TestString AS char(90)) AS TS, CAST(@TestInt AS char(90)) AS TI
    )
    
    SELECT DATALENGTH(TS) AS LengthTS, DATALENGTH(TI) AS LengthTI
    FROM cte


    If there was a problem - Yo, I'll solve it

    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:12 AM
    Wednesday, April 1, 2015 6:26 PM
  • Thanks much!!
    Wednesday, April 1, 2015 6:57 PM
  • Thanks very much!!
    Wednesday, April 1, 2015 6:58 PM
  • Hi desgordon,

    In addition to above suggestion, before casting a number, you'd better notice the below conversion mistake.

    DECLARE @num FLOAT = 123.456 --this will cause an Arithmetic overflow error SELECT CAST(@num AS VARCHAR(6)) --to achieve the expected output, you have to convert the numeric to string type SELECT CAST(CAST(@num AS VARCHAR(MAX)) AS VARCHAR(6)) --Also you should notice the difference between VARCHAR and CHAR type --a fixed length CHAR will be right padding with space(' ') if the max length is not meet SELECT CAST('abc' AS CHAR(4)) AS [Str] UNION ALL SELECT CAST('abc' AS VARCHAR(4))

    --output Str 'abc ' --one space padded 'abc'


    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    • Edited by Eric__Zhang Thursday, April 2, 2015 9:41 AM
    • Proposed as answer by Eric__Zhang Monday, April 13, 2015 1:20 AM
    • Marked as answer by Eric__Zhang Wednesday, April 15, 2015 1:11 AM
    Thursday, April 2, 2015 9:39 AM