 Database Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums Database 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 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 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 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. 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 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. 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 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. 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 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. 22 Aug 2008 oh ... then you are right ....am working on V2R5.OK, thanks for ur reference.Thanks 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 ; 25 Aug 2008 I tried this query and it just worked fine to me. 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. 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.