Skip to main content

MSSQL: Generate Random String Using SQL

The following will generate a random fixed-length string using SQL. Simply pass in a string of accepted characters and the length of the random string to be generated.

IF ISNULL(object_id('cspRandomString'),0)<>0 DROP PROC cspRandomString
IF ISNULL(object_id('fnGenRandomInteger'),0)<>0 DROP FUNCTION fnGenRandomInteger
CREATE function fnGenRandomInteger(@MinValue int, @MaxValue int, @Seed float)
returns int
-- =============================================
-- Author:        Loh Hon Chun
-- =============================================
 return ((@MaxValue + 1) - @MinValue) * @Seed + @MinValue
CREATE procedure cspRandomString (
 @Result varchar(255) output,
 @AcceptedChars varchar(255),
 @Length int
-- =============================================
-- Author:        Loh Hon Chun
-- =============================================
 set nocount on

 declare @ret char(255)
 declare @i int, @pos int
 set @Result = ''
 set @i = 0

 if LEN(@AcceptedChars) > 0
  while @i < @Length
   set @pos = dbo.fnGenRandomInteger(1, LEN(@AcceptedChars), RAND(CONVERT(VARBINARY, NEWID())))
   set @Result = @Result + CAST(SUBSTRING(@AcceptedChars, @pos, 1) AS VARCHAR(1))
   set @i = @i + 1

declare @result varchar(255)
declare @acceptedChars varchar(255)
declare @length int

set @acceptedChars = '0123456789'
set @length = 7
exec cspRandomString @Result=@result output, @AcceptedChars=@acceptedChars, @Length=@length
print @result

Popular Posts

Ho Ching named 5th most powerful and is mistaken as first lady by Forbes

Forbes named Singapore Prime Minister wife and CEO of Temasek Holdings, Ho Ching, as the 5th most powerful woman in the world. Ho Ching is mistaken as Singapore's first lady! OMG!

I wonder how can Forbes makes such a blunder. For a complete list, refer to here.

My opinion on PRUinvestor guaranteed plus product

I got to know of Prudential's new product PRUinvestor guaranteed plus only recently.

From agents, it is said to be 1) 100% Capital Guaranteed at maturity, 2) 2.5% p.a. interest guaranteed (Total 13.14%), 3) able to accept both cash and SRS, 4) and telling people application date for such "cool" product is ending soon but that's not really the case. I do not like the way products are marketed by agents.

1. 100% Capital Guaranteed at maturity

It is not really 100% guaranteed if it is guaranteed upon 36 months.

2. Sometimes marketed as 13.14%

This is a common tactic to "fool" aunties and uncles who are unaware of the compounding effect.

3. Able to accept SRS

At the rate of 2.5% p.a., I would say investing SRS monies in such product does not make sense to me since the new CPF interest rate structure is close to 2.5% p.a. too. At least CPF is truly 100% capital guaranteed.

4. Publish application date to be earlier than actual

This tactic attempts to make consumers make r…

How to stop FortiClient from starting automatically?

Installed FortiClient recently but the challenge in disabling the application/service from running automatically on every start-up annoyed me. Attempt to stop 'FortiClient Service Scheduler' only return 'Parameter is incorrect' error message.

An article on Technet help solve my trouble. To stop FortiClient from starting automatically, try the following:
Shut down FortiClient from the system tray.
Run net stop fortishield on command prompt.
Run msconfig.
On msconfig, switch to the Services tab. Clear the FortiClient Service Scheduler check box and click Apply.Run services.msc on command prompt to open up show all available services.Look for FortiClient Service Scheduler. Switch Startup type to Manual.Restart your computer. FortiClient should not be running automatically the next time round. Hope it helps.