CODEWITHSUNDEEP
sqlsql-servert-sqlsql-functionstring-function
user12980259
user12980259

Reputation:

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.

Upvotes: 0

Views: 123

Answers (3)

Patrick Artner
Patrick Artner

Reputation: 51683

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

Upvotes: 0

lptr
lptr

Reputation: 6798

create function dbo.ReturnAvatar(@uid uniqueidentifier, @avatars int = 10)
returns varchar(100)
as 
begin 
    return ('avatar' + cast(abs(checksum(@uid)) % isnull(abs(@avatars), 10)+1 as varchar(100)) + '.png')
end
go

create table myusers
(
username varchar(50),
theavatar varchar(100) default( dbo.ReturnAvatar(newid(), default))
);

insert into myusers(username)
select top (10000) 'user' + cast(row_number() over(order by(select null)) as varchar(50))
from master.dbo.spt_values as a
cross join master.dbo.spt_values as b;
go

select theavatar, count(*)
from myusers
group by theavatar;
go

drop table myusers;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

Here is one way:

CREATE VIEW getNewID AS SELECT newid() as new_id

CREATE FUNCTION dbo.ReturnAvatar() 
RETURNS nvarchar(100)
AS   
BEGIN  
    DECLARE @ret nvarchar(100);  
        SET @ret = (SELECT TOP 1 value
                    FROM
                        STRING_SPLIT('avatar1.png,avatar2.png,avatar3.png,avatar4.png,avatar5.png', ',')
                    ORDER BY (SELECT new_id FROM getNewID));
    RETURN @ret;  
END;

Note that your current CSV string of filenames does not seem proper, because comma does not indicate the start of the extension in either Windows or Linux. So, I have assumed dot everywhere. In addition, if you want to use STRING_SPLIT, you may only split on a single character. Therefore, I assume that comma will be the delimiter here.

Upvotes: 1

Related Questions