在SQL中使用包含日期字符串的变量进行更新语句。

huangapple
go评论100阅读模式
英文:

Use a variable containing a string of dates in an update statement in SQL

问题

Here is the translated code portion:

  1. 我想要一个脚本，它会更新一个日期表，如果日期存在于给定列表中，则将bankHoliday标志设置为true。
  3. 这个列表是用户每年或两年更新一次的：
  5. ```sql
  6. DECLARE @dateList VARCHAR(MAX) = '2024-01-01, 2024-03-29, 2024-04-01, 2024-05-06, 2024-05-27,
  7.     2024-08-26, 2024-12-25, 2024-12-26, 2025-01-01,
  8.     2025-04-18, 2025-04-21, 2025-05-05, 2025-05-26,
  9.     2025-08-25, 2025-12-25, 2025-12-26';

更新发生在一个较长脚本的底部，因此虽然我可以在顶部告诉用户“确保转到第300行左右并更新节假日列表”，但我更喜欢在顶部将此列表存储在一个变量中，然后在更新中引用它。

我遇到的第一个问题是要使其易于查看，它可以跨多行并包含空格，就像上面的示例一样。因此，我有以下代码来删除所有错误字符：

  1. -- 用单引号替换连字符
  2. SET @dateList = REPLACE(@dateList, ',', ''',''');
  4. -- 用单个空格替换多个空格和换行符
  5. SET @dateList = REPLACE(REPLACE(@dateList, CHAR(13) + CHAR(10), ' '), ' ', '');
  7. -- 在开头和结尾添加单引号
  8. SET @dateList = '''' + @dateList + '''';

此时执行 select @dateList 返回：

'2024-01-01','2024-03-29','2024-04-01','2024-05-06','2024-05-27','2024-08-26','2024-12-25','2024-12-26','2025-01-01','2025-04-18','2025-04-21','2025-05-05','2025-05-26','2025-08-25','2025-12-25','2025-12-26'

因此，现在，在脚本底部，我想在我的更新中使用 @dateList：

我尝试了以下代码，但每次都会收到相同的错误：

  1. UPDATE days
  2. SET    days.PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.   SELECT value FROM STRING_SPLIT(@dateList, ',')
  6. );
  1. UPDATE days
  2. SET    days.PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.   @dateList
  6. );
  1. UPDATE days
  2. SET    days.PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.   SELECT CONVERT(datetime, value) FROM STRING_SPLIT(@dateList, ',')
  6. );

再次说明，如果我将 @dateList 的结果直接放入更新中，它可以正常工作：

  1. UPDATE days
  2. SET    days.PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.   '2024-01-01','2024-03-29','2024-04-01','2024-05-06','2024-05-27','2024-08-26','2024-12-25','2024-12-26','2025-01-01','2025-04-18','2025-04-21','2025-05-05','2025-05-26','2025-08-25','2025-12-25','2025-12-26'
  6. );

但如果可能的话，我想避免这样做，因为它需要信任用户转到脚本底部。

  2. <details>
  3. <summary>英文:</summary>
  5. I am wanting to have a script which updates a table of days, setting the bankHoliday flag to true if it exists in a given list.
  7. This list is something a user would update every year or two:
  9. ```sql
  10. DECLARE @dateList VARCHAR(MAX) = &#39;2024-01-01, 2024-03-29, 2024-04-01, 2024-05-06, 2024-05-27,
  11.     2024-08-26, 2024-12-25, 2024-12-26, 2025-01-01,
  12.     2025-04-18, 2025-04-21, 2025-05-05, 2025-05-26,
  13.     2025-08-25, 2025-12-25, 2025-12-26&#39;;

The update happens at the bottom of a long script, so though I can tell the user at the top something like "--make sure to go to ~line 300 and update the list of bank holidays", I would prefer to have this list at the top in a variable and then refer to it down in the update.

