how to write a function to retrieve random character from A TO Z

Database
Enthusiast

how to write a function to retrieve random character from A TO Z

Hi

I 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
14 REPLIES
Enthusiast

Re: how to write a function to retrieve random character from A TO Z

There are efficient ways than writing like this(i guess)... But this is one of the way to achive what you want

sel
case when a1=1 then 'A'
when a1=2 then 'B'
when a1=3 then 'C'
end,
random(1,3) as a1
from db_name.a

Enthusiast

Re: how to write a function to retrieve random character from A TO Z



Hi

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

Thanks
Teradata Employee

Re: how to write a function to retrieve random character from A TO Z

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
Enthusiast

Re: how to write a function to retrieve random character from A TO Z



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
Teradata Employee

Re: how to write a function to retrieve random character from A TO Z

Exactly how are you executing the query?

Regards,

Adeel
Enthusiast

Re: how to write a function to retrieve random character from A TO Z



This is the query exactly what i have:
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

Thanks
Teradata Employee

Re: how to write a function to retrieve random character from A TO Z

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

Regards,

Adeel
Enthusiast

Re: how to write a function to retrieve random character from A TO Z



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
Teradata Employee

Re: how to write a function to retrieve random character from A TO Z

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