Different Like Operators in Stored Procedures in SQL Server

Here I am explaining four stored procedures to show use of different type of like in SQL Server.

--we have selected all rows for which UserName which contains @name .

create proc like1

@name varchar(200)

as

begin

select * from tbl_User where UserName like''+'%'+@name+'%'+''

End

--we have selected all rows for which UserName starts with any character between 'a' and 'r'.

create proc like2

@name varchar(200)

as

begin

select * from tbl_User where UserName like''+'[a-r]%'+@name+'%'+''

End

--we want to select all those rows which have 'a','e','i','o' or 'u' as data in UserName column

create proc like3

@name varchar(200)

as

begin

select * from tbl_User where UserName like''+'[aeiou]%'+@name+'%'+''

End

-- we are excluding all rows where value of UserName starts with any of 'a','e','i','o' or 'u':

create proc like4

@name varchar(200)

as

begin

select * from tbl_User where UserName like''+'[^aeiou]%'+@name+'%'+''

End