SQL Server列的默认随机10个字符串值 [英] Default random 10 character string value for SQL Server column

问题描述

我的表格[来宾]中有一列 rndm 。现在，对于表的默认值或绑定字段，每当插入新行时，我都希望自动在此列中插入10个字符的随机字符串作为默认值

I have a column rndm in my table [guests]. Now, for the field Default value or Binding for the table, whenever a new row is inserted I want to automatically insert a 10-character random string into this column as the default value.

此随机字符串不得包含特殊字符，只能包含 a-zA-Z0-9 中的字符。实现此目的的最佳方法是什么？

This random string may not contain special characters, only characters from a-zA-Z0-9. What is the best approach to achieve this?

要明确：我不想在.NET代码中生成此随机字符串，而是希望生成它在SQL Server中。而且我想将此字符串生成直接粘贴到表的默认值或绑定字段中，所以不要单独的SQL语句。因此，例如，当将 getdate（）粘贴到默认值或绑定字段中时，它就可以工作。

To be clear: I don't want to generate this random string in my .NET code, I want it to be generated within SQL Server. And I want to paste this string generation directly into the field Default value or Binding for the table, so not a separate SQL statement. So for example like it works when pasting getdate() into the field Default value or Binding.

推荐答案

扩展Deigo Garbar已经提出的建议。

Expanding on what Deigo Garbar has already suggested.

如果要将表达式用作表中的默认值，则需要将该表达式放入函数中。

If you want to use the expression as the default value in your table you would need to put this expression in a function.

但是问题是UDF函数不允许您在其中使用NEWID（）函数，它会抱怨无法在UDF中使用非确定性函数。 ...

But the problem is a UDF function will not allow you to use NEWID() function in it, it will complain about cannot use non-deterministic function in UDF bla bla....

一种解决方法是先创建一个视图，该视图简单地调用此表达式，然后在函数内部使用该视图，然后将该函数用作您的默认值表。

A way around will be to create a view first which simply calls this expression then use that view inside your function and then use that function as Default value for your table.

查看

CREATE VIEW dbo.vw_Function_Base
AS
SELECT substring(replace(convert(varchar(100), NEWID()), '-', ''), 1, 10) AS Rand_Value

功能

CREATE FUNCTION dbo.fn_getRandom_Value()
RETURNS VARCHAR(10)
AS
BEGIN
  DECLARE @Rand_Value VARCHAR(10);
SELECT @Rand_Value = Rand_Value
FROM vw_Function_Base

  RETURN @Rand_Value;
END

现在，您可以在表中将此功能用作默认值。

Now you can use this function as default value in your table.

测试

CREATE TABLE SomeTest_Table
 ( ID        INT
 , RandValue VARCHAR(10) DEFAULT dbo.fn_getRandom_Value()
 )
GO

 INSERT INTO SomeTest_Table(ID)
 VALUES (1)
GO

 SELECT * FROM SomeTest_Table

重要提示

我不认为这部分GUID值将始终是唯一的，因此请小心。

I am not convinced this Part of GUID value will always be unique, so be careful.

这篇关于SQL Server列的默认随机10个字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！