C# 连接SQL数据库 常用连接字符串 http://www.cnblogs.com/delphinet/archive/2011/06/24/2088765.htmlSQL Server 常用连接字符串标签：本文系统来源：http://www.cnblogs.com/dblg/p/4769606.html
table tb ([id] int,[name] varchar(2)) insert into tb select 1,‘aa‘ union all select 2,‘bb‘ union all select 1,‘cc‘ union all select 3,‘dd‘ union all select 2,‘ee‘select * from tb --查询 select id,name=stuff((select ‘,‘+name from tb where id=t.id for xml path(‘‘)),1,1,‘‘) fromtb t group byid方法二：CREATE FUNCTION GET_STRING(@ID INT) RETURNS VARCHAR(50) AS BEGIN DECLARE @NAME VARCHA...
declare @xml xml declare @propertyName varchar(50) declare @str nvarchar(max) set @propertyName = ‘s_Code‘ set @xml = ‘<root> <row propertyName="s_Code">test</row><row propertyName="s_Posword">admin</row></root>‘ SELECT @str =@xml.value(‘(/root/row[@propertyName=sql:variable("@propertyName")])[1]‘, ‘nvarchar(max)‘) select @str declare @xml xml declare @propertyName varcha...
declare @xml xml declare @propertyName varchar(50) declare @str nvarchar(max) set @propertyName = ‘s_Code‘ set @xml = ‘<root> <row propertyName="s_Code">test</row><row propertyName="s_Posword">admin</row></root>‘ SELECT @str =@xml.value(‘(/root/row[@propertyName=sql:variable("@propertyName")])[1]‘, ‘nvarchar(max)‘) select @str declare @xml xml declare @propertyName varcha...
Select 2 a.xm,xk=substring(a.xk,b.number,charindex(‘,‘,a.xk+‘,‘,b.number)-b.number) 3 from 4 表A a join master..spt_values b 5 ON b.type=‘p‘ AND b.number BETWEEN 1 AND LEN(a.xk) 6 where 7 substring(‘,‘+a.xk,b.number,1)=‘,‘--1.将字符串转换为列显示 if object_id(‘tb‘) is not null drop table tb go create table tb([编号] varchar(3),[产品] varchar(2),[数量] int,[单价]...
0Feb 22 2006 4:26PMCONVERT(CHAR(19), CURRENT_TIMESTAMP, 0)102/22/06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1)206.02.22CONVERT(CHAR(8), CURRENT_TIMESTAMP, 2)322/02/06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 3)422.02.06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 4)522-02-06CONVERT(CHAR(8), CURRENT_TIMESTAMP, 5)622 Feb 06CONVERT(CHAR(9), CURRENT_TIMESTAMP, 6)7Feb 22, 06CONVERT(CHAR(10), CURRENT_TIMEST...
Provider = SQLNCLI10;Server = .\SQLExpress;AttachDbFilename = c:\asd\qwe\mydbfile.mdf; Database = dbname; Trusted_Connection = Yes; 在连接到SQL Server Express实例时，从数据目录下附加一个数据库文件Provider = SQLNCLI10;Server = .\SQLExpress;AttachDbFilename = |DataDirectory|mydbfile.mdf; Database = dbname;Trusted_Connection = Yes; 数据库镜像Provider = SQLNCLI10;Data Source = myServerAddress;Failov...
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO/* by kudychen 2011-9-28 */ CREATE function [dbo].[SplitString] (@Input nvarchar(max), --input string to be separated@Separator nvarchar(max)=‘,‘, --a string that delimit the substrings in the input string@RemoveEmptyEntries bit=1 --the return value does not include array elements that contain an empty string ) returns @TABLE table ([Id] int ident...
二.数学函数与字符串函数三.练习 1.创建一个学生信息表，根据要求写出程序 2.新建一个超市表，进了十种商品，个数都是十件 SQL server聚合函数、数学函数、字符串函数标签：本文系统来源：http://www.cnblogs.com/Fate-rail/p/4982922.html
IF OBJECT_ID(N‘dbo.ufn_GetNextCodeChars‘, ‘FN‘) IS NOT NULL2 BEGIN3 DROP FUNCTION dbo.ufn_GetNextCodeChars;4 END5 GO6 7 --==================================8 -- 功能: 获取下一个编码字符串9 -- 说明: 具体实现阐述 10 -- 作者: XXX11 -- 创建: yyyy-MM-dd12 -- 修改: yyyy-MM-dd XXX 修改内容描述13 --==================================14 CREATE FUNCTION dbo.ufn_GetNextCodeChars15 (16 @chvCodeCha...
"1,2,3";string sqltxt="select * from tablename where id in ("+aa+")";可以把形如“1,2,3”这样的字符串转换为一个临时表，这个表有一列，3行，每一行存一个项目（用逗号分隔开的一部分） 该函数可以这样写:GO /****** Object: UserDefinedFunction [dbo].[StrToTable] Script Date: 2016/3/1 18:53:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Function [dbo].[StrToTable](@str varchar(1000)) R...
"（单引号）的运用：在sql server中，两个" ‘ "(单引号)在拼接字符串的情况下运用，就是表示拼接上了一个" ‘ "单引号字符串。 在此列举出正确的写法：set @sql = ‘update #temp_monthKQ_Users set day‘+@curruntCount+‘=‘‘‘+@descTemp+‘‘‘ where user_id = ‘‘‘+@user_id+‘‘‘ ‘;错误的写法如下：set @sql = ‘update #temp_monthKQ_Users set day‘+@curruntCount+‘=‘‘‘+@descTemp+‘‘‘ where user_id = ‘...
a.*,e.NoRead,e.AlRead,ToUserName= STUFF(( SELECT ‘,‘ + NameFROM Users dwhere PATINDEX(‘%,‘ + RTRIM(Account) + ‘,%‘,‘,‘ + a.ToUserID + ‘,‘)>0ORDER BY PATINDEX(‘%,‘ + RTRIM(Account) + ‘,%‘,‘,‘ + a.ToUserID + ‘,‘) --加上排序不变（如:2,1,3）FOR XML PATH(‘‘)), 1, 1,‘‘),case a.NoticeTypeID when 2 then ‘调查问卷‘ when 3 then ‘奖品订单‘ else ‘普通通知‘ end as Notice...
select ‘A‘+ space(2)+‘B‘取子字符串substring(string,start_position,length) 可以从任意位置取任意长度的子字符串，left(string,length) 从左侧开始取子字符串right(string,length)从右侧开始取子字符串select substring(‘HelloWorld!‘,6,6)select left(‘HelloWorld!‘ ,5)select right(‘HelloWorld!‘ ,6)字符串替换replace（string,要被替换的字符串，替换的字符串）select replace(‘HelloWorld!‘,‘o‘,‘e‘) 结果...
USE [dataChange] GO /****** Object: UserDefinedFunction [dbo].[f_splitSTR] Script Date: 05/09/2016 15:04:21 <span style="color:#ff0000;">自己修改数据库名，直接执行就可以了</span>******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[f_splitSTR](@s varchar(8000), --待分拆的字符串@split varchar(10) --数据分隔符 )RETURNS @re TABLE(col varchar(max)) AS BEGINDECLARE ...