The Ultimate T-SQL String Splitter (function)0 Reacties

 

I just want to share this function, since there are a lot of version around, which are not resistant against zero positions advance, for instance, if you split ‘1,2,3’ into a table, it would be find, but what if one element is empty, such as ‘1,,3’? This function deals with it setting returning a null element.

Usage:

SELECT * FROM [udf_SplitVarchar2Table]('one,two,three', ',')

returns:

ALTER FUNCTION [dbo].[udf_SplitVarchar2Table]
(
    @List varchar(max),
    @delimiter VARCHAR(10)
)

RETURNS
    @Values TABLE(col VARCHAR(512))
AS

BEGIN 
    IF @List IS NULL OR LEN(@List) = 0 RETURN;
 
  SET @List = replace(@List,CHAR(39)+CHAR(39),CHAR(39))
 
  DECLARE @Index INT=1; 
  DECLARE @ItemValue varchar(100);  
  DECLARE @pos INT = 1;
  DECLARE @l INT = LEN(@List);

  WHILE @Index > 0   
    BEGIN        
      SET @Index = CHARINDEX(@Delimiter,@List, @pos);  
   
      IF @Index  > 0 
            IF (@index- @pos> 0)
                SET @ItemValue = SUBSTRING(@List,@pos, @index- @pos );
            ELSE
                SET @ItemValue=NULL;
      ELSE
        IF (@l-@pos+1)>0
            SET @ItemValue =SUBSTRING( @List, @pos, @l-@pos+1) ;
        ELSE
            SET @ItemValue = NULL;

      INSERT INTO @Values (col) VALUES (@ItemValue);    
      SET @pos = @index+1;
    END
    RETURN;
END

T-SQL Alternative to hexadecimal binary strings?0 Reacties

I found an easy way to have binary parameters as base64 encoded string. You might wonder, why bother?

Well, in a a well used environment, size and compactness of data over the wire, still matters! Because a binary value is sent as a hexadecimal over the wire; Hexadecimals are 4 times the size of one byte. Base64 encoded strings however, just need +/- 3 times the size of one byte. .

example:

EXEC proc_receiveMyBlob 0xA05FDAF  (etc) 

 The stored procedure itself would have this signature:

CREATE PROC  @myBLob varbinary(max)    -- or image whatever

BEGIN 

   INSERT INTO tblMyBlobs VALUES(@myBlob);

END 

The trick:

 CREATE PROC  @myBLob xml  -- <-- use the xml T-SQL data type

BEGIN 

-- remember, the binary field in SQL must not be changed to xml, keep it as binary! 

   INSERT INTO tblMyBlobs VALUES(@myBlob.value('xs:base64Binary(.)', 'varbinary(max)') );

END  

 

The call to the stored proc (obviously) looks something like this:

EXEC proc_receiveMyBlob 'SGVsbG8gQmFzZTY0' 

or if you like:

EXEC proc_receiveMyBlob 'SGVsbG8gQmFzZTY0