如何在SQL中转换数据类型? [英] How to convert data type in SQL?

问题描述

SELECT SUBSTRING((从INFORMATION_SCHEMA.COLUMNS中选择(``select''，''+ COLUMN_NAME +''``+ DATA_TYPE +''''+''(''+ CHARACTER_MAXIMUM_LENGTH +'')''

在哪里TABLE_NAME =''emp_payment''
FOR XML PATH(''''))，2,200000)''


如果我执行此查询会出错...

SELECT SUBSTRING((select '',''+COLUMN_NAME+'' ''+DATA_TYPE+'' ''+''(''+CHARACTER_MAXIMUM_LENGTH+'')''from INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=''emp_payment''
FOR XML PATH('''')),2,200000)''


If i execute this query am getting error...

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '(' to data type int.

请告诉我该解决方案是什么

我尝试过的事情:

SELECT SUBSTRING((选择``，''+ COLUMN_NAME +''``+ DATA_TYPE +''''+ CONVERT(INT，''('')+ CHARACTER_MAXIMUM_LENGTH + CONVERT(INT，'')'')从INFORMATION_SCHEMA.COLUMNS

在哪里TABLE_NAME =''emp_payment''
FOR XML PATH(''''))，2,200000)

please tell me what is the solution for this

What I have tried:

SELECT SUBSTRING((select '',''+COLUMN_NAME+'' ''+DATA_TYPE+'' ''+CONVERT(INT,''('')+CHARACTER_MAXIMUM_LENGTH+CONVERT(INT,'')'') from INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME=''emp_payment''
FOR XML PATH('''')),2,200000)

推荐答案

在CONVERT中包装数字值:

Wrap the numeric value in CONVERT:

... + CONVERT(NVARCHAR(6), CHARACTER_MAXIMUM_LENGTH) + ...

[edit]
考虑一下，您可能还需要检查是否为空-只有VARCHAR和NVARCHAR列具有非空值

[edit]
Thinking about it, you probably want to check that for nulls as well - only VARCHAR and NVARCHAR columns have non-null values

... + CONVERT(NVARCHAR(6), ISNULL(CHARACTER_MAXIMUM_LENGTH, -1)) + ...

[/edit]

[/edit]

报价:

尝试以下查询，让我知道是否有任何问题

SELECT SUBSTRING((select``，''+ COLUMN_NAME +''``+ DATA_TYPE +''(''+ Convert(Varchar(20)，CHARACTER_MAXIMUM_LENGTH)+'')''''从INFORMATION_SCHEMA.COLUMNS
在哪里TABLE_NAME =''emp_payment''
FOR XML PATH(''''))，2,200000)

Try below query and let me know if any questions

SELECT SUBSTRING((select '',''+COLUMN_NAME+'' ''+DATA_TYPE+''(''+Convert(Varchar(20),CHARACTER_MAXIMUM_LENGTH)+'')'' from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=''emp_payment''
FOR XML PATH('''')),2,200000)

这篇关于如何在SQL中转换数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！