March 4, 2013 at 4:19 pm
I hardly ever use the word "stupid" in describing something but the people asking you to do this are freakin' stupid to their core! How many lawsuits do they want to endure by offending someone's sensibilities with randomly spelled out, highly offensive swear words? Even if you remove the bloody vowels, you can still get hammered over randomly constructed words like BTFCKR but at least you have a plausible defense for those. It won't do much for words like 1D10T or SH1T or even P00P, though. Like I said, this is a stupid, ST00P1D, incredibly 1D10T1C requirement. Let me know when this comes out so I can get in on the lawsuits that are going to happen. I need to retire as a millionaire. 😀
The easiest way to get around all of this is to generate two UNIQUEIDENTIFIERS, concatenate them together, and pick the number of characters you need. That way, there's virtually no chance of (English, at least) swear words and you can stop messing around with that ugly cursor! 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2013 at 3:46 am
Jeff Moden (3/4/2013)
I hardly ever use the word "stupid" in describing something but the people asking you to do this are freakin' stupid to their core! How many lawsuits do they want to endure by offending someone's sensibilities with randomly spelled out, highly offensive swear words? Even if you remove the bloody vowels, you can still get hammered over randomly constructed words like BTFCKR but at least you have a plausible defense for those. It won't do much for words like 1D10T or SH1T or even P00P, though. Like I said, this is a stupid, ST00P1D, incredibly 1D10T1C requirement. Let me know when this comes out so I can get in on the lawsuits that are going to happen. I need to retire as a millionaire. 😀The easiest way to get around all of this is to generate two UNIQUEIDENTIFIERS, concatenate them together, and pick the number of characters you need. That way, there's virtually no chance of (English, at least) swear words and you can stop messing around with that ugly cursor! 😉
Well, Jeff I agree with all the stuff you told...I am looking stupid now because of doing what they say 😀 ...But no other option.... Generating unique identifiers is not all my problem.... generating more than we could is our problem....you know the probability of choosing unique one character value from '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' cab be 36. So you can assign 36 values to 36 rows in a field....but how can we update for 40 rows...? Since i use IF statement to check for duplicates, when i get more number of rows to be updated more than 36.....i am unable to stop the cursor....since it is running through some application...
March 5, 2013 at 3:52 am
prakashr.r7 (3/5/2013)
Jeff Moden (3/4/2013)
I hardly ever use the word "stupid" in describing something but the people asking you to do this are freakin' stupid to their core! How many lawsuits do they want to endure by offending someone's sensibilities with randomly spelled out, highly offensive swear words? Even if you remove the bloody vowels, you can still get hammered over randomly constructed words like BTFCKR but at least you have a plausible defense for those. It won't do much for words like 1D10T or SH1T or even P00P, though. Like I said, this is a stupid, ST00P1D, incredibly 1D10T1C requirement. Let me know when this comes out so I can get in on the lawsuits that are going to happen. I need to retire as a millionaire. 😀The easiest way to get around all of this is to generate two UNIQUEIDENTIFIERS, concatenate them together, and pick the number of characters you need. That way, there's virtually no chance of (English, at least) swear words and you can stop messing around with that ugly cursor! 😉
Well, Jeff I agree with all the stuff you told...I am looking stupid now because of doing what they say 😀 ...But no other option.... Generating unique identifiers is not all my problem.... generating more than we could is our problem....you know the probability of choosing unique one character value from '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' cab be 36. So you can assign 36 values to 36 rows in a field....but how can we update for 40 rows...? Since i use IF statement to check for duplicates, when i get more number of rows to be updated more than 36.....i am unable to stop the cursor....since it is running through some application...
You can solve your immeditate problem by using the solution I proposed above - generate a table of all 36 values (or whatever) randomised relative to an incrementing numeric key. With each loop n of the cursor you choose the value corresponding to key n in the table. Once you've consumed the value corresponding to key 36, you exit the cursor loop. Not only will this solve your immediate problem, it will provide all 36 values, because your existing method allows the same value to be generated again and again, even if they are not used.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 5, 2013 at 4:01 am
You can solve your immeditate problem by using the solution I proposed above - generate a table of all 36 values (or whatever) randomised relative to an incrementing numeric key. With each loop n of the cursor you choose the value corresponding to key n in the table. Once you've consumed the value corresponding to key 36, you exit the cursor loop. Not only will this solve your immediate problem, it will provide all 36 values, because your existing method allows the same value to be generated again and again, even if they are not used.
Chris, First of all i want to thank you for the tolerance you have with me....
With each loop n of the cursor you choose the value corresponding to key n in the table.Once you've consumed the value corresponding to key 36, you exit the cursor loop.
i am not getting this part...Could you explain me please?
March 5, 2013 at 4:06 am
prakashr.r7 (3/4/2013)
Eugene Elutin (3/4/2013)
....and remaining rows with random unique values...
and if you have more than 36 (or, as per your sample which already contains two of one-char codes, 34) rows remaining, what code they should be set to? There will be no unique values left.
yes, as per my sample we can generate 34 new one char codes apart from those 2...but if it is more than 36, this is where i am getting kicked off....my process is keep on looping to get a new value...so how can i stop it, if it exceeds more than we could generate....?
Eugene, do you understand my problem now ? just curious
Sorry, but I don't.
I have asked what should happen with rows which cannot have unique on-character code set as all unique values already were used. Should such rows be left not-updated?
March 5, 2013 at 4:07 am
prakashr.r7 (3/5/2013)
You can solve your immeditate problem by using the solution I proposed above - generate a table of all 36 values (or whatever) randomised relative to an incrementing numeric key. With each loop n of the cursor you choose the value corresponding to key n in the table. Once you've consumed the value corresponding to key 36, you exit the cursor loop. Not only will this solve your immediate problem, it will provide all 36 values, because your existing method allows the same value to be generated again and again, even if they are not used.
Chris, First of all i want to thank you for the tolerance you have with me....
With each loop n of the cursor you choose the value corresponding to key n in the table.Once you've consumed the value corresponding to key 36, you exit the cursor loop.
i am not getting this part...Could you explain me please?
Declare an integer-typed variable @Iteration or something outside the cursor loop and assign a value of 1. Inside the loop, SELECT @value = value FROM table WHERE key = @Iteration. There's your random character. Once collected, increment the counter variable @Iteration. When you hit 37, exit the loop.
It's polishing a turd, but it will work.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 5, 2013 at 4:13 am
Sorry, but I don't.
I have asked what should happen with rows which cannot have unique on-character code set as all unique values already were used. Should such rows be left not-updated?
Yes... Leave it as non-updated or uncommit everything...This is what i want....
March 5, 2013 at 4:15 am
Declare an integer-typed variable @Iteration or something outside the cursor loop and assign a value of 1. Inside the loop, SELECT @value = value FROM table WHERE key = @Iteration. There's your random character. Once collected, increment the counter variable @Iteration. When you hit 37, exit the loop.
It's polishing a turd, but it will work.
Okay Chris, lemme try this...
March 5, 2013 at 4:35 am
prakashr.r7 (3/5/2013)
Sorry, but I don't.
I have asked what should happen with rows which cannot have unique on-character code set as all unique values already were used. Should such rows be left not-updated?
Yes... Leave it as non-updated or uncommit everything...This is what i want....
You do not need a cursor to do this!!!
Create TAble #Coded (JurisID int,CodeID int, srcCodeValue varchar(50), Description varchar(100), PnxCodeValue varchar(10))
insert into #Coded(JurisId,CodeID, srcCodeValue,Description)
Select 0,1034,'BLU','BLUE' union
Select 0,1034,'O','ORANGE' union
Select 0 , 1034,'R','RED' union
Select 0 , 1034,'GR','GREEN' union
Select 0 , 1034,'BW','BROWN'
;WITH newCodes
AS
(
SELECT v, ROW_NUMBER() OVER(ORDER BY (SELECT NEWID())) vp
FROM (VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
,('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J')
,('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T')
,('U'),('V'),('W'),('X'),('Y'),('Z') ) d (v)
WHERE NOT EXISTS(SELECT 1 FROM #Coded WHERE srcCodeValue = d.v)
)
,allRows
AS
(
SELECT *
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM #Coded
WHERE LEN(srcCodeValue) != 1 OR srcCodeValue IS NULL
)
UPDATE a
SET PnxCodeValue = n.v
FROM allRows AS a
JOIN newCodes AS n ON n.vp = a.RN
UPDATE #Coded
SET PnxCodeValue = srcCodeValue
WHERE LEN(srcCodeValue) = 1 AND PnxCodeValue IS NULL
SELECT * FROM #Coded
March 5, 2013 at 4:45 am
and a variation with single update...
;WITH newCodes
AS
(
SELECT v, ROW_NUMBER() OVER(ORDER BY (SELECT NEWID())) vp
FROM (VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
,('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J')
,('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T')
,('U'),('V'),('W'),('X'),('Y'),('Z') ) d (v)
WHERE NOT EXISTS(SELECT 1 FROM #Coded WHERE srcCodeValue = d.v)
)
,allRows
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY (CASE WHEN LEN(srcCodeValue) =1 THEN 1 ELSE 0 END)
ORDER BY (SELECT NULL)) RN
FROM #Coded
)
UPDATE a
SET PnxCodeValue = CASE WHEN LEN(srcCodeValue)=1 THEN srcCodeValue
ELSE ISNULL(n.v,PnxCodeValue)
END
FROM allRows AS a
LEFT JOIN newCodes AS n ON n.vp = a.RN
March 5, 2013 at 4:54 am
Yes, Eugene, I did a mistake by using cursor....This one is very simple.. but how can i modify this set based operation to generate 2 character, 3, 4 and so on...?
March 5, 2013 at 5:02 am
Eugene, I forgot to tell this functionality.....
For example, if we have 150 rows to be updated with two character values, first we have to update PnxCodeValue feild with 99,98,97,...10 so we would cover 90 rows and then reamaining 60 rows , we have to use random alpha numeric values ....This is my exact requirement...
March 5, 2013 at 6:25 am
prakashr.r7 (3/5/2013)
Yes, Eugene, I did a mistake by using cursor....This one is very simple.. but how can i modify this set based operation to generate 2 character, 3, 4 and so on...?
By adding the possible values into newCodes CTE. You can create a permanent table using the function I've posted before which convert decimal into 36-base.
March 5, 2013 at 6:28 am
prakashr.r7 (3/5/2013)
Eugene, I forgot to tell this functionality.....For example, if we have 150 rows to be updated with two character values, first we have to update PnxCodeValue feild with 99,98,97,...10 so we would cover 90 rows and then reamaining 60 rows , we have to use random alpha numeric values ....This is my exact requirement...
Random? Ok, could you please explain why randomness is important.
What are these PnxCodeValue for? Are you coding some casino game?
Actually, 09, 08, 07 ... and 00 are also two-characters codes!
March 5, 2013 at 7:25 am
Eugene Elutin (3/5/2013)
prakashr.r7 (3/5/2013)
Eugene, I forgot to tell this functionality.....For example, if we have 150 rows to be updated with two character values, first we have to update PnxCodeValue feild with 99,98,97,...10 so we would cover 90 rows and then reamaining 60 rows , we have to use random alpha numeric values ....This is my exact requirement...
Random? Ok, could you please explain why randomness is important.
What are these PnxCodeValue for? Are you coding some casino game?
Actually, 09, 08, 07 ... and 00 are also two-characters codes!
Sorry about that word RANDOM....i will never use it...I need all is unique character values.... sorry, yeah we even can use 09,08,07,06.....and 00. Eugene, It's is how the functionality works ...very hard to explain....
thing is , need to update the PnxcodeValue as same as SrcCodeValue if length is equal to the input length....else update with unique values..
let me show you some sample output...
WITH T AS (Select 0 AS ID ,1034 CodeID,'BLU' CodeValue,'BLUE' as Description , 'B' as PnxcodeValue
union
Select 0,1034,'O','ORANGE', 'O'
union
Select 0 , 1034,'R','RED' ,'R'
union
Select 0 , 1034,'GR','GREEN', '8'
union
Select 0 , 1034,'BW','BROWN' ,'7'
union
Select 0 , 1034,'BK','BLACK' ,'6'
union
Select 0 , 1034,'F','FAIR' ,'F'
union
Select 0 , 1034,'P','PINK' ,'P'
union
Select 0 , 1034,'RS','ROSE','5'
union
Select 0 , 1034,'BB','BALCK-BROWN' ,'4'
union
Select 0 , 1034,'WC','WHEAT COLOR' ,'3'
union
Select 0 , 1034,'PL','PURPLE' ,'2'
union
Select 0 , 1034,'MED','MEDIUM' ,'1'
union
Select 0 , 1034,'HVY','HEAVY' ,'0'
union
Select 0 , 1034,'LT','LIGHT' ,'A'
union
Select 0 , 1034,'9','UNKONWN' ,'9'
union
Select 0 , 1034,'BD','BALD' ,'C'
union
Select 0 , 1034,'BRD','BEARD' ,'D'
union
Select 0 , 1034,'SK','SKINNY','E'
union
Select 0 , 1034,'FT','FAT' ,'Z'
union
Select 0 , 1034,'BW17','BROWN' ,'G'
union
Select 0 , 1034,'MARR','Married','H'
union
Select 0 , 1034,'DIVS','Divorced' ,'I'
union
Select 0 , 1034,'SEPR','Separated' ,'J'
union
Select 0 , 1034,'WIDO','Widowed' ,'K'
union
Select 0 , 1034,'COML','Common law','L'
union
Select 0 , 1034,'SING','Single' ,'M'
union
Select 0 , 1034,'SIGO','Single' ,'N'
union
Select 0 , 1034,'UNKN','UNKN' ,'Q'
union
Select 0 , 1034,'ATHL','ATHL' ,'S'
union
Select 0 , 1034,'MUSC','MUSC' ,'T'
union
Select 0 , 1034,'PETT','PETT' ,'U'
union
Select 0 , 1034,'THI','Thin/Slender' ,'V'
union
Select 0 , 1034,'OBS','Obese' ,'W'
union
Select 0 , 1034,'AVG','Average/Medium' ,'X'
union
Select 0 , 1034,'ATH','Athletic Build' ,'Y'
) SELECT * FROM T ORDER BY PnxcodeValue DESC
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply