sql 字串操作以及其他

標籤：數值   第一個   union   今天   一個   技術   ldb   copy   進位   

1.字串操作函數

假設@a=‘abc789‘

1.charindex 返回字元或者字串在另一個字串中的起始位置

charindex(‘b‘,@a)  --結果：2

2.substring 返回字元、二進位、文本或映像運算式的一部分（截取字串）

substring(@a,1,len($a))  --substring(@a,start,length)

結果：abc789

3.left 返回從字串左邊開始指定個數的字元

left(@a,1) --結果：a

left(@a,3) --結果：abc

4.right 返回從字串右邊開始指定個數的字元

right(@a,1)  --結果：9

right(@a,3) --結果：789

right(‘000000‘+cast(id as varchar),7)  --左側六位自動補零

5. patindex 返回指定運算式中某模式第一次出現的起始位置

patindex(‘%[^0-9]%‘,@a) --結果：1

patindex(‘%[0-9]%‘,@a) --結果：4

6.stuff 刪除指定長度的字元並在指定的起始點插入另一組字元

stuff(‘abc789‘, 2, 3, ‘ijklmn‘) --stuff(@a,start,length,replacestr)

結果：aijklmn89

樣本：

INSERTINTO table_1(name,classid) 
SELECT‘蘋果‘,1 UNIONALL 
SELECT‘香蕉‘,1 UNIONALL 
SELECT‘櫻桃‘,1 UNIONALL
SELECT‘可可(牛奶)‘

select case charindex(‘(‘,[name]) when 0 then [name] else substring([name],0,charindex(‘(‘,[name])) end from table_1

查詢結果：

...

櫻桃

可可　　　　--被截取了

7.replace替換斷行符號、換行、斷行符號分行符號

SELECT *, REPLACE(detail, CHAR(13) , ‘<br>‘)

SELECT *, REPLACE(detail, CHAR(10), ‘<br>‘)

SELECT *, REPLACE(detail, CHAR(13) + CHAR(10), ‘<br>‘) 

2.表變數是否為空白

[email protected]=COUNT(1) FROM @resultTempIF(@tmpCount>0)BEGIN        ......END

3.sp_executesql 執行動態Sql，支援參數

DECLARE @strsql NVARCHAR(500)SET @strsql=‘SELECT @total=SUM(a.total) FROM ‘[email protected]+‘.dbo.table_1 a,TestDBA.dbo.Project b WHERE a.code=b.code‘EXEC sp_executesql @strsql,N‘@total AS FLOAT OUTPUT‘,@alltotal OUTPUT

 4.多個欄位中取最大值

select max(m)from(    select max(aa)m from TableName    union all    select max(bb) from TableName    union all    select max(cc) from TableName) t

 5.日期函數

 1.當前系統日期、時間

    select getdate()

 2.dateadd 在向指定日期加上一段時間的基礎上，返回新的 datetime 值

    例如：向日期加上2天

    select dateadd(day,2,‘2004-10-15‘) --返回：2004-10-17 00:00:00.000

 3.datediff 返回跨兩個指定日期的日期和時間邊界數。

    select datediff(day,‘2004-09-01‘,‘2004-09-18‘) --返回：17

 4.datepart 返回代表指定日期的指定日期部分的整數。

    select datepart(month, ‘2004-10-15‘) --返回 10

 5.datename 返回代表指定日期的指定日期部分的字串

    select datename(weekday, ‘2004-10-15‘) --返回：星期五

 6. day(), month(),year() --可以與datepart對照一下

 7.當前日期、目前時間   

    select 當前日期=convert(varchar(10),getdate(),120),目前時間=convert(varchar(8),getdate(),114)

 8.本年第幾周、今天周幾

    select 今天是周幾=datename(dw,‘2004-10-15‘)

    select 本年第多少周=datename(week,‘2004-10-15‘),今天是周幾=datename(weekday,‘2004-10-15‘)  

 9.短日期格式

    select  convert(varchar(10),getDate(),120)

    select  CONVERT(VARCHAR(10),getdate(),23)

      函數                                                參數/功能
  GetDate( )                                          返回系統目前的日期與時間
  DateDiff (interval,date1,date2)              以interval 指定的方式，返回date2 與date1兩個日期之間的差值 date2-date1
  DateAdd (interval,number,date)            以interval指定的方式，加上number之後的日期
  DatePart (interval,date)                        返回日期date中，interval指定部分所對應的整數值
  DateName (interval,date)                      返回日期date中，interval指定部分所對應的字串名稱

  參數 interval的設定值如下：

  值 縮 寫（Sql Server）                           Access 和 ASP 說明
  年：Year             Yy         yyyy                  1753 ~ 9999
  季：Quarter        Qq         q                       1 ~ 4
  月：Month          Mm        m                      1 ~ 12
  一年的日數,一年中的第幾日：Day of year        Dy          y            1-366
  日：Day             Dd         d                       1-31
  一周的日數,一周中的第幾日：Weekday            Dw         w            1-7
  周，一年中的第幾周：         Week                  Wk        ww          0 ~ 51
  時：Hour            Hh         h                       0 ~ 23
  分：Minute          Mi          n                       0 ~ 59
  秒：Second         Ss         s                       0 ~ 59
  毫秒：Millisecond  Ms         -                       0 ~ 999

  access 和 asp 中用date()和now()取得系統日期時間；其中DateDiff,DateAdd,DatePart也同是能用於Access和asp中，這些函數的用法也類似

  一個月第一天
  SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

  本周的星期一

  SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

  一年的第一天

  現在用年(yy)的時間間隔來顯示這一年的第一天。

  SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

  季度的第一天

  假如你要計算這個季度的第一天，這個例子告訴你該如何做。

  SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

  當天的半夜

  SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

  上個月的最後一天

  它通過從一個月的最後一天這個例子上減去3毫秒來獲得。有一點要記住，在Sql Server中時間是精確到3毫秒。這就是為什麼我需要減去3毫秒來獲得我要的日期和時間。

  SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

  計算出來的日期的時間部分包含了一個Sql Server可以記錄的一天的最後時刻(“23:59:59:997”)的時間。

  去年的最後一天

  SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

  本月的最後一天

  SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

  本年的最後一天

  SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

  本月的第一個星期一

  select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

 6.金額加逗號 

 DECLARE @i MONEY
 SET @i=123456789.00
 SELECT CONVERT(varchar,@i,1)

 DECLARE @i VARCHAR(50)
 SET @i=123456789.00
 SELECT CONVERT(varchar,CONVERT(money,@i),1)

7、查庫名、庫ID

SELECT Name FROM Master..SysDatabases ORDER BY Name

8、判斷是否存在表名、預存程序、函數

--判斷庫是否存在
if exists(select * from master..sysdatabases where name=N‘庫名‘)
drop database 庫名
GO

-- 判斷要建立的表名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[表名]‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1)
drop table [dbo].[表名]
GO

--列是否存在
IF COL_LENGTH( ‘表名‘,‘列名‘) IS NULL
alter table 表名 drop constraint 預設值名稱
go
alter table 表名 drop column 列名
go

--判斷要建立暫存資料表是否存在
If Object_Id(‘Tempdb.dbo.#Test‘) Is Not Null
drop table #Test
GO

-- 判斷要建立的預存程序名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[預存程序名]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1)
drop procedure [dbo].[預存程序名]
GO

-- 判斷要建立的視圖名是否存在
if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[視圖名]‘) and OBJECTPROPERTY(id, N‘IsView‘) = 1)
drop view [dbo].[視圖名]
GO

