We can use the following code to generate some random strings: So if we have a !! def', then we may have strings like 'abc 12 def' or 'abc 87 def' generated.įor canadian post code the pattern can be !' (the middle blank space will be kept as it is in the generated string, like 'V1A 2P5'Ĭreate function dbo.ucf_random_pattern nvarchar(max), int=0 )Įxternal name. Returns nvarchar(max) with execute as callerĮxternal name means one letter from a to z (both lower and upper cases), ! means one digit number, i.e. Use MSSQLTips - this is my test databaseĪlter database MSSQLTips set trustworthy on įrom 'C:\mssqltips\Random_String\bin\CLR_Rand_String.dll'Ĭreate function dbo.ucf_random_string int, int, int =0) In my case, after I build the application to generate a DLL file, which I put it under c:\MSSQLTips\Random_String\bin\ folder, I need to run the following to import the DLL into SQL Server 2012. StringBuilder sb = new StringBuilder(pattern.Length) String CharList = "abcdefghijklmnopqrstvvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" Public static SqlString fn_random_pattern(SqlString pat, SqlInt32 seed) Using (var rng = new RNGCryptoServiceProvider()) Public static SqlString fn_random_string(SqlInt32 minLen, SqlInt32 maxLen, SqlInt32 seed) Public partial class UserDefinedFunctions I will not repeat the steps about how to create/deploy an assembly with Visual Studio, but you can refer to the links in section to find the details. Generate a random string with a simple pattern defined by the pattern parameter, this function also has a seed parameter to. With the same random string when using the same seed. Generate a random string with its length specified, and also with a seed parameter, we ensure repeatablility.So in this tip, I will provide two CLR functions to meet the above-mentioned requirements. For heavy-load string generation, the pure T-SQL solution's performance is compromised.įor string manipulation inside SQL Server, the majority agree that a CLR function will be better positioned.Non-deterministic functions such as Rand(), NewID() and CRYPT_GEN_RANDOM() are not allowed to be used inside a UDF, which means you need to create some additional layer to bypass this limitation.However, just using pure T-SQL has two obvious disadvantages: Generally speaking, with pure T-SQL, we can use Rand(), NewID(), CRYPT_GEN_RANDOM() and Convert/Cast/Substring Here is one good discussion of this topic " Generating random There are many ways in T-SQL to generate random strings.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |