>>Script Language and Platform: SQL Server
This udf_SuggestUserName function generates usernames based on date of birth, zipcode, username, firstname and lastname. It also generates zodiac username based on date of birth.

Useful in suggesting a list of usernames to the person who is trying to register and if the username already exist in the database.

Usage:

select * from dbo.udf_SuggestUserName

   ('Kurara','Chibana','03-12-1982','07051','RainbowRiver')



--result

Chibana_07051

Chibana_Kurara

Chibana_Pisces_Chibana

Chibana19820312

Chibana82

ChibanaKurara

contact_Chibana

contact_Kurara

contact_RainbowRiver

contactChibana

contactKurara

contactRainbowRiver

KChibana

KChibana1982

KChibana82

Kurara_07051

Kurara_Chibana

Kurara_numberone

Kurara_Pisces_Kurara

Kurara19820312

Kurara82

KuraraChibana

Message_RainbowRiver

Pisces_Chibana

Pisces_Kurara

Pisces07051

Pisces19820312

RainbowRiver_07051

RainbowRiver_1

RainbowRiver_1982

RainbowRiver_2

RainbowRiver_3

RainbowRiver_4

RainbowRiver_5

RainbowRiver_6

RainbowRiver_7

RainbowRiver_8

RainbowRiver_9

RainbowRiver1

RainbowRiver1982

RainbowRiver19820312

RainbowRiver2

RainbowRiver3

RainbowRiver4

RainbowRiver5

RainbowRiver6

RainbowRiver7

RainbowRiver8

RainbowRiver82

RainbowRiver9

reach_Chibana

reach_Kurara

reach_RainbowRiver

reachChibana

reachKurara

reachRainbowRiver

Author: MAK

if exists (select * from dbo.sysobjects 

  where id = object_id(N'[dbo].[udf_SuggestUserName]') 

  and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[udf_SuggestUserName]

GO



Create function dbo.udf_SuggestUserName 

(@firstname varchar(100),

@lastname varchar(100),

@dob datetime,

@zipcode varchar(12),

@username varchar(100))

returns @usernameTable TABLE (username varchar(100))

as

begin

declare @year2 int

declare @zodiac varchar(15)

declare @dob2 datetime

set @dob2= convert

  (datetime,convert(varchar(2),month(@dob))+' -'+convert(varchar(2),day(@dob))+'-2000' )

select @zodiac = case 

when @dob2 between '3/21/2000' and '4/20/2000' then 'Aries'

when @dob2 between '4/21/2000' and '5/21/2000' then 'Taurus'

when @dob2 between '5/22/2000' and '6/22/2000' then 'Gemini'

when @dob2 between '6/23/2000' and '7/23/2000' then 'Cancer' 

when @dob2 between '7/24/2000' and '8/23/2000' then 'Leo'

when @dob2 between '8/24/2000' and '9/23/2000' then 'Virgo'

when @dob2 between '9/24/2000' and '10/23/2000' then 'Libra'

when @dob2 between '10/24/2000'and '11/22/2000'then 'Scorpio'

when @dob2 between '11/23/2000'and '12/22/2000' then 'Sagittarius'

when @dob2 between '12/23/2000'and '1/19/2000' then 'Capricorn'

when @dob2 between '1/20/2000' and '2/19/2000' then 'Aquarius'

when @dob2 between '2/20/2000' and '3/20/2000'then 'Pisces' end





insert into @usernameTable

select @firstname+'_'+@lastname union

select @lastname+'_'+@firstname union

select @firstname+@lastname union

select @lastname+@firstname union

select left(@firstname,1)+@lastname union

select @username+'_'+@zipcode union

select 'reach'+'_'+@username union

select 'contact'+'_'+@username union

select 'reach'+'_'+@firstname union

select 'contact'+'_'+@lastname union

select 'reach'+'_'+@lastname union

select 'contact'+'_'+@firstname union

select 'reach'+@username union

select 'contact'+@username union

select 'reach'+@firstname union

select 'contact'+@lastname union

select 'reach'+@lastname union

select 'contact'+@firstname union

select left(@firstname,1)+@lastname+convert(varchar(4),year(@dob)) union

select @username+'_'+convert(varchar(4),year(@dob)) union

select @username+convert(varchar(4),year(@dob)) union

select @firstname+'_'+@zipcode union

select @lastname+'_'+@zipcode union

select 'Message_'+@username union

select @username+convert(varchar(8),@dob,112) union

select @Firstname+convert(varchar(8),@dob,112) union

select @Lastname+convert(varchar(8),@dob,112) union

select @Firstname+'_numberone' union

select @username+'_1' union

select @username+'_2' union

select @username+'_3' union

select @username+'_4' union

select @username+'_5' union

select @username+'_6' union

select @username+'_7' union

select @username+'_8' union

select @username+'_9' union

select @username+'1' union

select @username+'2' union

select @username+'3' union

select @username+'4' union

select @username+'5' union

select @username+'6' union

select @username+'7' union

select @username+'8' union

select @username+'9' union

select @zodiac+convert(varchar(8),@dob,112) union

select @zodiac+@zipcode union

select @zodiac+'_'+@firstname union

select @zodiac+'_'+@lastname union

select @firstname+'_'+@zodiac+'_'+@firstname union

select @lastname+'_'+@zodiac+'_'+@lastname union

select @firstname+right(convert(varchar(4),year(@dob)),2) union

select @lastname+right(convert(varchar(4),year(@dob)),2) union

select @username+right(convert(varchar(4),year(@dob)),2) union

select left(@firstname,1)+@lastname+right(convert(varchar(4),year(@dob)),2)



RETURN

end



go

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home