Convert a comma separted list to a table in SQL


I thought of sharing this simple function that you can use for splitting a list or nvarchar(max) or anything that is coma separated and sent to DB, into a temp table.

Reason:

Performance is a bottle neck for us. As for that matter, for any SP executions. We cannot live with the IN operator. It is always advicable to go ahead with SET based operations like JOINs in these cases. They also have a good track record of showing an improved performance to a greater extend.

If you need to have such a list in a table format, it is always advicable to have a generic function in your DB to handle such operations, as you might have such scenarios coming up in other SPs also.

Adding the SplitList Function to fulfil the above requirement is shared below:

CREATE FUNCTION [dbo].[SplitList]   
(   
 @List nvarchar(max),   
 @SplitOn nvarchar(5)   
)     
RETURNS @RtnValue table    
(   
     
 Id int identity(1,1),   
 Value nvarchar(100)   
)    
AS     
BEGIN   
   
While (Charindex(@SplitOn,@List)>0)   
Begin    
Insert Into @RtnValue (value)   
Select    
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))    
    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))   
End    
   
Insert Into @RtnValue (Value)   
    Select Value = ltrim(rtrim(@List))   
    Return   
END 

–declare @tempList nvarchar(max)
–set @tempList = ‘1,2,3,4,5,6,7,8,9,10’

–select VALUE FROM dbo.SplitList(@tempList,’,’) — Remember, this will give you a table.. so store this statement into a temptable

 

Run the above function in your DB and you can start using the split list operation. Check the last 3 statements to see the usage of this function.

You normally should do

INSERT INTO @tbl_MyTempTable (ListValue)

SELECT VALUE FROM dbo.SplitList(@tempList,’,’) — my list is a coma separated one..

The above @tbl_MyTempTable will have my list (‘1,2,3,4,5’) stored as each rows..

Hope this was helpful…

How to disable text selection in a table


When the user drags the mouse over the rows, the row text gets selected, looking a bit awkward. Check the image below:

In order to prevent the user from doing these selections and to keep the rows integrity, we can use the following options:

  • CSS Fix –  works with all browsers except IE
  • Controlling KeyEvents (onselectstart and ondragstart)- works with IE

Check the sample code below to see the implementation of both the approaches:

<html>
<head>
<style type=”text/css”>
.selecting {
-moz-user-select: -moz-none;
-webkit-user-select: none;
user-select: none;

}
</style>
</head>
<body>
<table id=”x” onselectstart=”return false” ondragstart=”return false”>
<tr><td>1</td><td>Indy</td></tr>
<tr><td>1</td><td>Amy</td></tr>
<tr><td>1</td><td>John</td></tr>
<tr><td>1</td><td>Luke</td></tr>
<tr><td>1</td><td>Steve</td></tr>
</table>
</body>
</html>

Well now the user mouse drags won’t work and the page will look neat.

jQuery to check the row count of a table


To get the row count of the table, in jQuery, all you need to do is use any of the following approaches:

$(“#mytable tr”).length

$(“#mytable tr”).size() //But the size method calls the length property internally

One thing to be noted here is that the indexing is 1-based and not 0-based. For ex.:

<html>
<head>
<script src=”jquery-1.3.2.min.js” type=”text/javascript” language=”javascript”></script>
<script type=”text/javascript” language=”javascript”>
$(document).ready(function(){
alert($(“#x tr”).length);
});

</script>
</head>
<body>
<table id=”x”>
<tr><td>1</td></tr>
<tr><td>2</td></tr>
<tr><td>3</td></tr>
<tr><td>4</td></tr>
<tr><td>5</td></tr>
<tr><td>6</td></tr>
<tr><td>7</td></tr>
<tr><td>8</td></tr>
</table>
</body>
</html>

Running the above html will give ‘8’ as the result and not 7.
//
//