-- 判斷要建立的函數名是否存在
if exists (select * from sysobjects where xtype=‘fn‘ and name=‘函數名‘)
if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[函數名]‘) and xtype in (N‘FN‘, N‘IF‘, N‘TF‘))
drop function [dbo].[函數名]
GO

 9.SQL2008組態管理工具服務顯示遠端程序呼叫失敗

開啟程式和功能，卸載了一個叫"Microsoft SQL Server 2012LocalDB"，重新開啟SQL組態管理員，發現可以了。

10.還原資料庫的時候，提示"因為資料庫正在使用，所以無法獲得對資料庫的獨佔訪問權"

1,設定資料庫在單一使用者模式下工作。
設定方法：在需要還原的資料庫上右擊，在右鍵功能表命令上選擇"屬性"- >"選項"- >"狀態"- >"限制訪問"- >"Single"。這是SQLSERVER2005的功能表命令，其它版本請自己尋找。

2,利用SQL語句，殺死正在使用該資料庫的所有進程，自己以前在做一個SQL SERVER操作小工具的時候有寫過該功能的SQL，貼出來供大家參考：
複製代碼 代碼如下:
declare @dbname varchar(50)
set @dbname=‘資料庫名稱‘
declare @sql varchar(50)
declare cs_result cursor local for select ‘kill ‘+cast(spid as varchar(50)) from sys.sysprocesses where db_name(dbid)[email protected]
open cs_result
fetch next from cs_result into @sql
while @@fetch_status=0
begin
execute(@sql)
fetch next from cs_result into @sql
end
close cs_result
deallocate cs_result
該SQL語句利用遊標迴圈所有正在使用該資料庫的進程，並通過kill命令殺死進程。

3,利用SQL語句,斷開所有使用者連結，並復原所有事務，具體SQL語句如下：
複製代碼 代碼如下:
ALTER DATABASE [資料庫名稱]
SET OFFLINE WITH ROLLBACK IMMEDIATE

注意:在使用方法2與3時，不要在需要的還原的資料庫下執行，建議在master資料庫下面執行。

11、不允許儲存更改，阻止儲存要求重新建立表的更改

在SQL Server Management Studio點擊菜單【工具】 - 【選項】，
開啟【選項】對話方塊，展開【Designers】，取消【阻止儲存要求重新建立表的更改】複選框

sql 字串操作以及其他（轉）