5

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.

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

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.

enter image description here

Adam
  • 6,041
  • 36
  • 120
  • 208

2 Answers2

10

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.

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.

View

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

Function

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.

Test

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

Important Note

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

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Thanks! However, when I paste this `fn_getRandom_Value()` or `fn_getRandom_Value` into the table field `Default Value or Binding`, SQL Server Management Studio replaces it directly with `N'fn_getRandom_Value'`...so making it a string. How can I prevent that? – Adam May 09 '15 at 23:31
  • write t-sql yourself dont expect Management Studio to do your work :) – M.Ali May 09 '15 at 23:34
  • Maybe I did not explain myself clearly :) I want to use a default value for this column without having to insert that value via the INSERT statement. Just like I would add `newid()` or `getdate()` in that field. I'll updated my post with a screenshot. – Adam May 09 '15 at 23:44
  • 1
    I know exactly what you mean, all I am saying is use t-sql to create or alter table definition , SSMS is to do only minor jobs. anyway enter the Default value as `([dbo].[fn_getRandom_Value]())` – M.Ali May 09 '15 at 23:49
  • Thanks. This almost works. It looks like your example only uses uppercase characters and no lowercase? – Adam May 10 '15 at 05:54
4
SELECT SUBSTRING(REPLACE(CONVERT(varchar, NEWID()), '-', ''), 1, 10)
Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77
diego garber
  • 59
  • 5
  • I can't paste your code directly into the field `Default value or Binding` of the table. How would I do that? – Adam May 09 '15 at 20:22
  • 1
    A short paragraph or even a sentence or two by way of explanation would have gone a long way into making this a great answer. –  May 10 '15 at 00:36
  • 1
    @Flo - try it without the SELECT (i.e. just the nested functions). The whole statement is mean as a sample T-SQL statement that will display the result in a query window. –  May 10 '15 at 00:38
  • 1
    I had to specify the length of the varchar, then this command worked for me; I only wanted 1 character in the random column: `substring(replace(CONVERT([varchar](36),newid()),'-',''),(1),(1))` – bendecko Sep 27 '20 at 09:03