How can I add 5596 + 00003 to get 559600003 in SQL Server?
I tried with the following query:
select 5596 + '00003'
But it is giving 5599 and I want this to show 559600003.
3 Answers
You can also use CONCAT():
SELECT CONCAT(5596, '00003')
CONCAT() does not require any explicit conversion.
SQL Server attempts to return the result in the datatype of the first part of the calculation which in your case is a number. It can happily convert the second part of the calculation into a number, and therefore does so.
To obtain the result you want you must convert the first part of the calculation to a string e.g.
select convert(varchar, 5596) + '00003'
Note: convert(varchar,x) uses a default length of 30 which is probably enough for most numbers.
CONCAT(), as in one of the other answers is probably a better solution.
You can use CAST
SELECT CAST(5596 AS varchar) + '00003'; -- return 559600003
Because :
- CAST is more easier to read than CONVERT
- CAST is ANSI-SQL compliant : it means that the CAST function can be used by many databases
But in case you have a date type CONVERT is more flexible and contains more options than CAST
For more details take a look : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15
Related