Tool Scripts

1. Function: 16进制转字符串

 Create FUNCTION [dbo].[f_hextostr] (@hexstring VARCHAR(max))
 RETURNS VARCHAR(max)
 
 AS
 
 begin
 declare @char1 char(1), @char2 char(1), @strlen int, @currpos int, @result varchar(max)
  set @strlen=len(@hexstring)
  set @currpos=1
  set @result=''
  while @currpos<@strlen
   begin
    set @char1=substring(@hexstring,@currpos,1)
    set @char2=substring(@hexstring,@currpos+1,1)
    if (@char1 between '0' and '9' or @char1 between 'A' and 'F')
     and (@char2 between '0' and '9' or @char2 between 'A' and 'F')
     set @result=@result+
      char((ascii(@char1)-case when @char1 between '0' and '9' then 48 else 55 end)*16+
      ascii(@char2)-case when @char2 between '0' and '9' then 48 else 55 end)
    set @currpos = @currpos+2
   end
  return @result
 end

2. Function: 批量替换

Create Function [dbo].[RemoveCustomCharacters](@InitialString VarChar(8000), @Regex VarChar(8000), @ReplaceWith VarChar(8000))
Returns VarChar(8000)
AS
Begin
Declare @KeepValues as varchar(50)
Set @KeepValues = '%'+@Regex+'%'
While PatIndex(@KeepValues, @InitialString) > 0
Set @InitialString = Stuff(@InitialString, PatIndex(@KeepValues, @InitialString), 1, @ReplaceWith)
 
Return @InitialString
End

3. 通过SQL语句读取文件

Select * From OpenRowSet(BULK N'FilePath', SINGLE_CLOB) As Content