The first problem I have is for it to be easily visible, it can be on multiple lines and have spaces, like the above example. So I have this bit of code following it to strip out all the wrong characters:

  1. -- Replace the dashes with single quotes
  2. SET @dateList = REPLACE(@dateList, &#39;,&#39;, &#39;&#39;&#39;,&#39;&#39;&#39;);
  4. -- Replace multiple spaces and new lines with a single space
  5. SET @dateList = REPLACE(REPLACE(@dateList, CHAR(13) + CHAR(10), &#39; &#39;), &#39; &#39;, &#39;&#39;);
  7. -- Add single quotes at the beginning and end
  8. SET @dateList = &#39;&#39;&#39;&#39; + @dateList + &#39;&#39;&#39;&#39;;

Doing select @dateList at this point returns this:
'2024-01-01','2024-03-29','2024-04-01','2024-05-06','2024-05-27','2024-08-26','2024-12-25','2024-12-26','2025-01-01','2025-04-18','2025-04-21','2025-05-05','2025-05-26','2025-08-25','2025-12-25','2025-12-26'

So now, down at the bottom of the script, i want to use @dateList in my update:

I tried the following, but each time get the same error:
Conversion failed when converting date and/or time from character string.

  1. UPDATE days
  2. SET    days.PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.   SELECT value FROM STRING_SPLIT(@dateList, &#39;,&#39;)
  6. );
  1. UPDATE days
  2. SET    days.PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.   @dateList
  6. );
  1. UPDATE days
  2. SET    days.PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.   SELECT CONVERT(datetime, value) FROM STRING_SPLIT(@dateList, &#39;,&#39;)
  6. );

Again, if i put the result of @dateList directly into the update, it works fine:

  1. UPDATE days
  2. SET    days.PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.   &#39;2024-01-01&#39;,&#39;2024-03-29&#39;,&#39;2024-04-01&#39;,&#39;2024-05-06&#39;,&#39;2024-05-27&#39;,&#39;2024-08-26&#39;,&#39;2024-12-25&#39;,&#39;2024-12-26&#39;,&#39;2025-01-01&#39;,&#39;2025-04-18&#39;,&#39;2025-04-21&#39;,&#39;2025-05-05&#39;,&#39;2025-05-26&#39;,&#39;2025-08-25&#39;,&#39;2025-12-25&#39;,&#39;2025-12-26&#39;
  6. );

but i want to avoid this if possible as it requires trusting the user to go to the foot of the script.

答案1

得分: 1

如前所述，varchar 变量只是一串数据，不会自动变成一个列表。

不要玩弄字符串拆分和转换，只需使用表变量或表值参数。同时，使用明确的日期格式，例如 yyyyMMdd 或 yyyy-MM-dd HH:mm:ss。

  1. DECLARE @dateList AS TABLE (value date PRIMARY KEY);
  2. INSERT @dateList (value) VALUES
  3. ('20240101'),
  4. ('20240329'),
  5. ('20240401'),
  6. ('20240506'),
  7. ('20240527'),
  8. ('20240826'),
  9. ('20241225'),
  10. ('20241226'),
  11. ('20250101'),
  12. ('20250418'),
  13. ('20250421'),
  14. ('20250505'),
  15. ('20250526'),
  16. ('20250825'),
  17. ('20251225'),
  18. ('20251226');

然后

  1. UPDATE days
  2. SET    PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.     SELECT value FROM @dateList
  6. );
英文:

As mentioned, a varchar variable is just a string of data, it doesn't automatically become a list.

