lunedì 6 maggio 2013

TSQL - Generate random string

Sometimes could be the need to generate random strings...let's consider as an example the case of a password reinizilizzation...
First of all we need a range of characters that we want to use in our final string...
declare @TABLE table(value char(1))
declare @i int, @strLen int
select @i = 65, @strLen=5

while @i <= 90 begin
   insert @TABLE select char(@i)
   select @i = @i + 1
end
-- 34 to 123 for more char
-- 48 to 57 for numbers
-- 65 to 90 for uppercase letters
-- 97 to 122 for lowercase letters
Now we have a table with all the char we want to include in our final string

lets generate n random elements from this repository.
This step is too consider also duplicate elements...
declare @TABLE__ table(value char(1))
declare @j int
select @j = 0
while @j<@strLen begin
   insert into @TABLE__
      select top 1 value from @TABLE ORDER BY newid()

   select @j = @j + 1
end
select * from @TABLE__
Now we just have to concatenate elements...
select substring(replace((
   SELECT ';'+ value FROM @TABLE__ a for xml path('')), ';', ''), 1, @strLen)
if we do not want to duplicate elements just do not consider @TABLE__ but simply
select substring(replace((
   SELECT ';'+ value FROM @TABLE ORDER BY newid() for xml path('')), ';', ''), 1, @strLen)

mercoledì 1 maggio 2013

TSQL - Xml path to concatenate

We could be in need of concatenate in a single string the contents of many columns and row in a table. Let's have a var table like that
DECLARE @TABLE table(id int identity(1,1),
     title varchar(50), textVal varchar(50))
insert into @TABLE
   select 'TitleOne', 'First Text'
   union all
   select '2', 'Text number two'
   union all
   select 'Three', ''
   union all
   select null, null
   union all
   select 'Row5', 'value 5'
   union all
   select 'NullOnlyValue', null
select * from @TABLE
id
title
textVal
1 TitleOne First Text
2 2 Text number two
3 Three
4 NULL NULL
5 Row5 value 5
6 NullOnlyValue NULL

and assume we could need to have a result like
title: textVal; ....
and so one for every row we can use for xml path('') to concatenate the fields as we need...
select '; '+ title +': '''+ isnull(textVal, '') +'''' from @TABLE for xml path ('')
then use stuff function to have the result without the first ;
select stuff((
   select '; '+ title +': '''+ isnull(textVal, '') +'''' from @TABLE for xml path ('')
   ), 1,1, '') concatenatedString
this will produce this expected result
-- TitleOne: 'First Text'; 2: 'Text number two'; Three: ''; NullOnlyValue: ''; Row5: 'value 5'