giovedì 8 agosto 2013

T-SQL Split String

The folowing code shows the way to do a table function to make string split in T-SQL.



create function fx_split(@stringToSplit VARCHAR(MAX),  @splitString varchar(5) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(@splitString, @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(@splitString,  @stringToSplit) 
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList
  SELECT @name

  SELECT @stringToSplit = ltrim(rtrim(SUBSTRING(@stringToSplit, @pos+len(@splitString), LEN(@stringToSplit)-@pos)))
 END

 INSERT INTO @returnList
 SELECT @stringToSplit


 RETURN
END


Here The Use of the Function
 

select * from fx_split('1,2,3,4,5', ',')



And This Is The RESULT.

1
2
3
4
5



My Two Cents ... 

martedì 19 febbraio 2013

T-SQL, Executing DOS Command

The seguente T-SQL Script shows how execute DOS Command:


declare  @temptb as table (id int identity, valore varchar(255))
insert into @temptb exec xp_cmdshell 'ipconfig'
select * from @temptb

My Two Cents ...

Getting SQL Server IP Address


If you need to know the IP of your SQL Server, the seguent scripts is what you need:


declare @cmdresults as table(ip varchar(255))
insert into @cmdresults exec xp_cmdshell'ipconfig | find "IPv4"'
select ltrim(rtrim(substring(ip,charindex(':',ip)+1,len(ip)))) from @cmdresults where ip is not null  

or


declare @cmdresults as table(ip varchar(255))
insert into @cmdresults exec xp_cmdshell'ipconfig | find "IP Address"'
select ltrim(rtrim(substring(ip,charindex(':',ip)+1,len(ip)))) from @cmdresults where ip is not null  

My Two Cents ... 

T-SQL, Row Value Concatenation Unsing "FOR XML PATH"

Sometime, could be usefull to concatenate row values.
For example you have a select that returns this result:


A
B
C
D


but you need:


A, B, C, D

the seguente script makes this: a Row Value Concatenation.


SELECT STUFF((
      SELECT
            ', ' + ltrim(rtrim(Your_Field))
      FROM
            Your_Table
      FOR XML PATH('')
),1,2,'')



My Two Cents ...

Time From Datetime



The following script shows how you can get time from datetime:

select CONVERT(char(8), getdate(), 108) as Time



My Two Cents...


martedì 20 novembre 2012

Datetime without hours, minutes and seconds

If you need to get date from datetime:

select dateadd(dd,0,datediff(dd,0,getdate()))

If you want only date without hours, minutes and seconds:
SELECT convert(varchar, getdate(), 103) 

103 format is dd/mm/yyyy


My Two Cents ...


T-SQL, Calculate Age From Date of Birth


Sometime could be useful calculate age from date of birth.
The following t-sql code shows how could be done it.




datediff(yy, date_of_birth, GETDATE()) -
       (case when (datepart(m date_of_birth) > datepart(m, GETDATE()))OR
                   (datepart(m date_of_birth) = datepart(m, GETDATE()) AND
                    datepart(d date_of_birth) > datepart(d, GETDATE()))
                        then 1
                        else 0
        end) AS AGE



My Two Cents...