question

Yacovs-5736 avatar image
0 Votes"
Yacovs-5736 asked EchoLiu-msft commented

sql server-adding a suffix to an integer field

I would like to display an Integer type field (Salary in this example) with a symbol suffix (%)
To do this I've cast it as a varchar, with the varchar size the maximum current length of the field (5)
However, what I would really like is to have the varchar size be programmatically the length of the field

SELECT
[salary]
,len(salary) LenSalary
,cast(salary as varchar(5))+'%' 'Salary+%'
--,cast(salary as varchar(len(salary)))+'%'

salary LenSalary Salary+%
10,000 5 10000%

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

Why?

If it is an integer column, go for varchar(10), as that is long as an integer value can be. Well, 11 if it is negative. Or simply say

select concat(Salary, ' %')

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Yacovs-5736,

Is the length of each data in your salary column the same? I guess it should be different. If the length of each data in the salary column is the same, that is, a fixed value, then you can try:

     select len(salary) LenSalary from yourtable 

then:

     select cast(salary as varchar(The fixed value from the previous step))+'%' from yourtable 

But this should not be the case.The actual length of each value in your salary column should be different, and the parameter in varchar can only be a fixed value, so your idea cannot be realized.

Please also try:

 select cast(salary as varchar)+'%' from yourtable

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.






5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.