Database

Mycroft Holmes wrote:

Doing that is akin to sticking your finger in the power point, may not kill you but your fingers gonna sting for a while.

Mycroft Holmes wrote:

So did the extra set of brackets work?

declare @t table(Company_Name varchar(50))
insert into @t 
	select 'ComxxxA Co.' union all select 'ComxxxA Co.' union all
	select 'ComxxxA Co.,Ltd.' union all select 'ComxxxA Co.,Ltd.' union all
	select' ComxxxA Co.,' union all select 'ComxxxA Co.,' union all
	select 'ComxxxA ' union all select 'ComxxxA ' union all 
	select 'Comx' union all select 'ComxxxxxxxB Co.,Ldt' union all
	select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co.,Ldt' union all
	select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co' union all
	select 'ComxxxxxxxB Co.,' union all select 'ComxxxxxxxB Co.,'

select  Company_Name
from @t 
where replace(Company_Name, ' ','') not like '%[.,]%'
group by Company_Name 

Company_Name
Comx
ComxxxA 
ComxxxxxxxB Co

ALTER TABLE dbo.tablename ADD CONSTRAINT
	DF_TimeStamp DEFAULT '20091218' FOR TimeStamp

Ashfield wrote:

How about defining a default value for the column?

select column_list....
from table1 t1
where indicator > 30
and exists (select 1 from table1 t2 where t2.pk = t1.pk and t2.indicator < 30 and t2.dateadded = dateadd(day,-3,t1.dateadded))

declare @t table(childid int,name varchar(20),code int,parentid int)
insert into @t values (6219,'HBO',02,0)
insert into @t values(6220,'HBO1', 0201,6219)
insert into @t values(6221,'HEAO',0202,6219)
insert into @t values(6222,'HTS',0203,6219)
insert into @t values(6231,'MEAO',0403,6228)
insert into @t values(6232,'MTS',0404,6228)
insert into @t values(6234,'Midde',05,0)
insert into @t values(6235,'Overi',06,0)
insert into @t values(6228,'MBO',04,0)
insert into @t values(6236,'WO',10,0)
insert into @t values(6237,'Post',07,0)
insert into @t values(6239,'Basi',NULL,NULL)
insert into @t values(6448,'Athe',05001,6234)
select * from @t

;with cte as
(
	select 
		cast(t1.childid as varchar(1000)) [path]
		, t1.childid
		,t1.name
		,t1.code
		,t1.parentid 
		,0 AS [Level] from @t t1 where parentid =  0 or parentid is null

	union all
	select 
		cast([path] + '/' + cast(t1.childid as varchar(1000)) as varchar(1000)) [path]
		,t1.childid
		,t1.name
		,t1.code
		,t1.parentid 
		,c.[Level]+1 AS [Level]from @t t1
	join cte c
	on c.childid = t1.parentid
)
select childid,name,code,parentid
from cte order by [path]

childid	name	code	parentid
6219	HBO	2	0
6220	HBO1	201	6219
6221	HEAO	202	6219
6222	HTS	203	6219
6228	MBO	4	0
6231	MEAO	403	6228
6232	MTS	404	6228
6234	Midde	5	0
6448	Athe	5001	6234
6235	Overi	6	0
6236	WO	10	0
6237	Post	7	0
6239	Basi	NULL	NULL