Row to Column conversion separated delimiter in sql

declare @str1 varchar(max)=',,sql,,,asp,technique,yield,ado,,'
declare @str2 varchar(max)=''
create table #tbl1 (id varchar(max))

while(charindex(',',@str1)>0)
begin
if isnull(@str2,'')<>''
insert into #tbl1 select @str2
select @str2 = substring(@str1,1,charindex(',',@str1)-1)
set @str1 = substring(@str1,charindex(',',@str1)+1,len(@str1))
end

if isnull(@str2,'')<>''
insert into #tbl1 select @str2

select * from #tbl1
drop table #tbl1

Reset Identity for the table in sqlserver

CREATE TABLE IdentTest (Ident INT NOT NULL IDENTITY (1,1), varfield varchar(100))
INSERT INTO IdentTest VALUES ('abc')

DBCC CHECKIDENT ('IdentTest',RESEED,100)

-- here 100 starts identity from 101 if is there any data in table otherwise it starts from 100, for 1 you have to  use 0
INSERT INTO IdentTest VALUES ('def')

SELECT * FROM IdentTest
--Note: if the inserting with already existing identity, then it will raise the error.
--****first POST of the Year

Happy new Year with sqlserver query

SELECT CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15) + CHAR(15) + CHAR(15) + CHAR(15) + CHAR(13) + CHAR(72) + CHAR(65) + CHAR(80) + CHAR(80)+ CHAR(89) + ' ' + CHAR(78) + CHAR(69)+CHAR(87)+ ' ' + CHAR(89)+ CHAR(69)+ CHAR(65) + CHAR(82) +CHAR(13)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)

Get each char ASCII value from :

DECLARE @i int
SET @i = 1
WHILE @i < = 256 Begin PRINT CHAR(@i) + ' --> ' + CONVERT(VARCHAR,@i)
SET @i = @i + 1
END

***Last POST of the Year

Create a table named 'select' with column 'table'

SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" int)

When SET QUOTED_IDENTIFIER is ON, all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not usually allowed in Transact-SQL identifiers

Reference: http://msdn.microsoft.com/en-us/library/aa259228(v=sql.80).aspx

or

CREATE TABLE [SELECT] ([TABLE] INT)

Customize sql startup query window from:

C:\Users\My-PC\AppData\Local\VirtualStore\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql\SqlFile.sql

(Chclt)

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SqlFile.sql

Replace multiple spaces with one/none in sql server

select replace(replace(replace(LTrim(RTrim('      6      Spaces  6      Spaces.      ')),'  ',' |'),'| ',''),
--'|','')
' |','')

select replace(
replace(
replace(
LTrim(RTrim('      6      Spaces  6      Spaces.      '))
,' ',' |')
,'| ',''),
' |',' ')

--Trim the field
--Mark double spaces
--Delete double spaces offset by 1
--Tidy up

sort array of int values without using built-in functions (using Bubble sort)

string res = "";
int[] arr = { 800, 11, 50, 771, 649, 770, 240, 9 };
for (int k = 0; k < arr.Length; k++)
{
for (int i = 0; i < arr.Length - 1; i++)
{
if (arr[i] > arr[i + 1])
{
int hold = arr[i + 1];
arr[i + 1] = arr[i];
arr[i] = hold;
}
}
res += arr[k].ToString();
arr[k] = arr[k];
}

string s = "1,2,3,,3,2";
string[] ss = (s.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
for (int k = 1; k < ss.Length; k++)
{
for (int i = 0; i < (ss.Length - 1); i++)
{
if (Convert.ToInt32(ss[i].Trim()) > Convert.ToInt32(ss[i + 1].Trim()))
{
string hold = ss[i];
ss[i] = ss[i + 1];
ss[i + 1] = hold;
}
}
}
s = string.Join(",", ss);

split string in c# with regular expression instead of forloop

string s = "er32(s),re4'wr(a),f2.0(t)A(y),(a)";
        s = Regex.Replace(s, @"[\w\s';.&,:]+\(([\w\d.]+)\)", "$1");
        Response.Write(s);

Convert column to row in SQL Server

select (select convert(varchar(max),id)+',' from #tbl1 for xml path('')) colTOrow

Get the html control to asp without using runat='server' property

string strValue = Page.Request.Form["name of the control"].ToString();

How to get the current row in the grid view rowcommand event

GridViewRow row = ((LinkButton)e.CommandSource).NamingContainer as GridViewRow;

In RowDataBound for datakeys

GridView1.DataKeys[e.Row.RowIndex].Value.ToString()

((DataRowView)e.Row.DataItem)["mainid"].ToString()

persistedselection property for a gridview control in asp

You can specify how row selection is persisted when the GridView control is in paging mode. By default, row selection is persisted by row index. For example, if the second row in a page is selected, the second row in all pages will be selected.

Alternatively, you can specify that row selection is persisted by data-row key. In that case, when a row is selected, rows in other pages are not selected. To enable this functionality, set the EnablePersistedSelection property to true as shown in the following example:

<asp:GridView id="GridView1" runat="server" PersistedSelection="true">
</asp:GridView>

datarow to dataset in asp

DataTable dt = (DataTable)ViewState["tabData"];
DataRow[] dr = dt.Select("test_cd='899'");

DataSet dsnew = new DataSet();
DataTable dtnew = dt.Clone();
dtnew.Constraints.Clear();
DataRow drnew = dtnew.NewRow();

drnew.ItemArray = dr[0].ItemArray;
dtnew.Rows.Add(drnew);
dsnew.Tables.Add(dtnew);

Query to attaching of database using only .mdf file without associated log file

CREATE DATABASE ... FOR ATTACH_REBUILD_LOG

with log file: CREATE DATABASE ... FOR ATTACH

Order of the sqlquery that logically processed

What is the order of execution in the following list?

• select


• from


• where


• group by


• order by


• having

A: from,where,group by,having,select,order by

ref:  http://msdn.microsoft.com/en-us/library/ms174149.aspx