|
I created a Stored Procedure to create random strings of the length and complexity desired. It is based on the widely published spt_values query for near random numbers.
It is functional, but I'm not entirely happy with the script. There has to be more elegant way to handle different complexity settings, other than the IF...ELSE clauses I used.
CODE:
<br />
-- Random String Generator<br />
-- By: Jesse Wimberley<br />
-- 27 August 2008<br />
-- Description: Creates Random strings of <br />
-- variable length and complexity.<br />
CREATE PROCEDURE [dbo].[up_RanGen]<br />
@Return varchar(200) = '' OUTPUT,<br />
-- @Len for the length of the output<br />
@Len int = 8,<br />
--Complexity set with @Complex<br />
-- 1 = Uppercase Letters<br />
-- 2 = Uppercase and Lowercase Letters<br />
-- 3 = Uppercase, Lowercase and Numbers<br />
-- 4 = Complex Password<br />
@Complex int = 4<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
declare @Random varchar(200)<br />
-- @a thru @f set the ranges of acceptable values for each character<br />
declare @a int, @b int, @c int, @d int, @e int, @f int<br />
If @Complex = 1<br />
BEGIN<br />
set @a = 65<br />
set @b = 80<br />
set @c = 81<br />
set @d = 82<br />
set @e = 83<br />
set @f = 90<br />
END<br />
ELSE<br />
If @Complex = 2<br />
BEGIN<br />
set @a = 65<br />
set @b = 90<br />
set @c = 97<br />
set @d = 100<br />
set @e = 101<br />
set @f = 122<br />
END<br />
ELSE<br />
If @Complex = 3<br />
BEGIN<br />
set @a = 65<br />
set @b = 90<br />
set @c = 97<br />
set @d = 122<br />
set @e = 48<br />
set @f = 57<br />
END<br />
ELSE<br />
BEGIN<br />
set @a = 48<br />
set @b = 60<br />
set @c = 61<br />
set @d = 75<br />
set @e = 76<br />
set @f = 122<br />
END<br />
set @Random=''<br />
select @Random=@Random+char(n) from<br />
(<br />
select top (@Len) number as n from master.dbo.spt_values <br />
where (type='p' and number between (@a) and (@b)) or (type='p' and number between (@c) and (@d)) or (type='p' and number between (@e) and (@f))<br />
order by newid()<br />
) as t<br />
set @Return = @Random<br />
END<br />
The four complexity settings are based on ranges of values returned as their ascii equivalents. Upper/Lower/Numbers has three ranges of numbers. For simplicity, I stretched all four settings across three ranges, even when only one or two ranges were needed.
|
|
|
|
|
Hi,
how can i get info on the 2nd max(id) or 3rd min(money) record on a table.
I only know how to get min(X) or max(X) record on a table
Thanks
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Select Max(id)
From <code>table</code>
where id != (Select Max(id) From <code>table</code>)
The above will give you the second maximum id.
|
|
|
|
|
Another way:
<br />
select top 1 ColumName from TableName<br />
where ColumName < (select max(ColumName ) from TableName)<br />
order by TableName desc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
To select the 5th lowest (or n-th lowest) replace the "5" in this query with the number you want.
Select max(X) from
(Select top 5 id as X from idTable order by id asc)t
To select the n-th highest replace the "5" in this query with the number you want.
Select min(X) from
(Select top 5 id as X from idTable order by id desc) t
Cheers!
|
|
|
|
|
Hi,
i need some intel on arrays and loops. I only know how to use the while loop. I sometimes write store_procedures that have more 100lines.
Some tutorial would be a good help to me (on how to perform loops or array)
declare @id_user1 as int
declare @id_user2 as int
declare @id_user3 as int
...
declare @id_user10 as int
i can´t do this
while @a < 10 + 1
begin
set @id_user[@a] = 'whatever'
end
thanks
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Thats because you can't do sql like that, there is no concept of an array. To do it like that you need to create and execute the sql dynamically
declare @id_user1 as int
declare @id_user2 as int
declare @id_user3 as int
declare @sql nvarchar(2000)
declare @a int
set @a =1
while @a < 11
begin
select @sql = 'set @id_user' + convert(varchar,@a) + ' = 1'
exec (@sql)
select @a = @a +1
end
try Google for dynamic sql, there are lots of examples.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
The best way to simulate arrays in SQL is to use the table variable (which I see you posted about just before this post)
So to do the above, you would have something like this:
DECLARE @table TABLE
( id INT, id_user varchar(200))
declare @i INT
Set @i = 1
While @i <= 10
BEGIN
insert into @table(id, id_user)
values(@i,'whatever' + cast(@i as varchar))
Set @i = @i + 1
END
-- Test it:
Select * from @table
Hope this answers your previous question as well!
Cheers!
|
|
|
|
|
Hi,
how can something like this?
declare @my_table as table (id int,f_name varchar(50),l_name varchar(50))
insert into @my_table values (select id_user,first_name,last_name from dbo.users)
nelsonpaixao@yahoo.com.br
|
|
|
|
|
So whats the question?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I think you are looking for something like
declare @my_table as table (id int,f_name varchar(50),l_name varchar(50))
insert into @my_table (id, f_name, l_name)
select id_user, first_name, last_name
From dbo.users
|
|
|
|
|
I have a table called CostAverage which stores the average value of an item and the date that it was calculated on. Each item has its cost average calculated on different days, and only when changes are made to the item. Since the OnDate could be anything, and the table retains the history, I am looking for a way to get the most recent cost average for each item in the table. Here is a snapshot of the table:
CostAverage
PK CostAverageID
FK UserItemID
CostAverage
OnDate
At first glance, I threw down something like this:
SELECT UserItemID, CostAverage, MAX(OnDate)
FROM CostAverage
GROUP BY UserItemID, CostAverage
Since the CostAverage is always different, that of course returns nearly the entire table. I can't really use an aggregate on the CostAverage since I need the most recent.
Does anyone have a suggestion? I am sure that I wrote a similar query once in the past, but I can't seem to find it (or remember it)
|
|
|
|
|
Try this
SELECT UserItemID, CostAverage,OnDate
FROM CostAverage ca1
where OnDate = (SELECT MAX(OnDate)
FROM CostAverage ca2 where ca2.UserItemID = ca1.UserItemID)
Personally, I would add a flag to indicate the current record and maintain it via a trigger as the above may be slow depending on your indexing.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank you, that works quite well. The query runs around ~2 seconds, this table should be weeded out in the next 6 months during a refactor so I'm not too worried about performance yet.
Again, thanks.
|
|
|
|
|
No problem
Bob
Ashfield Consultants Ltd
|
|
|
|
|
suppose iam having a string like ...
ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well.
now this is the string now how can i find a string--- ramu was a good boy is existed or not from the above string .
|
|
|
|
|
Use Patindex
Where PATINDEX('%ramu was a good boy%', 'ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well') !=0
Bob
Ashfield Consultants Ltd
|
|
|
|
|
dear ashfield thank u for immediate reply...u gave
Where PATINDEX('%ramu was a good boy%', 'ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well') !=0
is this works in oracle then please the exact how i can use this query in oracle
|
|
|
|
|
Sorry, no idea - you didn't say you were using Oracle. I expected SQL Server like 99% of the questions.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
declare @val as varchar (255)<br />
set @val='ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well.'<br />
<br />
select charindex('ramu was a good boy',@val)
result:
1 = string exists
0 = string doesn't exists
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
In Oracle use INSTR (or it's variants):
WHERE 0 < INSTR('ramu was a good boy .he is studying in ngbs school .cultatta and he studies very well',
'ramu was a good boy')
Mika
|
|
|
|
|
we know that when we insert data,update data and delete data against any table then that record is stored in transaction log of sql server. so how can i see those data in a very friendly way. please help with sample sql code.
tbhattacharjee
|
|
|
|
|
As far as I know, there is no way through SQL to read the transaction log of any database. You might find some tools out there to read the transaction log, but you would need to be very careful that you don't interfere with the running database.
Why don't you create an audit table that would hold these before and after values of the data ? You can implement this logic by creating triggers that would write to this table during an insert or delete. This is very standard practice when developing a system that requires auditing functionality.
|
|
|
|
|
First, apologies for hijacking this thread...
What kind of table layout do you use for the Audit tables? Right now, we have some old legacy apps that are being rewritten and we are going to be adding auditing to them via triggers. Is there a good / standard practice format for the tables?
|
|
|
|
|
The format I've seen before is typically an exact copy of the original table being audited along with some additional info such as datatime, userID and transaction code (Insert,Update,Delete). We used to name these tables something like EMPLOYEE_AUDIT. By keeping the format close to the original table format, it made it easier to implement.
Take into consideration the size of these audit tables !
I have seen audit tables grow at 500MB per month, which can cause serious performance drain on your DB server. Google around for other ideas on how to implement auditing in your new system.
|
|
|
|