replace multi words with single in sqlserver

 declare @i int = 1;

;with s1(id,names,cities) 

as 

(select 1 ,'a' ,'test<br/><br/>hyd<br/><br/><br/>blr<br/>mumbai<br/><br/><br/><br/>' 

union all 

select 2,'b','test<br/><br/>hyd<br/><br/><br/>blr<br/>mumbai<br/><br/><br/><br/>' 

union all 

select 3,'c','test<br/><br/>hyd<br/><br/><br/>blr<br/>mumbai<br/><br/><br/><br/>' 

union all 

select 4,'d','test<br/><br/>hyd<br/><br/><br/>blr<br/>mumbai<br/><br/><br/><br/>'

union all 

select 5,'f','test<br/><br/>hyd<br/><br/><br/>blr<br/>mumbai<br/><br/><br/><br/>')

select * into #s1 from s1

select * from #s1

while(@i < 20)

begin

update #s1 set cities = replace(cities, '<br/><br/>', '<br/>')

set @i = @i+1

end

select * from #s1

drop table #s1

GridView data filter with StoredProcedure

SqlCommand cmd=new SqlCommand();

SqlConnection con = new SqlConnection("..your connection string....");
SqlDataAdapter da=new SqlDataAdapter();
DataSet ds=new DataSet();

cmd.Parameters.Clear();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "sp_getall";

cmd.Connection = con;

cmd.Parameters.Add("@p", SqlDbType.VarChar, 20).Value = txtSearch.Text;

da = new SqlDataAdapter(cmd);

da.Fill(ds);

DataView dv = ds.Tables[0].DefaultView;

dv.RowFilter = "eName like '" + mr+"'";

gvsearch.DataSource = dv;

gvsearch.DataBind();

create  proc sp_getall

as
begin
select * from tblemp

end

GridView data filter with StoredProcedure

SqlCommand cmd=new SqlCommand();

SqlConnection con = new SqlConnection("..your connection string....");
SqlDataAdapter da=new SqlDataAdapter();
DataSet ds=new DataSet();

cmd.Parameters.Clear();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "sp_getall";

cmd.Connection = con;

cmd.Parameters.Add("@p", SqlDbType.VarChar, 20).Value = txtSearch.Text;

da = new SqlDataAdapter(cmd);

da.Fill(ds);

DataView dv = ds.Tables[0].DefaultView;

dv.RowFilter = "eName like '" + mr+"'";

gvsearch.DataSource = dv;

gvsearch.DataBind();

create  proc sp_getall

as
begin
select * from tblemp

end

sp for search

create proc sa_tstname(@p varchar(20))
as
begin
select lastname from geninfo where lastname like @p+'%'
end
exec sa_tstname c

sp for search

create proc sa_tstname(@p varchar(20))
as
begin
select lastname from geninfo where lastname like @p+'%'
end
exec sa_tstname c

how to find out highest salaried employee details of a table in sqlserver

select * from tblEmp tbl1 where 0=(select count(distinct salary) from tblEmp tbl2 where tbl1.salary<tbl2.salary)

with s as (select *, row_number() over(order by sal desc) as rn from sam) select * from s where s.rn=1

max possible columns per a table in sqlserver is 1024

copy one column data into other column of a same table using query in sqlserver

update tblEmp set address1=address2

get the list of tables that r available in a particular database in sqlserver

select * from information_schema.tables

or

select * from sys.tables

get the list of columns that r available in a particular table in sqlserver

select column_name from information_schema.columns where table_name='tblEmp'

or

select name from sys.columns where object_id=object_id('tblEmp')

get the serial no. for records of table in sqlserver

select empname, row_number() over(order by empid)as 's.no' from tblEmp

how to find out rank of employees based on their salaries

select empname,dense_rank()over(order by salary)as rank from tblEmp

how to find servername from sqlserver name using query

select host_name()


For app name:

select app_name()

how to find out highest salaried employee details of a table in sqlserver

select * from tblEmp tbl1 where 0=(select count(distinct salary) from tblEmp tbl2 where tbl1.salary<tbl2.salary)



max possible columns per a table in sqlserver is 1024 

copy one column data into other column of a same table using query in sqlserver

update tblEmp set address1=address2

get the list of tables that r available in a particular database in sqlserver

select * from information_schema.tables

or

select * from sys.tables

get the list of columns that r available in a particular table in sqlserver

select column_name from information_schema.columns where table_name='tblEmp'

or

select name from sys.columns where object_id=object_id('tblEmp')

get the serial no. for records of table in sqlserver

select empname, row_number() over(order by empid)as 's.no' from tblEmp

how to find out rank of employees based on their salaries

select empname,dense_rank()over(order by salary)as rank from tblEmp

how to find servername from sqlserver name using query

select host_name()


For app name:

select app_name()

how to make nvarchar id auto incerement (1) function in sqlserver

create function idincr() returns nvarchar(50)

as begin

declare @id int, @cid nvarchar(50), @did int

set @id=(select count(*) from tblcust)

if(@id>0)

begin

set @did= (select max(cast(substring(custid,2,len(custid))as int))from tblcust)+1;

set @cid='A'+cast(@did as nvarchar(50))

end

else 

set @cid='A1'

return @cid

end

Go

select dbo.idincr()

Go