SPLIT FUNCTION Comma Seperator with RowId

CREATE FUNCTION [dbo].[fn_Split](@locationlist varchar(5000))                   

RETURNS @ParsedList Table (Rowid int,location Varchar(100))                   

AS                   

BEGIN   

 Set @locationlist = @locationlist + ','   

 DECLARE @location varchar(100), @Pos int                   

 declare @cnt int               

 SET @location =replace((LTRIM(RTRIM(@locationlist))),'''','')           

 SET @Pos = CHARINDEX(',', @locationlist, 1)                     

 Set @cnt = 1                     

 IF REPLACE(@locationlist, ',', '') <> ''                     

 BEGIN                     

  WHILE @Pos > 0                     

  BEGIN                     

   SET @location = LTRIM(RTRIM(LEFT(@locationlist, @Pos - 1)))                     

   IF @location  <> ''                     

   BEGIN                     

    INSERT INTO @ParsedList (Rowid,location)                      

    VALUES (@cnt,@location) --Use Appropriate conversion                

 Set @cnt = @cnt + 1                    

   END                     

   SET @locationlist = RIGHT(@locationlist, LEN(@locationlist) - @Pos)                     

   SET @Pos = CHARINDEX(',', @locationlist, 1)                     

                     

  END                     

 END                      

 RETURN                     

END

CharIndex

select charindex('an','madhsan')

--output

6

select charindex('an','madhsan',1)

--output(as it searches from 1st character)

6

select charindex('an','madhsan',7)

--output (as it searches from 7nth characters)

0

select patindex('%an%','madhsancoolan')

--output (Patindex is charindex plus wildcardsearch)

6

Intro To Database Standford Lecture

http://openclassroom.stanford.edu/MainFolder/CoursePage.php?course=IntroToDatabases

Read Values of xml through tsql or sql querry

DECLARE @doc xml  ='<?xml version="1.0"?>

<dicom>

<attr len="2" vr="OB" tag="00020001">00\01</attr>

<attr len="22" vr="UI" tag="00020002">1.2.840.10008.5.1.4.33</attr>

</dicom>'                                                                                                       

declare @idoc int

select @doc

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT

tag,len,comment

FROM

OPENXML(@idoc, '/dicom/attr')

WITH

(tag varchar(30)

,len varchar(30)

,comment ntext 'text()'

)

Display Dates Between Two Dates in Sql Server

DECLARE @StartDate DATETIME

SET @StartDate = '02/26/1988'

DECLARE @EndDate DATETIME

SET @EndDate = '03/01/1988'

DECLARE @TableOfDates TABLE(DateValue DATETIME)

DECLARE @CurrentDate DATETIME

SET @CurrentDate = @startDate

WHILE @CurrentDate <= @endDate

BEGIN

    INSERT INTO @TableOfDates(DateValue) VALUES (@CurrentDate)

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)

END

SELECT * FROM @TableOfDates

Add a column to a table if it does not exits

Add a column to a table if it does not exits

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS         

WHERE TABLE_NAME = 'TblSourceMaster' AND COLUMN_NAME = 'CorpSourceId')         

BEGIN         

   ALTER TABLE TblSourceMaster ADD CorpSourceId bigint NULL         

END        

Free Google Online Chat Support For Website and Blogs

Free Google Online Chat Support For Website and Blogs
STEP1
Go to the following site and create a badge as shown in the Picture
http://www.google.com/talk/service/badge/New

STEP2
Add the HTML code into the website or Blog To get an Online Chat

STEP 3
Get the chat screen

Executing Commands listed in a notepad

create the below command as  a .bat file

FOR /F  %%a  in (E:\test\command.txt) do %%a

Give the command file location from which the commands to be executed

Thats it ur done 

Passing Parameters To Batch File with Spaces and to Poweshell

This Tutorial covers passing parameters to a batch file and passing parameter to powershell script.

# The Below is the screen shot of the cmd screen passing parameter to batch file



The Below it the screen shot of the bat file ps.bat

The Below is the Contents Of Powershell Script

The Below is the result which displays the dir for the folder given

Poweshell Bacics in Debugging

A Free Debugger called PowerGUI is available for Poweshell debugging.

After downloading open the powershell as administrator and type the following command to start debugging with the PowerGuI
Set-ExecutionPolicy RemoteSigned
Boom-- start using the debugger with intellisense

Dos Dir Command

dir /qDisplay the directory with the owner of the file.

eg.
03/18/2011  10:43 AM    <DIR>          MEDALLCORP\madhan      Charcoal.txt
03/18/2011  10:44 AM    <DIR>          MEDALLCORP\madhan      Colors.jpeg

dir /s
Display the files in the specified directory and all subdirectories





dir /s
Display the files in the specified directory and all subdirectories

dir /od
Displays files sorted by date dir /on sorts file in alphabetical order
dir /o-d
Displays file sorted by date descending order.

SQL String Functions

SQL String Functions

Sql string function is a built-in string function.
It perform an operation on a string input value and return a string or numeric value.
Below is All built-in Sql string function :ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE,CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME,STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE,UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM


Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)SELECT ASCII('a') -- Value = 97
SELECT ASCII('b') -- Value = 98
SELECT ASCII('c') -- Value = 99
SELECT ASCII('A') -- Value = 65
SELECT ASCII('B') -- Value = 66
SELECT ASCII('C') -- Value = 67
SELECT ASCII('1') -- Value = 49
SELECT ASCII('2') -- Value = 50
SELECT ASCII('3') -- Value = 51
SELECT ASCII('4') -- Value = 52
SELECT ASCII('5') -- Value = 53 


Example SQL String Function - SPACE 
-Returns spaces in your SQL query (you can specific the size of space). 
Syntax - SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS 


Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] ) 
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial') 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20) 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)


Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] ) 
SELECT QUOTENAME('Sql[]String')
-- Value = [Sql[]]String]


Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 ) 
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer) 
SELECT LEFT('TravelYourself', 6) 
-- Value = Travel
SELECT LEFT('BeautyCentury',6) 
-- Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)-- Value = urself
SELECT RIGHT('BeautyCentury',6)-- Value = Century


Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)SELECT REPLICATE('Sql', 2) 
-- Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3) 
-- Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string) 
SELECT LEN('SQLServer')
-- Value = 9


Example SQL String Function - REVERSE
-Returns reverse a string.Syntax - REVERSE( string)SELECT REVERSE('SQLServer') 
-- Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char) 
SELECT UNICODE('SqlServer') 
-- Value = 83 (it take first character)
SELECT UNICODE('S')
-- Value = 83


Example SQL String Function - LOWER
-Convert string to lowercase.Syntax - LOWER( string )SELECT LOWER('SQLServer') 
-- Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string ) 
SELECT UPPER('sqlserver') 
-- Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )SELECT LTRIM(' sqlserver')-- Value = 'sqlserver' (Remove left side space or blanks)


Example SQL String Function - RTRIM 
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )SELECT RTRIM('SqlServer ')
-- Value = 'SqlServer' (Remove right side space or blanks)