CODEWITHSUNDEEP
sqlsql-server-2008t-sql
DtotheG
DtotheG

Reputation: 1257

Joining number with a slash SQL

Got a query that when I try and run it it returns Conversion failed when converting the nvarchar value '0003/' to data type int.

Query is

SELECT

name,
date,
CAST(('0' + Location + '/' + ID) AS nvarchar(50)) AS [UniqNumber]

FROM
Table

Upon this it returns Conversion failed when converting the nvarchar value '0003/' to data type int. Location and ID are both ints.

Upvotes: 1

Views: 2526

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

Try this as you want to convert nvarchar into int

CAST(REPLACE(Location,'/',0) AS nvarchar(50)) + CAST(ID as nvarchar(50)) AS [UniqNumber]

output:

0030

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453378

You need to cast the inputs.

'0' + CAST(Location AS VARCHAR(20)) + '/' + CAST(ID AS VARCHAR(20))

int has higher data type precedence than the string datatypes. If you do string + int it will try and cast the string to an integer and add it rather than casting the integer to a string and concatenating it.

Upvotes: 2

Related Questions