CODEWITHSUNDEEP
t-sql
campo
campo

Reputation: 1902

tsql format as percentage

I have the following sql statement: 

SELECT 1, 2, 3

UNION ALL

SELECT CAST(1 AS VARCHAR) + '%',CAST(2 AS VARCHAR) + '%',CAST(3 AS VARCHAR) + '%'

And I am getting the following error:

Conversion failed when converting the varchar value '1%' to data type int.

I could change the first select statement to '1','2','3', but i have a many unions and only one row that has to have the percentage format. Is there any other alternatives that are available?

Thanks!

Upvotes: 2

Views: 1638

Answers (2)

Pranay Rana
Pranay Rana

Reputation: 176896

try this 

select cast(1 as varchar) ,cast(2 as varchar), cast(3 as varchar)

union all

select cast(1 as varchar) + '%',cast(2 as varchar) + '%',cast(3 as varchar) + '%'

Reason of error is you above select having 1,2,3 which is an integer values and you are doing union with the varchar value

so solution is you need to convert you above select data type to varchar

The second solution is

Select cast(col1 as varchar),cast(col2 as varchar),........... from
(
   .. all your union large query you have writting
) d

union all
select cast(1 as varchar) + '%',cast(2 as varchar) + '%',cast(3 as varchar) + '%'

Upvotes: 1

gbn
gbn

Reputation: 432261

You need to CAST all union clauses.

Every row in the final output with have the highest precedence datatype. int is higher than varchar, so 1% is cast to int with obvious results

select cast(1 as varchar), cast(2 as varchar), cast(3 as varchar)
union all
select cast(1 as varchar) + '%',cast(2 as varchar) + '%',cast(3 as varchar) + '%'

Or: you can not have a different datatypes/formats in one column.

Of course this is an example, but mixing ints and formatted floats makes no sense even if SQL did allow it

Upvotes: 2

Related Questions