MS SQL Server View not allowing CONCAT

问题:

MS SQL Server 2014.

I have a SQL statement that works fine:

SELECT CONCAT (
        CAST(T1.[F1] AS INTEGER),
        CAST(T1.[F2] AS INTEGER)
        ) AS F3 
FROM mytable AS T1

If I then put this into a view, and try to run I receive the error:

Operand data type int is invalid for concat operator

F1 and F2 both contain decimals but I want them concatenating e.g.:

F1 = 123.00000
F2 = 456.00000

Therefore F3 = 123456

Why does the view not allow this and is there a solution?

回答1:

Don't use the visual designers.

They are buggy and in this case convert

    SELECT CONCAT (
        CAST(T1.[F1] AS INTEGER),
        CAST(T1.[F2] AS INTEGER)
        ) AS F3 
FROM mytable AS T1

to

SELECT { fn CONCAT(CAST(T1.F1 AS INTEGER), CAST(T1.F2 AS INTEGER)) } AS F3
FROM     mytable AS T1

Calling the highly limited ODBC escape sequence version of the function (that only accepts two parameters which must be strings)

Just use a standard new query window to execute your CREATE VIEW/ALTER VIEW/SELECT FROM operations and you won't get this issue.

回答2:

Please use concat function like below

SELECT {fn CONCAT('123 ', '456') }