英文:
Concat string and Value in SQL
问题
请看下面的代码：
DECLARE @startTS datetime2(3) = DATEADD(MI, -20, GETDATE())
DECLARE @param nvarchar(max), @runID char(36) , @isDelta char(1)
-- 设置@isDelta的值，假设它的值是1
SET @isDelta = '1'
-- 将变量和字符串连接
SET @param = '{"SourceDatabase":"DWH", "SourceSchema":"dbo","SourceTable":"LU_ERs","TargetDatabase":"ADrS","TargetSchema":"ofr","TargetTable":"LU_ERs", "isDelta":"' + CAST(@isDelta AS nvarchar(1)) + '","startTS":"' + CAST(@startTS AS nvarchar(50)) + '"}'
-- 选择@param
SELECT @param
这个代码片段将@isDelta和@startTS的值与字符串连接，生成您所期望的 JSON 结果。
英文:
Please I need your help, I want to concatenate variable and string :
below my code :
DECLARE @startTS datetime2(3) = DATEADD(MI, -20, GETDATE())
DECLARE @param nvarchar(max), @runID char(36) , @isDelta char(1)
SET @param = '{"SourceDatabase":"DWH", "SourceSchema":"dbo","SourceTable":"LU_ERs","TargetDatabase":"ADrS","TargetSchema":"ofr","TargetTable":"LU_ERs"'+',"isDelta":"' + CAST(@isDelta AS nvarchar(1)) + '","startTS":"' + CAST(@startTS AS nvarchar(50)) + '"}'
SELECT @param
I get back NULL.
I want to get something like this :
{
"SourceDatabase": "DWH",
"SourceSchema": "dbo",
"SourceTable": "LU_ERs",
"TargetDatabase": "ADrS",
"TargetSchema": "ofr",
"TargetTable": "LU_ERs",
"isDelta": "1",
"startTS":"2023-04-10 16:04:30.493"
}
I specify this
{
"SourceDatabase": "DWH",
"SourceSchema": "dbo",
"SourceTable": "LU_ERs",
"TargetDatabase": "ADrS",
"TargetSchema": "ofr",
"TargetTable": "LU_ERs"
comes another variable.
答案1
得分: 0
如果您正在使用SQL SERVER，请尝试以下操作：
SET @param = '{ "SourceDatabase":"DWH", "SourceSchema":"dbo","SourceTable":"LU_ERs","TargetDatabase":"ADrS","TargetSchema":"ofr","TargetTable":"LU_ERs"'
+ ',"isDelta":"' + COALESCE(CAST(@isDelta AS nvarchar(1)),'''') + '","startTS":"' + CAST(@startTS AS nvarchar(50)) + '"}'
错误在于@isDelta是空的，所以使用COALESCE来返回一个默认值，如果@isDelta是空的话，例如空值''。
英文:
If you are using SQL SERVER then try this :
SET @param = '{"SourceDatabase":"DWH", "SourceSchema":"dbo","SourceTable":"LU_ERs","TargetDatabase":"ADrS","TargetSchema":"ofr","TargetTable":"LU_ERs"'+',"isDelta":"' + COALESCE(CAST(@isDelta AS nvarchar(1)),'') + '","startTS":"' + CAST(@startTS AS nvarchar(50)) + '"}'
The error is that the @isDelta is null, so use COALESCE to returns a default value if @isDelta is null exemple empty value ''
答案2
得分: 0
如果您使用 CONCAT 进行字符串连接，而不是老式的 +，该函数将为您处理数据转换和 NULL 问题。
{"SourceDatabase":"DWH", "SourceSchema":"dbo","SourceTable":"LU_ERs","TargetDatabase":"ADrS","TargetSchema":"ofr","TargetTable":"LU_ERs","isDelta":"","startTS":"2023-04-10 14:44:50.863"}
英文:
If you use CONCAT, instead of the old school + for string concatenation, the function will handle both your data conversions and your NULL issues for you.
SET @param = CONCAT(
'''{"SourceDatabase":"DWH", "SourceSchema":"dbo","SourceTable":"LU_ERs","TargetDatabase":"ADrS","TargetSchema":"ofr","TargetTable":"LU_ERs"'
,',"isDelta":"'
,@isDelta
,'","startTS":"'
,@startTS,'"}''');
Just substitute a comma for every + in your current concatenation, remove your explicit CASTs, and let the code do the work for you.
Result:
{"SourceDatabase":"DWH", "SourceSchema":"dbo","SourceTable":"LU_ERs","TargetDatabase":"ADrS","TargetSchema":"ofr","TargetTable":"LU_ERs","isDelta":"","startTS":"2023-04-10 14:44:50.863"}
Edited to add opening and closing single quotes per comment.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论