1.字符串截取、字符串转数字
--Server、8.30
select SUBSTRING('SqlServer_2008',4,6) as DB,
CONVERT(float,SUBSTRING('8.30~9.00',1,len('8.30~9.00')- CHARINDEX('~',REVERSE('8.30~9.00')))) as Time
CONVERT(int,'字符串')
2.系统时间
select GETDATE() 系统时间
3.字符串拼接
方式一:
--select @p_AllPARTOFCHECK = (select CAST(t.PARTOFCHECK as varchar)+'|' from QUEUEDETAIL t where t.APPOINTSID = @p_APPOINTSID for xml path('') )
--select @p_AllDirection = (select CAST(t.DIRECTION as varchar)+'|' from QUEUEDETAIL t where t.APPOINTSID = @p_APPOINTSID for xml path('') )
拼接结构如:字符串A|字符串B|
方式二:
select @p_AllPARTOFCHECK = (stuff((select '|' + PARTOFCHECK from QUEUEDETAIL where appointsid = @p_APPOINTSID for xml path('')),1,1,''))
select @p_AllDirection = (stuff((select '|' + direction from QUEUEDETAIL where appointsid = @p_APPOINTSID for xml path('')),1,1,''))
4.修改字段添加默认值
--修改字段添加默认值
ALTER TABLE HISAPPLICATIONFORM add CONSTRAINT defaultValue DEFAULT 0 FOR EMERGENCYFLAG
--修改字段添加默认值
ALTER TABLE 表名 add CONSTRAINT 约束名称 DEFAULT 默认值 FOR 字段名称
--新增字段
if not exists(select * from syscolumns where id=object_id('QUEUEDETAIL') and name='FillerOrderNumber') begin
ALTER TABLE QUEUEDETAIL ADD FillerOrderNumber VARCHAR(50) default '';
end
go