显示标签为“SQL Server”的博文。显示所有博文
显示标签为“SQL Server”的博文。显示所有博文

使用嵌套触发器

{ Posted on 星期五, 十二月 24, 2010 by Kaiser.XKw }

如果一个触发器在执行操作时引发了另一个触发器,而这个触发器又接着引发下一个触发器……这些触发器就是嵌套触发器。触发器可嵌套至 32 层,并且可以控制是否可以通过"嵌套触发器"服务器配置选项进行触发器嵌套。

如果允许使用嵌套触发器,且链中的一个触发器开始一个无限循环,则超出嵌套级,而且触发器将终止。

可使用嵌套触发器执行一些有用的日常工作,如保存前一触发器所影响行的一个备份。例如,可以在 titleauthor 上创建一个触发器,以保存由 delcascadetrig 触发器所删除的 titleauthor 行的备份。在使用 delcascadetrig 时,从 titles 中删除title_id PS2091 将删除 titleauthor 中相应的一行或多行。要保存数据,可在 titleauthor 上创建 DELETE 触发器,该触发器的作用是将被删除的数据保存到另一个单独创建的名为 del_save 表中。例如:

CREATE TRIGGER savedel
  ON titleauthor
FOR DELETE
AS
  INSERT del_save
  SELECT * FROM deleted

不推荐按依赖于顺序的序列使用嵌套触发器。应使用单独的触发器层叠数据修改。



说明  由于触发器在事务中执行,如果在一系列嵌套触发器的任意层中发生错误,则整个事务都将取消,且所有的数据修改都将回滚。在触发器中包含 PRINT 语句,用以确定错误发生的位置。


递归触发器
触发器不会以递归方式自行调用,除非设置了 RECURSIVE_TRIGGERS 数据库选项。有两种不同的递归方式:

直接递归
即触发器激发并执行一个操作,而该操作又使同一个触发器再次激发。例如,一应用程序更新了表 T3,从而引发触发器 Trig3。Trig3 再次更新表 T3,使触发器 Trig3 再次被引发。

间接递归
即触发器激发并执行一个操作,而该操作又使另一个表中的某个触发器激发。第二个触发器使原始表得到更新,从而再次引发第一个触发器。例如,一应用程序更新了表 T1,并引发触发器 Trig1。Trig1 更新表 T2,从而使触发器 Trig2 被引发。Trig2 转而更新表 T1,从而使 Trig1 再次被引发。

当将 RECURSIVE_TRIGGERS 数据库选项设置为 OFF 时,仅防止直接递归。若要也禁用间接递归,请将 nested triggers 服务器选项设置为 0。

示例
A. 使用递归触发器解决自引用关系
递归触发器的一种用法是用于带有自引用关系的表(亦称为传递闭包)。例如,表 emp_mgr 定义了:

一个公司的雇员 (emp)。


每个雇员的经理 (mgr)。


组织树中向每个经理汇报的雇员总数 (NoOfReports)。
递归 UPDATE 触发器在插入新雇员记录的情况下可以使 NoOfReports 列保持最新。INSERT 触发器更新经理记录的 NoOfReports 列,而该操作递归更新管理层向上其它记录的 NoOfReports 列。

USE pubs
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE pubs
  SET RECURSIVE_TRIGGERS ON
GO
CREATE TABLE emp_mgr (
  emp char(30) PRIMARY KEY,
   mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),
   NoOfReports int DEFAULT 0
)
GO
CREATE TRIGGER emp_mgrins ON emp_mgr
FOR INSERT
AS
DECLARE @e char(30), @m char(30)
DECLARE c1 CURSOR FOR
  SELECT emp_mgr.emp
  FROM   emp_mgr, inserted
  WHERE emp_mgr.emp = inserted.mgr

OPEN c1
FETCH NEXT FROM c1 INTO @e
WHILE @@fetch_status = 0
BEGIN
  UPDATE emp_mgr
  SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Add 1 for newly
  WHERE emp_mgr.emp = @e                            -- added employee.

  FETCH NEXT FROM c1 INTO @e
