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

## 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,

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

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

Exactly how are you executing the query?

Regards,

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

## 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,

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