Instead of messing around with splitting strings and conversion, just use a table variable or a Table Valued Parameter. Also use an unambiguous date format such as yyyyMMdd or yyyy-MM-dd HH:mm:ss

  1. DECLARE @dateList AS TABLE (value date PRIMARY KEY);
  2. INSERT @dateList (value) VALUES
  3. (&#39;20240101&#39;),
  4. (&#39;20240329&#39;),
  5. (&#39;20240401&#39;),
  6. (&#39;20240506&#39;),
  7. (&#39;20240527&#39;),
  8. (&#39;20240826&#39;),
  9. (&#39;20241225&#39;),
  10. (&#39;20241226&#39;),
  11. (&#39;20250101&#39;),
  12. (&#39;20250418&#39;),
  13. (&#39;20250421&#39;),
  14. (&#39;20250505&#39;),
  15. (&#39;20250526&#39;),
  16. (&#39;20250825&#39;),
  17. (&#39;20251225&#39;),
  18. (&#39;20251226&#39;);

And then

  1. UPDATE days
  2. SET    PublicHoliday = 1
  3. WHERE  days.Date IN
  4. (
  5.     SELECT value FROM @dateList
  6. );

答案2

得分: 0

以下是您提供的文本的中文翻译：

另一个答案展示了如何安全且正确地传递日期列表。当前尝试失败的原因是字符串中包含了换行符，而不仅仅是逗号和空白。

这会导致转换错误：

  1. select cast(value as date)
  2. from STRING_SPLIT(@dateList,';')

为了调查这个问题，可以使用TRY_CAST来返回无效日期的NULL值。以下是结果：

  1. select try_cast(value as date)
  2. from STRING_SPLIT(@dateList,';')

结果如下：

  1. 2024-01-01
  2. 2024-03-29
  3. 2024-04-01
  4. 2024-05-06
  5. 2024-05-27
  6. NULL
  7. 2024-12-25
  8. 2024-12-26
  9. 2025-01-01
  10. NULL
  11. 2025-04-21
  12. 2025-05-05
  13. 2025-05-26
  14. NULL
  15. 2025-12-25
  16. 2025-12-26

如果不能使用表值参数（TVP），一个相对安全的替代方法是使用JSON数组字符串，然后使用OPENJSON解析它。以下字符串可以被成功解析：

  1. DECLARE @dateList VARCHAR(MAX) = '[\"2024-01-01\", \"2024-03-29\", \"2024-04-01\", \"2024-05-06\", \"2024-05-27\",
  2.     \"2024-08-26\", \"2024-12-25\", \"2024-12-26\", \"2025-01-01\",
  3.     \"2025-04-18\", \"2025-04-21\", \"2025-05-05\", \"2025-05-26\",
  4.     \"2025-08-25\", \"2025-12-25\", \"2025-12-26\"]';
  6. select MyDate
  7. from OPENJSON(@dateList) with (MyDate date '$')

这将产生以下结果：

  1. MyDate
  2. ----------
  3. 2024-01-01
  4. 2024-03-29
  5. 2024-04-01
  6. 2024-05-06
  7. 2024-05-27
  8. 2024-08-26
  9. 2024-12-25
  10. 2024-12-26
  11. 2025-01-01
  12. 2025-04-18
  13. 2025-04-21
  14. 2025-05-05
  15. 2025-05-26
  16. 2025-08-25
  17. 2025-12-25
  18. 2025-12-26

这仍然不如表值参数（TVP）好，并且占用更多的实际空间。

英文:

The other answer shows how to safely and properly pass a list of dates. The reason the current attempt fails is that the string contains newlines, not just commas and whitespace.

This fails with a conversion error:

  1. select cast(value as date)
  2. from STRING_SPLIT(@dateList,&#39;,&#39;)

To investigate this, TRY_CAST can be used to return NULL for invalid dates. The result of :

  1. select try_cast(value as date)
  2. from STRING_SPLIT(@dateList,&#39;,&#39;)

is

  1. 2024-01-01
  2. 2024-03-29
  3. 2024-04-01
  4. 2024-05-06
  5. 2024-05-27
  6. NULL
  7. 2024-12-25
  8. 2024-12-26
  9. 2025-01-01
  10. NULL
  11. 2025-04-21
  12. 2025-05-05
  13. 2025-05-26
  14. NULL
  15. 2025-12-25
  16. 2025-12-26

If a TVP can't be used, a safe-ish alternative is to use a JSON array string and parse it with OPENJSON. The following string gets parsed without problems:

  1. DECLARE @dateList VARCHAR(MAX) = &#39;[&quot;2024-01-01&quot;, &quot;2024-03-29&quot;, &quot;2024-04-01&quot;, &quot;2024-05-06&quot;, &quot;2024-05-27&quot;,
  2.     &quot;2024-08-26&quot;, &quot;2024-12-25&quot;, &quot;2024-12-26&quot;, &quot;2025-01-01&quot;,
  3.     &quot;2025-04-18&quot;, &quot;2025-04-21&quot;, &quot;2025-05-05&quot;, &quot;2025-05-26&quot;,
  4.     &quot;2025-08-25&quot;, &quot;2025-12-25&quot;, &quot;2025-12-26&quot;]&#39;;
  6. select MyDate
  7. from OPENJSON(@dateList) with (MyDate date &#39;$&#39;)

This produces

  1. MyDate
  2. ----------
  3. 2024-01-01
  4. 2024-03-29
  5. 2024-04-01
  6. 2024-05-06
  7. 2024-05-27
  8. 2024-08-26
  9. 2024-12-25
  10. 2024-12-26
  11. 2025-01-01
  12. 2025-04-18
  13. 2025-04-21
  14. 2025-05-05
  15. 2025-05-26
  16. 2025-08-25
  17. 2025-12-25
  18. 2025-12-26

That's still not as good, and takes more actual space than a TVP.

答案3

得分: -1

DECLARE @dateList VARCHAR(MAX) = '2024-01-01, 2024-03-29, 2024-04-01, 2024-05-06, 2024-05-27, 2024-08-26, 2024-12-25, 2024-12-26, 2025-01-01, 2025-04-18, 2025-04-21, 2025-05-05, 2025-05-26, 2025-08-25, 2025-12-25, 2025-12-26';

DECLARE @dates TABLE (dt DATE)
INSERT INTO @dates
SELECT DATEFROMPARTS(LEFT(dt,4), SUBSTRING(dt, 6,2), SUBSTRING(dt, 9,2)) as date
FROM
(SELECT TRIM(value) as dt FROM string_split(@dateList, ',')) val

SELECT * FROM @dates

英文:

Create a table of values like this. Update with this table.

  1. DECLARE @dateList VARCHAR(MAX) = &#39;2024-01-01, 2024-03-29, 2024-04-01, 2024-05-06, 2024-05-27, 2024-08-26, 2024-12-25, 2024-12-26, 2025-01-01, 2025-04-18, 2025-04-21, 2025-05-05, 2025-05-26, 2025-08-25, 2025-12-25, 2025-12-26&#39;;
  3. DECLARE @dates TABLE (dt DATE)
  4. INSERT INTO @dates 
  5. SELECT DATEFROMPARTS(LEFT(dt,4), SUBSTRING(dt, 6,2), SUBSTRING(dt, 9,2)) as date
  6. FROM
  7. (SELECT TRIM(value) as dt FROM string_split(@dateList, &#39;,&#39;)) val
  9. SELECT * FROM @dates

huangapple
  • 本文由 发表于 2023年7月4日 20:33:01
  • 转载请务必保留本文链接：https://go.coder-hub.com/76612679.html
  • sql
  • tsql
  • varchar
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定