Cross join Vs Loop in SQL

 Search different keywords in sql database through Cross join while using loop
 First run Create function code then execute the database query

// Create Function First
Create function [dbo].[fn_split](
@str varchar(max),
@delimiter char(1)
)
returns @returnTable table (idx int primary key identity, item varchar(8000))
as
begin
declare @pos int
select @str = @str + @delimiter

while len(@str) > 0
    begin
        select @pos = charindex(@delimiter,@str)
        if @pos = 1
            insert @returnTable (item)
                values (null)
        else
            insert @returnTable (item)
                values (substring(@str, 1, @pos-1))
        select @str = substring(@str, @pos+1, len(@str)-@pos)
      
    end
return
end



// Query part

DROP TABLE vTable3
DROP TABLE vTable1

CREATE TABLE vTable1 (id1 INT, NAME VARCHAR(100)Primary Key (id1))
CREATE TABLE vTable3 (id3 INT, id1 int, Deductions VARCHAR(100),Primary Key (id3),
Foreign Key (id1) references vTable1(id1))


INSERT INTO vTable1
    SELECT 1,'Shamas' union all
    select 5,'Qamar' UNION ALL
    SELECT 2,'Atif' UNION ALL
    SELECT 3,'Kashif' UNION ALL
    SELECT 4,'Imran'

INSERT INTO vTable3
    SELECT 1,2,'70' UNION ALL
    SELECT 2,5,'80'


DECLARE @vParam VARCHAR(100)
SET @vParam = '70,s'

SELECT distinct t1.*,t3.* FROM vTable1 t1
left Outer join vTable3 t3 on t3.id1=t1.id1
CROSS APPLY (SELECT item FROM dbo.[fn_split](@vParam,',')) b
WHERE
(t1.NAME LIKE '%' + b.item + '%'
  or t3.Deductions LIKE '%' + b.item + '%'
)

Get Registered Version of Microsoft Products

Just log on http://www.asp.net
Create your account and get VS2010, SQL Server 2008 R2 and Windows Server 2008 R2 for 3 years

Regular Expression for Date

Regular Expression for date format MM/dd/yyyy

^([1-9]|0[1-9]|1[012])[/]([1-9]|0[1-9]|[12][0-9]|3[01])[/](19|20)\\d\\d$

Convert HTML To Text

Below code convert html code into text form and remove all tags related to html, script and style etc. Code remove all tags with the help of regular expressions so you don't need any parser for this, and its more efficient then any parser.

public static string ConvertHTMLtoText(string input)
    {
        input = Regex.Replace(input, @"<script.*?>[\s\S]*?</.*?script>", "");

        input = Regex.Replace(input, @"<style.*?>[\s\S]*?</.*?style>", "");

        input = Regex.Replace(input, @"(&lt;|&lt;/)( )*\w*&gt;", "");

        return input = Regex.Replace(input, @"<( )*([^>])*>", "");

    }