END
CLOSE c1
DEALLOCATE c1
GO
-- This recursive UPDATE trigger works assuming:
--   1. Only singleton updates on emp_mgr.
--   2. No inserts in the middle of the org tree.
CREATE TRIGGER emp_mgrupd ON emp_mgr FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
  UPDATE emp_mgr
  SET emp_mgr.NoOfReports = emp_mgr.NoOfReports + 1 -- Increment mgr's
  FROM inserted                            -- (no. of reports) by
  WHERE emp_mgr.emp = inserted.mgr         -- 1 for the new report.

  UPDATE emp_mgr
  SET emp_mgr.NoOfReports = emp_mgr.NoOfReports - 1 -- Decrement mgr's
  FROM deleted                             -- (no. of reports) by 1
  WHERE emp_mgr.emp = deleted.mgr          -- for the new report.
END
GO
-- Insert some test data rows.
INSERT emp_mgr(emp, mgr) VALUES ('Harry', NULL)
INSERT emp_mgr(emp, mgr) VALUES ('Alice', 'Harry')
INSERT emp_mgr(emp, mgr) VALUES ('Paul', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Joe', 'Alice')
INSERT emp_mgr(emp, mgr) VALUES ('Dave', 'Joe')
GO
SELECT * FROM emp_mgr
GO
-- Change Dave's manager from Joe to Harry
UPDATE emp_mgr SET mgr = 'Harry'
WHERE emp = 'Dave'
GO
SELECT * FROM emp_mgr
GO

以下是更新前的结果:

emp                            mgr                           NoOfReports
------------------------------ ----------------------------- -----------
Alice                          Harry                          2
Dave                           Joe                            0
Harry                          NULL                           1
Joe                            Alice                          1
Paul                           Alice                          0

以下为更新后的结果:

emp                            mgr                           NoOfReports
------------------------------ ----------------------------- -----------
Alice                          Harry                          2
Dave                           Harry                          0
Harry                          NULL                           2
Joe                            Alice                          0
Paul                           Alice                          0

SQL: PIVOT的使用

{ Posted on 星期五, 十一月 20, 2009 by Kaiser.XKw }
有個主管要求一個交叉統計表, 來統計他們部門單據的簽核狀況, 以前常用case... when... then .... 這次用PIVOT,發現PIVOT的使用比case... when... then .... 清晰多了



試寫了一下關鍵部分的代碼,記錄下來, 其他的讓小朋友接手處理

範例一:
注意下面的代碼必須使用 YEAR(OrderDate) 的別名 vYear 不能直接使用 YEAR(OrderDate), 應該 YEAR(OrderDate)的值為數值, 如2002,2003等, 程式無法正確進行如果使用別名, 程式會將對應的值處理為 [2002],[2003]等, 程式即可正確進行

SELECT VendorID, [2001],[2002],[2003],[2004],[2005]
FROM
( SELECT VendorID, PurchaseOrderID, YEAR(OrderDate) as vYear
FROM Purchasing.PurchaseOrderHeader ) p
PIVOT
( count(PurchaseOrderID)
FOR vYear IN
( [2001],[2002],[2003],[2004],[2005] )
) AS dd
go

圖片中的代碼:

DECLARE @signYear Varchar(4)
DECLARE @signUser Varchar(12)
DECLARE @sql nvarchar(4000)
DECLARE @sqlMonth nvarchar(4000)

SET @signYear = '2009'
SET @signUser = '1578'

SET @sqlMonth= '[' + @signYear + '07],[' + @signYear + '08],[' + @signYear + '09]'
set @sql ='SELECT sign_user, rpt_id, ' + @sqlMonth
set @sql =@sql + ' FROM '
set @sql =@sql + ' (SELECT sign_user, rpt_id, vou_no, left(upd_date,6) AS SignMonth '
set @sql =@sql + ' FROM w_flowsignhist WHERE (sign_user = ' + @signUser + ')) p '
set @sql =@sql + ' PIVOT (count(vou_no) FOR SignMonth IN '
set @sql =@sql + ' (' + @sqlMonth + ')) as pvt'

--PRINT @sql
EXEC sp_executesql @sql

资料库中的一些统计SQL

{ Posted on 星期四, 十一月 19, 2009 by Kaiser.XKw }
资料库中的Trigger统计
select a.name 'Table', i.name 'Insert Trigger', u.name 'Update Trigger', d.name 'Delete Trigger'
from sysObjects a LEFT OUTER JOIN sysObjects i
ON a.instrig = i.id
LEFT OUTER JOIN sysObjects u
ON a.updtrig = u.id
LEFT OUTER JOIN sysObjects d
ON a.deltrig = d.id
where ((a.deltrig>0 or a.instrig>0 or a.updtrig>0) and a.type != 'TR')

资料库中的columns统计
SELECT DISTINCT LEFT(a.name, 30) AS fieldname, left(c.name,20) AS datatype, a.length AS size, b.name AS tablename
FROM syscolumns a INNER JOIN
systypes c ON a.xtype = c.xusertype INNER JOIN
sysobjects b ON a.id = b.id
WHERE (b.xtype = 'U') AND (b.name <> 'dtproperties')
ORDER BY size desc, tablename, fieldname

SQL: 今天星期几

{ Posted on 星期四, 十一月 19, 2009 by Kaiser.XKw }
SELECT CASE(datepart(dw, getdate())+ @@datefirst)%7 WHEN 2 THEN ''Monday'' WHEN 3 THEN ''Tuesday'' WHEN 4 THEN ''Wednesday'' WHEN 5 THEN ''Thursday'' WHEN 6 THEN ''Friday'' WHEN 0 THEN ''Saturday'' WHEN 1 THEN ''Sunday'' ELSE NULL END

SQL: 日期格式化FormatDate.sql

{ Posted on 星期四, 十一月 19, 2009 by Kaiser.XKw }
FormatDate
FormatDate.sql

This script will create a UDF called FormatDate that allows you to format a date by simply supplying 
a format such as 'mm/dd/yyyy', 'yyyymmdd', 'dddd, mmmm d1, yyyy', etc. The comment header
 provides additional examples of how to use the function. The script has been tested on SQL Server 2000. 

Author: Rusty Hansen

Author: Rusty Hansen 8-21-2001
Description: Formats a date to a specific format.
Parameters:
     @dDate = A value or field of datatype datetime or a value or field that can be explicitly converted to 
              a datetime datatype.
     @sFormat varchar(40) = Format codes using the characters described below
       
     MMMM or DDDD = the full name for the day or month
     MMM or DDD = the first 3 letters of the month or day
     MM or DD = the two digit code signifying the month or day
     M1 or D1 = the month or day value without a preceding zero
     YYYY = a four digit year
     YY = a two digit year
     
     All other characters will not be replaced such as / - . * # a b z x % and will show
     up in the date in the same relative position that they appear in the format
     parameter.
     
     Examples
     select dbo.FormatDate('9/21/2001','dddd, mmmm d1, yyyy') --> Friday, September 21, 2001
     select dbo.FormatDate('9/21/2001','mm/dd/yyyy') --> 09/21/2001
     select dbo.FormatDate('9/21/2001','mm-dd-yyyy') --> 09/21/2001
     select dbo.FormatDate('9/21/2001','yyyymmdd') --> 20010921
     select dbo.FormatDate('9/5/2001','m1/d1/yy') --> 9/5/01
     select dbo.FormatDate('9/21/2001','mmm-yyyy') --> Sep-2001

//////////////////////////////////////////////////////////////////////////////////
*/

create function [dbo].[FormatDate]
     (
     @dDate datetime          --Date value to be formatted
     ,@sFormat varchar(40)    --Format for date value
     )
returns varchar(40)
as
begin

     -- Insert the Month
     -- ~~~~~~~~~~~~~~~~
     set @sFormat = replace(@sFormat,'MMMM',datename(month,@dDate))
     set @sFormat = replace(@sFormat,'MMM',convert(char(3),datename(month,@dDate)))
     set @sFormat = replace(@sFormat,'MM',right(convert(char(4),@dDate,12),2))
     set @sFormat = replace(@sFormat,'M1',convert(varchar(2),convert(int,right(convert(char(4),@dDate,12),2))))

     -- Insert the Day
     -- ~~~~~~~~~~~~~~
     set @sFormat = replace(@sFormat,'DDDD',datename(weekday,@dDate))
     set @sFormat = replace(@sFormat,'DDD',convert(char(3),datename(weekday,@dDate)))
     set @sFormat = replace(@sFormat,'DD',right(convert(char(6),@dDate,12),2))
     set @sFormat = replace(@sFormat,'D1',convert(varchar(2),convert(int,right(convert(char(6),@dDate,12),2))))

     -- Insert the Year
     -- ~~~~~~~~~~~~~~~
     set @sFormat = replace(@sFormat,'YYYY',convert(char(4),@dDate,112))
     set @sFormat = replace(@sFormat,'YY',convert(char(2),@dDate,12))

     -- Return the function's value
     -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~  
     return @sFormat
end

SQL: 金额阿拉伯数字转换为中文的自定义函数

{ Posted on 星期四, 十一月 19, 2009 by Kaiser.XKw }
CREATE FUNCTION ChangeBigSmall
(@ChangeMoney money)  
RETURNS VarChar(100) AS  

BEGIN 
    Declare    @String1    char(20)
    Declare    @String2    char(30)
    Declare    @String4    Varchar(100)
    Declare @String3     Varchar(100)    --从原A值中取出的值
    Declare @i         int        --循环变量
    Declare @J         Int        --A的值乘以100的字符串长度
    Declare @Ch1         Varchar(100)    --数字的汉语读法
    Declare @Ch2         Varchar(100)    --数字位的汉字读法
    Declare @Zero         Int        --用来计算连续有几个零
    Declare    @Returnvalue    VarChar(100)

    Select  @Returnvalue = ''
    Select     @String1 = '零壹贰叁肆伍陆柒捌玖'
    Select    @String2 = '万仟佰拾亿仟佰拾万仟佰拾元角分'

    Select @String4 = Cast(@ChangeMoney*100 as int)    

    select @J=len(cast((@ChangeMoney*100) as int))

    Select @String2=Right(@String2,@J)

    Select    @i = 1 

    while    @i<= @j Begin

        Select @String3 = Substring(@String4,@i,1)

        if @String3<>'0' Begin

            Select     @Ch1 = Substring(@String1, Cast(@String3 as Int) + 1, 1)
            Select    @Ch2 = Substring(@String2, @i, 1)
            Select    @Zero = 0                    --表示本位不为零
        end
        else Begin
            If (@Zero = 0) Or (@i = @J - 9) Or (@i = @J - 5) Or (@i = @J - 1)
                        Select @Ch1 = '零' 
                    Else
                        Select @Ch1 = ''

                    Select @Zero = @Zero + 1             --表示本位为0
                        
            --如果转换的数值需要扩大,那么需改动以下表达式 I 的值。
            Select @Ch2 = ''

                If @i = @J - 10  Begin
                        Select @Ch2 = '亿'
                        Select @Zero = 0
            end
                    
            If @i = @J - 6 Begin
                        Select @Ch2 = '万'
                        Select @Zero = 0
            end
                    
            if @i = @J - 2 Begin
                        Select @Ch2 = '元'
                        Select @Zero = 0
            end
                    
            If @i = @J 
                        Select @Ch2 = '整'
                        
        end    

        Select @Returnvalue = @Returnvalue + @Ch1 + @Ch2

        select @i = @i+1
    end

    --最后将多余的零去掉
    If CharIndex('仟仟',@Returnvalue) <> 0 
            Select @Returnvalue = Replace(@Returnvalue, '仟仟', '仟')

    If CharIndex('佰佰',@Returnvalue) <> 0 
            Select @Returnvalue = Replace(@Returnvalue, '佰佰', '佰')

        If CharIndex('零元',@Returnvalue) <> 0 
            Select @Returnvalue = Replace(@Returnvalue, '零元', '元')
    
        If CharIndex('零万',@Returnvalue) <> 0 
            Select @Returnvalue = Replace(@Returnvalue, '零万', '万')
   
        If CharIndex('零亿',@Returnvalue) <> 0
            Select @Returnvalue = Replace(@Returnvalue, '零亿', '亿')
    
        If CharIndex('零整',@Returnvalue) <> 0 
            Select @Returnvalue = Replace(@Returnvalue, '零整', '整')
    
    If CharIndex('零佰',@Returnvalue) <> 0 
            Select @Returnvalue = Replace(@Returnvalue, '零佰', '零')

    If CharIndex('零仟',@Returnvalue) <> 0 
            Select @Returnvalue = Replace(@Returnvalue, '零仟', '零')

    If CharIndex('元元',@Returnvalue) <> 0 
            Select @Returnvalue = Replace(@Returnvalue, '元元', '元')

    return @Returnvalue

END

设定Oracle SQL Developer 支持SQLServer

{ Posted on 星期二, 十一月 17, 2009 by Kaiser.XKw }
今天下載了Oralce提供的開發工具SQL Developer2.1版,雖然不是很好用, 但總比沒有Oracle自帶的管理工具好用, 而且是綠色的, 只是發現號稱跨平台的東東,多資料庫管理的,竟然沒有設定SQLServer的選項. 查了下資料, 發現下載一個JDBC的Driver, 然後設定下SQLDeveloper就可以支持了
1.先到 jTDS JDBC Driver 網站下載 JDBC driver 現在最新版為 jtds-1.2.4-dist.zip,下載後解壓縮 jtds-1.2.4.jar 檔案並置於 [SQL Developer 安裝目錄]\jdbc\lib 目錄下。
2.然後進 SQL Developer 資料庫管理工具的 Tools 選單中選取 Preferences 設定 Third Party JDBC Drivers 項目,並點選 Add Entry 新增 JDBC driver 的 *.jar 檔。
3.接著選取剛剛 jar 檔
4.新增連線就可以看到我們想要的SQLServer資料庫的頁籤了,不過缺點是在設定 Microsoft SQL Server 時只能使用 TCP/IP 連線,無法使用 Named Pipe 設定連線。而且切換資料庫不太方便.
jTDS JDBC Driver 下載的網頁:

SQL: 一个月多少天

{ Posted on 星期一, 十一月 02, 2009 by Kaiser.XKw }
SQL: 一个月多少天
写一个Function取得月份的天数,参数:月份,年份
先得到下一个月的1日,然后减1天得到,需求月份的最后1天,利用Day函数即可得到月份的天数

create function GetMonths (@Month smallint, @Year int)
returns smallint
as
begin
return(DAY(dateadd(dd,-1,DATEADD(m,1,cast(@Year as varchar(4)) + ''-'' + cast
(@Month as varchar(2)) + ''-01''))))
end

select DatabaseName.dbo.GetMonths (2,2002)

[学习]字符串的格式化-Numeric Conversions

{ Posted on 星期三, 十月 07, 2009 by Kaiser.XKw }

 字符串-Numeric Conversions

Introduction

Continuing on with taming strings...  

 

Often when I am reporting data from SQL Server, I am concatenating information together to form more readable output. Many times this output will also include some numeric data that needs to be placed inside a string. I am not sure exactly why an integer is not implicitly converted to a string, but it doesn't work, so I have to use another solution.

 

The Problem

Numeric values (whether integer or float) are not implicitly converted to characters within a string concatenation statement. Instead, the following statement (using Northwind):

 

 

  select customerID + 5

   from customers

 

returns this:

 

Server: Msg 245, Level 16, State 1, Line 1

Syntax error converting the nvarchar value 'ALFKI' to a column of data type int.

 

And this code:

 

  select 5 + customerID

   from customers

 

still returns this:

 

Server: Msg 245, Level 16, State 1, Line 1

Syntax error converting the nvarchar value 'ALFKI' to a column of data type int.

 

Apparently the numeric data type takes some precendence over the character data types. In some searching of Books Online, I have not found any documentation of this.

In the past I have used CONVERT, and more recently CAST, to convert the numeric data into character data. However there are a few issues with this technique. Consider the following code:

 

 

select quantity,

  cast( quantity as char( 1)) 'Char_quantity'

 from [order details]

 

This results in something that looks like:

quantity Char_quantity

-------- -------------

12       *

10       *

5        5

9        9

 

Notice that a number of the results are not returned because the data type does not fit into the space allowed. So what can you do?

 

In the past, I have usually used code like the following:

 

 

select quantity,

       rtrim( cast( quantity as varchar( 50))) 'Char_quantity'

 from [order details]

 

This will ensure that I get all results, but still contains some issues.

 

Suppose that I have a series of decimals like the following:

 

 

select 4.35,

       12.423,

       234.34,

       6.3345

 

which will return:

----- ------- ------- -------

4.35  12.423  234.34  6.3345

 

Let us apply the previous formatting to this set of data and see the results.

 

 

select rtrim( cast( 4.35 as varchar( 50))),

       rtrim( cast( 12.423 as varchar( 50))),

       rtrim( cast( 234.34 as varchar( 50))),

       rtrim( cast( 6.3345 as varchar( 50)))

 

------------ -------------- ----------- ----------------

4.35         12.423         234.34      6.3345

 

I have shortened the result set, but this results in a wide result set in Query Analyzer, though the actual strings are the proper length.

 

But what if I need specific formatting? What if I need a specific length for formatting columns in a report? What if I am looking for xx number of decimals? It is easy to use SUBSTRING and SPACE to trim and then pad the columns, but this is cumbersome. For the decimals, then you run into another problem.

 

select rtrim( cast( round( 4.35, 2) as varchar( 50))),

       rtrim( cast( round( 12.423, 2) as varchar( 50))),

     rtrim( cast( round( 234.34, 2) as varchar( 50))),

     rtrim( cast( round( 6.3345, 2) as varchar( 50)))

 

------------ ------------ ---------- ----------

4.35         12.420       234.34     6.3300

 

In this code, the numbers are rounded to the proper number of decimals, but there are still the original number of characters in the converted strings. SUBSTRING presents a problem here unless I use CHARINDEX to find the decimal and then perform the proper operations. This gets cumbersome and I decided to search for an easier solution.

 

The Solution

I decided to search my handy-dandy Books Online (for those of you with young kids, you will get the joke. For the rest of you look here) in the string functions area and I found STR. This is a string function that is designed to convert numeric values to characters. Let us apply this to our sample data set.

 

 

select str( 4.35, 5, 2),

       str( 12.423, 5, 2),

       str( 234.34, 5, 2),

       str( 6.3345, 5, 2)

 

----- ----- ----- -----

 4.35 12.42 234.3  6.33

 

This almost appears to work, but there are still some issues. Notice that in column 3, the decimals are not set to 2 because the length of the string exceeds the total length. If we adjust the query as follows:

select ltrim( str( 4.35, 25, 2)),

       ltrim( str( 12.423, 25, 2)),

       ltrim( str( 234.34, 24, 2)),

       ltrim( str( 6.3345, 25, 2))

 

------------- ------------- ------------ --------------

4.35          12.42         234.34       6.33

 

Now I have strings with the proper number of decimals. Of course, if I want to get each string set to the same length and right justified, I still have some formatting to do, but I will stop here for now.

 

Conclusion

I hope that I have shed some light on a little used funciton and an alternative for converting numeric values into strings. No earth shattering technical knowledge in this article, but perhaps I will spark an idea or two in some of you.

 

As always, I welcome feedback and please rate this article below (and any you read on Swynk). It helps to motivate and assist us authors in writing better columns.

 

[学习]字符串的格式化-Using STUFF

{ Posted on 星期三, 十月 07, 2009 by Kaiser.XKw }
字符串-Using STUFF
Introduction
Continuing on with taming strings...

The first three parts of this series dealt with manipulating strings in response to some business issue, usually dealing with phone numbers or zip codes. This article continues in the same vein with another alternative for dealing with the formatting issues that occur with these types of data.

The Problem
Usually I receive data in a variety of formats and need to force it into a standard format. In past articles, I have used various string techniques to remove unnecessary data and reformat the data into a standard format. A lack of validation of input data, or no control over the data being input is the cause, but it happens. However, once I remove all extraneous formatting to standardize the data, I sometimes need to add additional formating to standardize the data for a front end application(s).

Suppose that I had scrubbed and cleansed all my phone number data from Part 1 of this series to look like the following:

phone
----------
6055552862
5615552700
9045555680
N/A
5805555371
2815558368
2545558430
3365552797
3365557233
5925553181x4951
96615551222x249
447930555271

Now I wish to format this data so that all phone numbers are stored in the following format:

(999) 999-9999

for US phone numbers. For this article, assume that I have some method of determining which rows are US phone numbers and which are not. This leaves me with the following rows

phone
----------
6055552862
5615552700
9045555680
5805555371
2815558368
2545558430
3365552797
3365557233
5925553181x4951
96615551222x249

that I want to convert to:

phone
----------
(605) 555-2862
(561) 555-2700
(904) 555-5680
(580) 555-5371
(281) 555-8368
(254) 555-8430
(336) 555-2797
(336) 555-7233
(592) 555-3181x4951
(966) 155-51222x249


The Solution
Once again, I decided to search my handy-dandy Books Online (for those of you with young kids, you will get the joke. For the rest of you look here) in the string functions area and I decided to use STUFF. This is a string function that allows you to delete characters from a string and insert a string value into another string with control over the positioning of the insertion. The difference from REPLACE is that this function uses a position in the string to make replacement rather than a pattern.

The format for STUFF is as follows:
STUFF ( character_expression 1, start , length , character_expression 2)
where the parameters are:


Character_expression 1 - The string expression in which to insert data
Start - The start position for the insertion
Length - The number of characters in character_expression 1 to delete.
Character_expression 2 - the string to insert into character_expression 1
Normally, I have used this function in the same manner as REPLACE to remove some data from a string and add different data. However, there is a trick to using this function to convert the phone number data above.

The trick is to not delete any characters. If I use the following code:

declare @d char( 14)
select @d = '6055552862'
select stuff( @d,1 ,1,'('), @d

I get
(055552862

which is not what I want. Instead, I want to change the length parameter to a 0, so no characters are deleted. In this case, an insertion occurs with no characters being deleted. The code now looks like:

declare @d char( 14)
select @d = '6055552862'
select stuff( @d,1 , 0,'('), @d

I get
(6055552862


By expanding this to include all the characters that I need, I can reformat the entire string in one (messy) SQL statement. Here is it:
declare @d char( 14)
select @d = '6055552862'
select stuff( stuff( stuff( stuff( @d,1 ,0, '('), 5, 0, ')'), 6, 0, ' '), 10, 0, '-')

which yeilds
(605) 555-2862

Conclusion
This statement could have easily been done with the SUBSTRING command and a few concatenations. I think the STUFF command is cleaner and easier to read. Once you get used to using it's syntax. Of course, to update the table of information, I would have to be sure that all rows were in the same format or use a WHERE clause to limit the update to those rows that match the criteria.

I hope and am sure this function will really spark some ideas in many of you. Hopefully I will come in handy for you in solving some problem.

As always, I welcome feedback and please rate this article below (and any you read on Swynk). It helps to motivate and assist us authors in writing better columns.

SQL 2005 上用Oralce 10g的客户端建立连接服务器出错的处理

{ Posted on 星期六, 九月 26, 2009 by Kaiser.XKw }
在SQL 2005 上用Oralce 9i建立连接服务器没有问题, 当改用Oralce 10g的客户端建立连接服务器时出现如上的错误. 经过查询相关资料,发现需要在 连接服务器>>提供者>>OraOledb.Oralce 上面属性必须设定允许inprocess

SQL邏輯查詢處理的步驟

{ Posted on 星期四, 九月 10, 2009 by Kaiser.XKw }

(8)SELECT  (9)DISTINCT  (11)TOP <select_list>

(1)FROM <left_table>

(3)<join_type>JOIN <right_table>

(2)ON <join_condition>

(4)WHERE <where_condition>

(5)GROUP BY <group_by_list>

(6)WITH {CUBE | ROLLUP}

(7)HAVING <having_condition>

(10)ORDER BY <order_by_list>

  

  1. 執行迪卡爾乘積 (交叉關聯)
  2. 應用ON篩選器(聯結條件)
    1. 在SQL中邏輯表達式的可能值包括 True, False, Unknown 三種, 稱為 三值邏輯
  1. 添加外部行(Outer Row)
    1. 左外部關聯(left outer join)把左表標記為保留表
    2. 右外部關聯(right outer join)把右表標記為保留表
    3. 完全外部關聯(full outer join)把兩個表都標記為保留表
  1. 應用Where 篩選器
  2. 分組(GROUP BY)
  3. 應用CUBE 或 ROLLUP選項
  4. 應用HAVING篩選器
  5. 處理SELECT列表
  6. 應用DISTINCT子句
  7. 應用ORDER BY子句
  8. 應用TOP選項