Get the object name from the fully qualified name


Howdy Everyone.

I have seen an interesting built in function of SQL Server today, It is PARSENAME.

The PARSENAME function will return the object/schema/database/server name from the given string.

For more information you can check MSDN article below

http://msdn.microsoft.com/en-us/library/ms188006.aspx

Advertisements

How to find the last day of the month


Howdy Everyone.

Most of the times I see, people getting up with new ideas to get the last day of the month…

But the simplest method we can use in SQL Server is by adding 1 to month and subtracting the number of days in the date, confusing….

Well let me explain with an example

Let us take the date 20-AUG-2011. Now if we add 1 to the month by using DATEADD function.. the result will be 20-SEP-2011. Now the day part of the resultant date is 20 and if we subtract the 20 from the resultant by using dateadd the result will be 30-AUG-2011.

The code is given below

DECLARE @input_date datetime
set @input_date = getdate() -- for testing purposes
select dateadd(dd,datepart(dd,@input_date)*-1,dateadd(mm,1,@input_date))

Find MAX number without using MAX,Where Clause, Order by


How can some one retrieve the max of the number from a table using TSQL where you are not allowed to use the MAX function or the order by clause or the Where clause.

I have found out a solution below, although the algorithm can be changed by using different sort techniques. I found this is simple to understand so the users might build upon this

Method 1

create table salaries (empid int identity(1,1), salary int)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go
insert into salaries (salary) values (3000)
go
insert into salaries (salary) values (2400)
go
insert into salaries (salary) values (100)
go
insert into salaries (salary) values (200)
go
insert into salaries (salary) values (1050)
go
insert into salaries (salary) values (8000)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go

select top 1 s1.salary from salaries s1
      inner join salaries s2 on s1.salary> s2.salary
      and s1.empid not in (select s2.empid from salaries s1
      inner join salaries s2 on s1.salary> s2.salary)

Method 2

create table #table
(
	num int
)

insert into #table values (1)
insert into #table values (2)
insert into #table values (3)
insert into #table values (4)
insert into #table values (10)
insert into #table values (6)
insert into #table values (7)
insert into #table values (8)
insert into #table values (9)

declare @max_number int
declare @present_num int
declare @previous_num int

SET @present_num = 0
SET @previous_num = 0
SET @max_number = 0
update t
SET	@present_num = t.num
    ,@max_number = case when @present_num > @max_number Then @present_num  else @max_number end
    ,@previous_num = t.num
FROM #table t

select @present_num ,@max_number,@previous_num

UDF to Split a delimited string and return it as a table


Howdy Every One

Each and everyday we might find out that we need a split a string and return it as a table with rows in SQL. There are many programming languages in which there are built in functions which will do this for you (javascript split). Now if you want the same functionality in SQL, the only choice left is to write your own UDF (as of SQL Server 2008 R2 version).

Below is code of the UDF that might help you out

CREATE FUNCTION dbo.Split
(
 @RowData nvarchar(2000),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 DECLARE @index INT
 SET @index = Charindex(@SplitOn,@RowData)
 While (@index>0)
 Begin
 Insert Into @RtnValue (data)
  Select
 Data = ltrim(rtrim(Substring(@RowData,1,@index-1)))

 Set @RowData = Substring(@RowData,@index+1,len(@RowData))
 Set @Cnt = @Cnt + 1
 SET @index = Charindex(@SplitOn,@RowData)
 End

 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))

 Return
END