All Forums Database
Dileep Banala 22 posts Joined 07/08
21 Aug 2008
how to write a function to retrieve random character from A TO Z

HiI am trying to write a function in Teradata SQL Assistant to fetch a character at a time randomly between A to Z.Can anyone help me in this regard?Thanks

Grao 48 posts Joined 08/06
21 Aug 2008

There are efficient ways than writing like this(i guess)... But this is one of the way to achive what you wantsel case when a1=1 then 'A'when a1=2 then 'B'when a1=3 then 'C'end,random(1,3) as a1from db_name.a

-Grao

Dileep Banala 22 posts Joined 07/08
22 Aug 2008

HiAm getting some question marks in place of the characters after executing the above query.And what is that table a?Thanks

Adeel Chaudhry 773 posts Joined 04/08
22 Aug 2008

Hello,On a very basic level, you can use following:SELECT RandChar FROM(SELECT RANDOM(1, 26) AS Rand, CASE WHEN Rand = 1 THEN 'A' WHEN Rand = 2 THEN 'B' WHEN Rand = 3 THEN 'C' WHEN Rand = 4 THEN 'D' WHEN Rand = 5 THEN 'E' WHEN Rand = 6 THEN 'F' WHEN Rand = 7 THEN 'G' WHEN Rand = 8 THEN 'H' WHEN Rand = 9 THEN 'I' WHEN Rand = 10 THEN 'J' WHEN Rand = 11 THEN 'K' WHEN Rand = 12 THEN 'L' WHEN Rand = 13 THEN 'M' WHEN Rand = 14 THEN 'N' WHEN Rand = 15 THEN 'O' WHEN Rand = 16 THEN 'P' WHEN Rand = 17 THEN 'Q' WHEN Rand = 18 THEN 'R' WHEN Rand = 19 THEN 'S' WHEN Rand = 20 THEN 'T' WHEN Rand = 21 THEN 'U' WHEN Rand = 22 THEN 'V' WHEN Rand = 23 THEN 'W' WHEN Rand = 24 THEN 'X' WHEN Rand = 25 THEN 'Y' WHEN Rand = 26 THEN 'Z' END AS RandChar ) QueryAlias1 HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Dileep Banala 22 posts Joined 07/08
22 Aug 2008

Even then am getting a question mark in my result ....i wonder why is it !!!And i hope random() function will not generate the float numbers as in SQL !!!!!!!Thanks

Adeel Chaudhry 773 posts Joined 04/08
22 Aug 2008

Exactly how are you executing the query?Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Dileep Banala 22 posts Joined 07/08
22 Aug 2008

This is the query exactly what i have:SELECT RandChar FROM(SELECTRANDOM(1, 26) AS Rand,CASE WHEN Rand = 1 THEN 'A'WHEN Rand = 2 THEN 'B'WHEN Rand = 3 THEN 'C'WHEN Rand = 4 THEN 'D'WHEN Rand = 5 THEN 'E'WHEN Rand = 6 THEN 'F'WHEN Rand = 7 THEN 'G'WHEN Rand = 8 THEN 'H'WHEN Rand = 9 THEN 'I'WHEN Rand = 10 THEN 'J'WHEN Rand = 11 THEN 'K'WHEN Rand = 12 THEN 'L'WHEN Rand = 13 THEN 'M'WHEN Rand = 14 THEN 'N'WHEN Rand = 15 THEN 'O'WHEN Rand = 16 THEN 'P'WHEN Rand = 17 THEN 'Q'WHEN Rand = 18 THEN 'R'WHEN Rand = 19 THEN 'S'WHEN Rand = 20 THEN 'T'WHEN Rand = 21 THEN 'U'WHEN Rand = 22 THEN 'V'WHEN Rand = 23 THEN 'W'WHEN Rand = 24 THEN 'X'WHEN Rand = 25 THEN 'Y'WHEN Rand = 26 THEN 'Z'END AS RandChar) QueryAlias1Thanks

Adeel Chaudhry 773 posts Joined 04/08
22 Aug 2008

Can you try running the same in BTEQ and SQL Assistant and check the outputs?Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Dileep Banala 22 posts Joined 07/08
22 Aug 2008

Even in BTEQ it is resulting question marks...and same with SQL Assistant also ....why is it happening like that?is there any other way of doing this?Thanks

Adeel Chaudhry 773 posts Joined 04/08
22 Aug 2008

It is very weird! It is a simple SQL, it should work on both. I wonder if this issue can be because of Teradata's version. I tried this on V2R6 and TD-12. It works fine on both (in BTEQ and SQL Assistant).Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Dileep Banala 22 posts Joined 07/08
22 Aug 2008

oh ... then you are right ....am working on V2R5.OK, thanks for ur reference.Thanks

Jim Chapman 449 posts Joined 09/04
22 Aug 2008

Try this:SELECTCASE WHEN Rand = 1 THEN 'A'WHEN Rand = 2 THEN 'B'WHEN Rand = 3 THEN 'C'WHEN Rand = 4 THEN 'D'WHEN Rand = 5 THEN 'E'WHEN Rand = 6 THEN 'F'WHEN Rand = 7 THEN 'G'WHEN Rand = 8 THEN 'H'WHEN Rand = 9 THEN 'I'WHEN Rand = 10 THEN 'J'WHEN Rand = 11 THEN 'K'WHEN Rand = 12 THEN 'L'WHEN Rand = 13 THEN 'M'WHEN Rand = 14 THEN 'N'WHEN Rand = 15 THEN 'O'WHEN Rand = 16 THEN 'P'WHEN Rand = 17 THEN 'Q'WHEN Rand = 18 THEN 'R'WHEN Rand = 19 THEN 'S'WHEN Rand = 20 THEN 'T'WHEN Rand = 21 THEN 'U'WHEN Rand = 22 THEN 'V'WHEN Rand = 23 THEN 'W'WHEN Rand = 24 THEN 'X'WHEN Rand = 25 THEN 'Y'WHEN Rand = 26 THEN 'Z'END AS RandCharFROM(SELECTRANDOM(1, 26) AS Rand) QueryAlias1 ;

