sqlsql-servert-sqlsql-functionstring-function

How to select from text list randomly?

I'm trying to build SQL function that I can use as a default value for a column. The function is about selecting an avatar image path randomly if the user didn't assign an image.

I have tried to but a completely wrong example to just approach the image not the solution

what I need to do is something like this

select top 1 from "avatar1,png, avatar2,png, avatar3.png, avatar4.png, avatar5.png"  order by rand();

and I will convert it to a function like this 

CREATE FUNCTION dbo.ReturnAvatar() 
RETURNS nvarchar(100)
AS   
BEGIN  
    DECLARE @ret nvarchar(100);  
        SET @ret = (select top 1 from "avatar1,png, avatar2,png, avatar3.png, avatar4.png, avatar5.png" as tbl order by rand());  
    RETURN @ret;  
END;

this is just to explain the idea that I'm not able to apply. I don't know if SQL server has something like this or not.

Solution

  • You do not need to create a table at all. Simply put the number inside your string and choose the number randomly:

    select 'avatar'+str(round(rand()*5+1,0))+'.png'

    would be fine.

    Put that into your function and you are all set.

    rand() produces 0..1(excl.) so you can simply multiply it by 5 and add 1 to get your range of 1...5

    Demo: http://sqlfiddle.com/#!18/9eecb/82866

    Documentation:

    So essentially you could boil it down to:

     select 'avatar'+ltrim(str(rand()*5+1,20,0))+'.png'

    with