CODEWITHSUNDEEP
sqlsql-servert-sql
Mari Gowda
Mari Gowda

Reputation: 31

Add number + varchar to get string concatenation

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.

Upvotes: 2

Views: 107

Answers (3)

Salma EL HAJRAOUI
Salma EL HAJRAOUI

Reputation: 196

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://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

Upvotes: 2

Dale K
Dale K

Reputation: 27289

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.

Upvotes: 5

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can also use CONCAT():

SELECT CONCAT(5596, '00003')

CONCAT() does not require any explicit conversion.

Upvotes: 8

Related Questions