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.


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


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

    @Values TABLE(col VARCHAR(512))

    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   
      SET @Index = CHARINDEX(@Delimiter,@List, @pos);  
      IF @Index  > 0 
            IF (@index- @pos> 0)
                SET @ItemValue = SUBSTRING(@List,@pos, @index- @pos );
                SET @ItemValue=NULL;
        IF (@l-@pos+1)>0
            SET @ItemValue =SUBSTRING( @List, @pos, @l-@pos+1) ;
            SET @ItemValue = NULL;

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

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. .


EXEC proc_receiveMyBlob 0xA05FDAF  (etc) 

 The stored procedure itself would have this signature:

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


   INSERT INTO tblMyBlobs VALUES(@myBlob);


The trick:

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


-- 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)') );



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

EXEC proc_receiveMyBlob 'SGVsbG8gQmFzZTY0' 

or if you like:

EXEC proc_receiveMyBlob 'SGVsbG8gQmFzZTY0


Howto program a LINQ expression which effectively does an aggregate subquery with optional cardinal relationship 0 Reacties

LINQ! Yes, Also I fell in love with linq. 

So here is my first try. And as you fans know, I like to dig into subjects.

Maybe, the title is a little incorrect, but I wanted a query that returns one record, using a subquery in one statement!

Using SQL syntax, this would look like the query just below.: Yes, It seems I can dream SQL, but it was shocking to see how I underestimated the LINQ syntax which took some extra hears from my head.

The query returns events, that have not already been booked full. (There are still some places left)

SELECT [t0].[id], [t0].[maxNumberofParticipants], [t0].[OwerId], [t0].[Description], [t0].[StartTime], [t0].[EndTime],
[t0].[orderId] FROM [dbo].[Activity] AS [t0]
WHERE ([t0].[OwerId] = @p0) AND ([t0].[maxNumberofParticipants] > @p1) AND ([t0].[maxNumberofParticipants] > (ISNULL((
    SELECT SUM([t2].[value])
    FROM (
        SELECT [t1].[countOfPersons] AS [value], [t1].[activityId]
        FROM [dbo].[ActivityParticipant] AS [t1]
        ) AS [t2]
    WHERE [t2].[activityId] = [t0].[id]

I could make this a stored proc, and execute this easily. But that was in my previous life.

So, how to express this using a LINQ query?

The trick with the ISNULL function is to -cast- the countOfPersons field, to a nullable type! Since the GetValueOrDefault() is available only to nullable data types we must cast it to an int? datatype.
The LINQ provider, will translate it finally, when it sends the actual SQL to SQL Server using TSQL function 'COALESC'.
bt.w., it will not use the ISNULL function. 

public IEnumerable<Activity> getActivityParticipableByOwnerWith(Guid ownerGuid)

var query = from a in Activities
where a.OwerId == ownerGuid && a.maxNumberofParticipants > 0 && a.maxNumberofParticipants >
ActivityParticipants.Where(aid => aid.activityId == => (int?)aid.countOfPersons).GetValueOrDefault(0)
select a ;

return query;

Kuddo's to Khaled Moawad, who very patiently helped me to improve the syntax.  b.t.w. guys/girls. Always try it hard yourself, before you ask the global community for support. That makes your brains retain better :)

My next goal is to make LINQ like a natural language for me, like SQL was :)