Documentation of SqlServerExtensions

General

File Operations

  • DbExt.Dir(@Path NVARCHAR(255), @Filter NVARCHAR(255))
    • Description: lists the content of a directory like a table with some attributes
    • Parameters:
      • @Path: path of the directory that should be listed
      • @Filter: filters the directory e.g.: *.exe
    • Returns: TABLE
    • Example:
SELECT * FROM DbExt.Dir('C:\', '*');

  • DbExt.FileCopy(@SourceFileName NVARCHAR(255), @DestFileName NVARCHAR(255), @Overwrite BIT)
    • Description: copies a file
    • Parameters:
      • @SourceFileName : source path and file
      • @DestFileName : destination path and file
      • @Overwrite : if true (1), an existing file will be overwritten
    • Returns: BIT
    • Example:
SELECT DbExt.FileCopy('D:\TestFolder\A\Test.txt', 'D:\TestFolder\B\Test.txt', 1)
  • DbExt.FileDelete(@Path NVARCHAR(255))
    • Description: deletes a file
    • Parameters:
      • @Path : path and file that should be deleted
    • Returns: BIT
    • Example:
SELECT DbExt.FileDelete('D:\TestFolder\A\Text.txt')
  • DbExt.FileExists(@Path NVARCHAR(255))
    • Description: check if file exists
    • Parameters:
      • @Path : check if path and file exists
    • Returns: BIT
    • Example:
SELECT DbExt.FileExists('D:\TestFolder\A\Test.txt')
  • DbExt.FileMove(@SourceFileName NVARCHAR(255), @DestFileName NVARCHAR(255))
    • Description: moves a file
    • Parameters:
      • @SourceFileName : source path and file
      • @DestFileName : destination path and file
    • Returns: BIT
    • Example:
SELECT DbExt.FileMove('D:\TestFolder\B\Text.txt', 'D:\TestFolder\A\Text.txt')
  • DbExt.FileRename(@SourceFileName NVARCHAR(255), @DestFileName NVARCHAR(255))
    • Description: renames a file
    • Parameters:
      • @SourceFileName : source path and file
      • @DestFileName : destination path and file
    • Returns: BIT
    • Example:
SELECT DbExt.FileRename('D:\TestFolder\B\Test.txt', 'D:\TestFolder\B\Text.txt')
  • DbExt.FileReadAllText (@Path NVARCHAR(255))
    • Description: reads a complete file into a variable
    • Parameters:
      • @Path: path to the file which should read into a variable
    • Returns: BIT
    • Example:
SELECT DbExt.FileReadAllText('C:\eula.1028.txt');
  • DbExt.FileWriteAllText (@Path NVARCHAR(255), @Contents NVARCHAR(MAX))
    • Description: writes a variable into a file
    • Parameters:
      • @Path: path to the file which should be written
      • @Contents: content that should be written to file
    • Returns: BIT
    • Example:
DECLARE @Contents NVARCHAR(MAX) = 'Line 1' + CHAR(13) + 'Line 2' + CHAR(13) + 'Line 3' 
SELECT DbExt.FileWriteAllText('D:\TestFolder\dummy.txt', @Contents)
  • DbExt.FileReadAllLines (@Path NVARCHAR(255))
    • Description: reads a complete file into a table with line numbers
    • Parameters:
      • @Path: path to the file which should read into a table
    • Returns: TABLE
    • Example:
SELECT * FROM DbExt.FileReadAllLines('C:\eula.1028.txt')
WHERE LineNumber BETWEEN 10 AND 20;

Network Operations

  • DbExt.ShareConnect(@DriveLetter NVARCHAR(1), @Path NVARCHAR(255), @Username NVARCHAR(255), @Password NVARCHAR(255))
    • Description: maps a share to a drive letter
    • Parameters:
      • @DriveLetter: letter which should be used for the share
      • @Path: full path to the file share that should be mapped to the driver letter
      • @Username: user name of the file share
      • @Password: password of the file share
    • Returns: BIT
    • Example:
SELECT DbExt.ShareConnect('J', '\\computer\sharename', 'username', 'secret')
  • DbExt.ShareDisconnect(@DriveLetter NVARCHAR(1))
    • Description: disconnects a mapped drive letter
    • Parameters:
      • @DriveLetter: letter which should be disconnected from a file share
    • Returns: BIT
    • Example:
SELECT DbExt.ShareDisconnect('J')

String Operations

  • DbExt.CSV(@Value NVARCHAR(255), @Seperator NVARCHAR(255))
    • Description: aggregates a column to list seperated by @Seperator
    • Parameters:
      • @Value: string that should be evaluated
      • @Seperator: character that seperates the single parts
    • Returns: NVARCHAR(MAX)
    • Example:
DECLARE @CSV_TEST TABLE ( TEXT_FIELD NVARCHAR(50) )
DECLARE @Result NVARCHAR(MAX)

INSERT INTO @CSV_TEST ( TEXT_FIELD ) VALUES ('1'), ('a'), ('r'), ('0')

SELECT DbExt.CSV(TEXT_FIELD, ',') FROM @CSV_TEST;
  • DbExt.Split(@Values NVARCHAR(MAX), @Seperator NVARCHAR(255))
    • Description: splits a comma seperated list into a table
    • Parameters:
      • @Value: string that should be evaluated
      • @Seperator: character that seperates the single parts
    • Returns: TABLE
    • Example:
SELECT * FROM DbExt.Split('1,a,r,0,', ',')
  • DbExt.LPad(@Value NVARCHAR(MAX), @Length SMALLINT, @PaddingChar NCHAR(1))
    • Description: fill the padding char to the left up to length of @length
    • Parameters:
      • @Value: string that should be filled with the padding character
      • @Length: maximum length of string
      • @PaddingChar: character that will be filled
    • Returns: NVARCHAR(MAX)
    • Example:
DECLARE @VALUE NVARCHAR(255) = '125'
SELECT DbExt.LPad(@VALUE, 6, '+')
  • DbExt.RPad(@Value NVARCHAR(MAX), @Length SMALLINT, @PaddingChar NCHAR(1))
    • Description: fill the padding char to the right up to length of @length
    • Parameters:
      • @Value: string that should be filled with the padding character
      • @Length: maximum length of string
      • @PaddingChar: character that will be filled
    • Returns: NVARCHAR(MAX)
    • Example:
DECLARE @VALUE NVARCHAR(255) = '125'
SELECT DbExt.RPad(@VALUE, 6, '$')
  • DbExt.SubStr(@Value NVARCHAR(MAX), @StartIndex SMALLINT)
    • Description: cuts the complete string from @StartIndex and returns it
    • Parameters:
      • @Value: string that should be evaluated
      • @StartIndex: index position that should return up to the end
    • Returns: NVARCHAR(MAX)
    • Example:
SELECT DbExt.SubStr('Munich lost champions league final', 8);
  • DbExt.Remove(@Value NVARCHAR(MAX), @StartIndex SMALLINT, @Count SMALLINT)
    • Description: removes parts of a string
    • Parameters:
      • @Value: string that should be evaluated
      • @StartIndex: start index position to remove
      • @Count: number of characters that should be removed. if it is null all characters to the end will be removed
    • Returns: NVARCHAR(MAX)
    • Example:
SELECT DbExt.Remove('Munich lost champions league final', 8, 5);
SELECT DbExt.Remove('Munich lost champions league final', 8, NULL);
  • DbExt.TrimStart(@Value NVARCHAR(MAX), @TrimChars NVARCHAR(10))
    • Description: trims leading characters of a string
    • Parameters:
      • @Value: string that should be evaluated
      • @TrimChars: characters that should be trimmed
    • Returns: NVARCHAR(MAX)
    • Example:
SELECT DbExt.TrimStart('++123++', '+')
  • DbExt.TrimEnd(@Value NVARCHAR(MAX), @TrimChars NVARCHAR(10))
    • Description: trims trailing characters of a string
    • Parameters:
      • @Value: string that should be evaluated
      • @TrimChars: characters that should be trimmed
    • Returns: NVARCHAR(MAX)
    • Example:
SELECT DbExt.TrimEnd('++123++', '+')
  • DbExt.StartsWith(@Value NVARCHAR(MAX), @StartsWithString NVARCHAR(MAX))
    • Description: check if a string starts with a given filter
    • Parameters:
      • @Value: string that should be evaluated
      • @StartsWithString: filter that is checked against the beginning of the string
    • Returns: BIT
    • Example:
IF DbExt.StartsWith('Munich lost champions league final', 'Mun') = 1
	SELECT '"Mun" was found'
  • DbExt.EndsWith
    • Description: check if a string ends with a given filter
    • Parameters:
      • @Value: string that should be evaluated
      • @StartsWithString: filter that is checked against the ending of the string
    • Returns: BIT
    • Example:
IF DbExt.EndsWith('Munich lost champions league final', 'inal') = 1
	SELECT '"inal" was found'
  • DbExt.Contains(@Value NVARCHAR(MAX), @Filter NVARCHAR(MAX))
    • Description: check if a string ends with a given filter
    • Parameters:
      • @Value: string that should be evaluated
      • @Filter: filter that is checked if @Value contains @Filter
    • Returns: BIT
    • Example:
IF DbExt.[Contains]('Munich lost champions league final', 'league') = 1
	SELECT '"league" was found'

Helper Operations

  • DbExt.Version(@VersionPart NVARCHAR(MAX))
    • Description: return the current installed version of the SqlServerExtensions
    • Parameters:
      • @VersionPart: part that should be returned (major number, minor number, build number or null)
    • Returns: NVARCHAR(MAX)
    • Example:
 SELECT DbExt.[Version]('major');
 SELECT DbExt.[Version]('minoR');
 SELECT DbExt.[Version]('Build');
 SELECT DbExt.[Version](null);

Last edited May 30, 2010 at 7:31 PM by Volleyknaller, version 31

Comments

No comments yet.