SQL Server : cannot get columns to output to varchar-CodePudding

I'm having an odd issue. I have uid and ui2 columns that I combine into a key column. Most of the time the uids are an alphanumeric combination and I don't have any issues.

However this set of data the uids are all numeric:

Input data:

 ------- ------- 
| uid   | uid2  |
 ------- ------- 
| 12345 | 98765 |
| 45645 | NULL  |
 ------- -------

What the output SHOULD look like:

 ------- ------- ------------ 
| uid   | uid2  | key        |
 ------- ------- ------------ 
| 12345 | 98765 | 1234598765 |
| 45645 | NULL  | 45645      |
 ------- ------- ------------

If the uid is not NULL then it's fine, however, if it is NULL, then it adds a 0 to the end of the uid. So where uid=45645 I end up with 456540

When I combined the two columns, I used CAST AS varchar(255).

I also tried to then CAST the entire result. Neither worked.

Here is my code:

SELECT 
    [uid] AS [id], 
    [uid2] AS [id2], 
    (CAST([uid] AS varchar(255))   CAST(IIF([uid2] IS NULL, ' ', [uid2]) AS varchar(255))) AS [key], 
    (CAST(CAST([uid] AS varchar(255))   CAST(IIF([uid2] IS NULL, ' ', [uid2]) AS varchar(255)) AS varchar(255))) AS [key]
FROM 
    [test]

And that is the output:

 ------- ------- ------------ ------------ 
| uid   | uid2  | key        | key        |
 ------- ------- ------------ ------------ 
| 12345 | 98765 | 1234598765 | 1234598765 |
| 45645 | NULL  | 456450     | 456450     |
 ------- ------- ------------ ------------

The IIF is to deal with the NULL. Otherwise it NULLs the entire thing. If I replace the ' ' with '-' then I get

Error converting data type varchar to float

which makes me think that even though everything is cast to varchar it's still numeric.

As a work around I could probably remove the last 0 from keys were uid2 is NULL, but that's kind of 'hacky' and I'd really like to know what's going on here.

CodePudding user response：

I think the problem is that SQL Server is trying to convert to int because you have . I think you just want to use CONCAT like following:

select CONCAT(uid, uid2) as key