ywjcjj 36 posts Joined 06/08
25 Aug 2008

I tried this query and it just worked fine to me.

eejimkos 73 posts Joined 01/12
05 Apr 2012

Hi,

one question.How can we select random values -chars- from a column , in order to insert them into another table?

Thanks.

ulrich 816 posts Joined 09/09
05 Apr 2012
select top 10

substr('LBXIDZDEEDNRKRUEBHMGZTHDIWMUWGNPLUJOUELEENNISWQEHREUWYBIDGDNQLRYYYQTHNOTPUDXXOCSDWJHGAWFKKHEJLFPOGBALFSXOCHOJPCTNBINWRDQRRINKGRSPQESSQXRFWUTCZGLESYCJOCGZGYAQDEOVGIIPRYBJQDMNOTZDPDRAIYWCZTDXWQMFGKHKRIUSXWFBLYLXVISCGLEOZLYDHLBTVRLRIHDZQJISTSJPPLFLERGFUQKYFHETTIUOBVELGVKLLMTGLFTCVSAFXTJLZCMVGOSHYXYNLXKUWEKKABBAXFJBBWHHLJPWJWNCCVOGIOEUUQGBWFZNYZDJCDYTTWJRASCXONMMOKDMKGBIPEUQCDPWAUISVKEJZYXMWDHSYNBVJFASYGAUAFDSHSXBMPRAMPEENAZNBAAZVIHIDBGJVKYLGPFFNOFPQGKDRXJOBSUASXYLXGQVGZRRUVUAUTWFKAZCBMXANXJCUVKKKJVWBPQIVNERZQGBGXBWJWNORISKPFUPAUMDWNRCFARWVANHWFHXKNHUOWJSSARXNMWPTWCMQVZKPBWVVVJRXDPCRBFOHPBTXDIVPXHOXNFRHXOJMMCPGSKSVKWEWFTDQDHSFZPWBTNNOWEZELAHIUXXAXSSLFSUYFWFDFWGGABQAXIHMQQINTVKXKRZSXXONATHNAARGQCMVTQTEGFJJCRRTAEQATIXBUTLSHCHKRLVRHVQNNQVQWTMMCTMDLIOKYRDGJNIXILWRNBCJAGWSBWVAHBAAHOBGJGDEHPIHPJJYFUQGIJJXTAQBEGNXQMFHASADJTKBUMUWDVKVUXKTHOFNYHUBGOBKEIYSYTSLRNJBFXSNXGNRVNSAYSFJDHHFEBNGROJATVKTPZNDWLQDHNYXPPSUVIYWEXVRPYFLTOIPGTOUCXFPSSEKEOYPPPJHLGQYFHXOARLCGCPXNWVYQPRHTYKAMWNAVNJFYMODCKYMYTOYYUEOJZDRHVFKTBVDTEXYAZPHOIIIZNIDEECDFUKJOCHGGGRECYOXYJGWEAMSCWATZXOTTMXKGPVFUBYZYTXAISRXUEVRGPLUVBCZUGDKHCHFYGVAYRUYIRQUHMJECOQAUKDDBYELYXHCOVHAYRZQIRLPQQYVCSUAYNZPUGZYTGVIIOPRLJBYNIPMFPQZUFDMPDZLBHQXUNHYAJYCWAXMMIVDAGLCBOXBHCIDMXCEXNPAGUKKSSLVHWZLGCCSJXRNYNPRCSIMEXBQIOPHFSVMREQRBZVPFQSQBXNGFMUAQSNNMPGPVQYLVJCHIMCYLWAYJGSYTGJLDCAQQQZYDCMWUSHPYHUOYBGGBCAPJZNXEUMTIBSQOQDAYGAUZIGVCYZFHMCYRAAZVBBPLJLRXNNIXZMCCYXHPQEPJITKFZSZYLNTGDCBRPCSBRPZQTOPSXJDVETKOVYKDFOLLBYMCJBYEFGERQAXXVOAPFJVKHGPUTVJVVHQRRRMKHWTDZGJNLZNCYQAFERSIZMHCHAEICPXKSADZVOUQACKAFWIRUXVWAOPMYPPHWRQHECMRCSXHWLVIJXRYNSXEEBHEYOUTYSMOXRZOIVPGRMBORZLTKIZKOQECOYVKQJKCWNQADMQZAWBQYMKELLJMEHGUPXLVQXROSVAZXBATZJXDPXCDOFRUHAINKQFEEEPMQZJOOOHKWJOCSNQLIVFTRJUXINRAIBKVZMGESWKTDOLTQPOVPMYEXTEMGTSKHXPLHXTHWVKMAUUFOWBKFOFHFGWJFKQAGTRBHKVEJAVDSNKZUFGGYXFIJHSDJNUGOAAWFIRECFWIMGTEOFDRJPNZMWVAAMDVWCEUHZETYPWXDHISSSKRTFKFZSVXCKZGSMOFUEBCGDPEWLHYKZCJBZYVQSYASKRWKFBRBIFIZYZBJPZGEBYIVTAOWLDBRSYZADZSTYJFOJPWAXPTVZGZTULMULYWUXVXMSTJVLCCYQEKUASELHUEACWVVHRYH',

Random(1,1900), Random(10,100))

from sys_calendar.calendar

if you change the last random you can controll the length of the string.

You might need a longer random string in case you need a high number of different output values.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.