sql自定义函数

原创 2007年10月09日 12:50:00


CREATE      function TL_getMode(@modeid nvarchar(100))
returns nvarchar(500)
as
begin
 declare @re nvarchar(500)
 set @re=''
 if(@modeid!='')
  begin
   select @re=@re+ModeName+'  ' from TL_Mode where charindex(','+cast(id as nvarchar(20))+',',','+@modeid+',')>0
   if(@re!='')
    begin
     if(RIGHT(@re,1)=',')
     select @re=left(@re, len(@re)-1) 
    end
   else
   set @re=''
  end
 else
  set @re=''
  return @re
end

 

  

SQL Server 2000 函数使用---CAST CONVERT
将某种数据类型的表达式显式转换为另一种数据类型。CAST CONVERT 提供相似的功能。
语法
使用 CAST
CAST ( expression AS data_type )
使用 CONVERT
CONVERT (data_type[(length)], expression [, style])
参数
expression
是任何有效的 Microsoft® SQL Server™ 表达式。
data_type
目标系统所提供的数据类型,包括 bigint sql_variant。不能使用用户定义的数据类型
length
ncharnvarcharcharvarcharbinary varbinary 数据类型的可选参数。
style
日期格式样式,借以将 datetime smalldatetime 数据转换为字符数据(ncharnvarcharcharvarcharnchar nvarchar 数据类型);或者字符串格式样式,借以将 floatrealmoney smallmoney 数据转换为字符数据(ncharnvarcharcharvarcharnchar nvarchar 数据类型)。
SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。
在表中,左侧的两列表示将 datetime smalldatetime 转换为字符数据的 style 值。给 style 值加 100,可获得包括世纪数位的四位年份 (yyyy)

不带世纪数位 (yy)
带世纪数位 (yyyy)

标准

输入/输出**
-
0 100 (*)
默认值
mon dd yyyy hh:miAM(或 PM
1
101
美国
mm/dd/yyyy
2
102
ANSI
yy.mm.dd
3
103
英国/法国
dd/mm/yy
4
104
德国
dd.mm.yy
5
105
意大利
dd-mm-yy
6
106
-
dd mon yy
7
107
-
mon dd, yy
8
108
-
hh:mm:ss
-
9 109 (*)
默认值 + 毫秒
mon dd yyyy hh:mi:ss:mmmAM(或 PM
10
110
美国
mm-dd-yy
11
111
日本
yy/mm/dd
12
112
ISO
yymmdd
-
13 113 (*)
欧洲默认值 + 毫秒
dd mon yyyy hh:mm:ss:mmm(24h)
14
114
-
hh:mi:ss:mmm(24h)
-
20 120 (*)
ODBC 规范
yyyy-mm-dd hh:mm:ss[.fff]
-
21 121 (*)
ODBC 规范(带毫秒)
yyyy-mm-dd hh:mm:ss[.fff]
-
126(***)
ISO8601
yyyy-mm-dd Thh:mm:ss:mmm(不含空格)
-
130*
科威特
dd mon yyyy hh:mi:ss:mmmAM
-
131*
科威特
dd/mm/yy hh:mi:ss:mmmAM

 

*    默认值(style 0 1009 10913 11320 12021 121)始终返回世纪数位 (yyyy)

 

SQL Server 2005 Books Online (September 2007)

CAST and CONVERT (Transact-SQL)

Updated: 17 July 2006

Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Transact-SQL Syntax Conventions

Syntax

Syntax for CAST: CAST ( expression AS data_type [ (length ) ]) Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments

expression

Is any valid expression.

data_type

Is the target system-supplied data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used. For more information about available data types, see Data Types (Transact-SQL).

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters.

style

Is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or to convert character data of known date or time formats to datetime or smalldatetime data; or the string format used to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). When style is NULL, the result returned is also NULL.

Note:

The styles listed in this topic are supported by SQL Server in the described combinations with CONVERT target data types. All other styles and combinations are not supported. Do not use any unsupported styles. Using an unsupported style or an unsupported combination of style and target data type might return an error or unreliable results. These results are not guaranteed across releases of SQL Server.

SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

In the following table, the two columns on the left represent the style values for converting datetime or smalldatetime data to character data. Add 100 to a style value to obtain a four-place year that includes the century (yyyy).

Without century (yy) (1)

With century (yyyy)

Standard

Input/Output (3)

-

0 or 100 (1, 2)

Default

mon dd yyyy hh:miAM (or PM)

1

101

U.S.

mm/dd/yyyy

2

102

ANSI

yy.mm.dd

3

103

British/French

dd/mm/yy

4

104

German

dd.mm.yy

5

105

Italian

dd-mm-yy

6

106 (1)

-

dd mon yy

7

107 (1)

-

Mon dd, yy

8

108

-

hh:mi:ss

-

9 or 109 (1, 2)

Default + milliseconds

mon dd yyyy hh:mi:ss:mmmAM (or PM)

10

110

USA

mm-dd-yy

11

111

JAPAN

yy/mm/dd

12

112

ISO

yymmdd

-

13 or 113 (1, 2)

Europe default + milliseconds

dd mon yyyy hh:mi:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 or 120 (2)

ODBC canonical

yyyy-mm-dd hh:mi:ss(24h)

-

21 or 121 (2)

ODBC canonical (with milliseconds)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126 (4)

ISO8601

yyyy-mm-ddThh:mi:ss.mmm (no spaces)

 

127(6, 7)

ISO8601 with time zone Z.

yyyy-mm-ddThh:mi:ss.mmmZ

(no spaces)

-

130 (1, 2)

Hijri (5)

dd mon yyyy hh:mi:ss:mmmAM

-

131 (2)

Hijri (5)

dd/mm/yy hh:mi:ss:mmmAM

1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

2 The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

3 Input when you convert to datetime; output when you convert to character data.

4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.

5 Hijri is a calendar system with several variations. SQL Server 2005 uses the Kuwaiti algorithm.

Important:

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.

6 Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

7The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.

The following table shows the values for style that can be used for converting float or real to character data.

Value

Output

0 (default)

A maximum of 6 digits. Use in scientific notation, when appropriate.

1

Always 8 digits. Always use in scientific notation.

2

Always 16 digits. Always use in scientific notation.

 

Note:

If style 126 is specified for conversion from float and real the output is equivalent to the style value of 2 and 1, respectively.

The following table shows the values for style that can be used for converting money or smallmoney to character data.

Value

Output

0 (default)

No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1

Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2

No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

 

Note:

If style 126 is specified for conversion from money, or smallmoney to character data, the output is equivalent to the style value of 2.

The following table shows the values for style that can be used for converting string input to xml data.

Value

Output

0 (default)

Use default parsing behavior that discards insignificant white space and does not allow for an internal DTD subset.

Note:

When you convert to the xml data type, SQL Server 2005 insignificant white space is handled differently than in XML 1.0. For more information, see Generating XML Instances.

1

Preserve insignificant white space. This style setting sets the default xml:space handling to behave the same as if xml:space="preserve" has been specified instead.

2

Enable limited internal DTD subset processing.

If enabled, the server can use the following information that is provided in an internal DTD subset to perform nonvalidating parse operations.

·                       Defaults for attributes are applied.

·                       Internal entity references are resolved and expanded.

·                       The DTD content model will be checked for syntactical correctness.

The parser will ignore external DTD subsets. It also does not evaluate the XML declaration to see whether the standalone attribute is set yes or no, but instead parses the XML instance as if it is a stand-alone document.

3

Preserve insignificant white space and enable limited internal DTD subset processing.

Return Types

Returns the same value as data_type.

Remarks

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server 2005 system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

Note:

Because Unicode data always uses an even number of bytes, use caution when you convert binary or varbinary to or from Unicode supported data types. For example, the following conversion does not return a hexadecimal value of 41; it returns 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Large-value Data Types

Large-value data types exhibit the same implicit and explicit conversion behavior as their smaller counterparts, specifically the varchar, nvarchar and varbinary data types. However, you should consider the following guidelines:

  • Conversion from image to varbinary(max) and vice-versa is an implicit conversion, and so are conversions between text and varchar(max), and ntext and nvarchar(max).
  • Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation will occur if the large value is too big for the specified length of the smaller data type.
  • Conversion from varchar, nvarchar, or varbinary to their corresponding large-value data types is performed implicitly.
  • Conversion from the sql_variant data type to the large-value data types is an explicit conversion.
  • Large-value data types cannot be converted to the sql_variant data type.

For information about converting Microsoft .NET Framework common language runtime (CLR) user-defined types, see Performing Operations on User-defined Types. For more information about converting from the xml data type, see Generating XML Instances.

xml Data Type

When you explicitly or implicitly cast the xml data type to a string or binary data type, the content of the xml data type is serialized based on a set of rules. For information about these rules, see Serialization of XML Data. For information on how to cast from XML to a CLR user-defined type, see Performing Operations on User-defined Types. For information about converting from other data types to the xml data type, see Generating XML Instances.

text and image Data Types

Automatic data type conversion is not supported for the text and image data types. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000 bytes. If you try an incorrect conversion such as trying to convert a character expression that includes letters to an int, SQL Server returns an error message.

Output Collation

When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default. For more information, see Collation Precedence (Transact-SQL).

To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

Truncating and Rounding Results

When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

From data type

To data type

Result

int, smallint, or tinyint

char

*

 

varchar

*

 

nchar

E

 

nvarchar

E

money, smallmoney, numeric, decimal, float, or real

char

E

 

varchar

E

 

nchar

E

 

nvarchar

E

* = Result length too short to display. E = Error returned because result length is too short to display.

SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from version to version. The following example shows such a roundtrip conversion:

Copy Code

DECLARE @myval decimal (5, 2) SET @myval = 193.57 SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5)) -- Or, using CONVERT SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

Note:

Do not try to construct binary values and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between versions of SQL Server.

The following example shows a resulting expression that is too small to display.

Copy Code

USE AdventureWorks; GO SELECT c.FirstName, c.LastName, SUBSTRING(c.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave' FROM HumanResources.Employee e JOIN Person.Contact c ON e.EmployeeID = c. ContactID WHERE NOT EmployeeID >5

Here is the result set.

Copy Code

FirstName LastName Title Sick Leave --------- --------- ------------------- ----------- Gustavo Achong Mr. * Catherine Abel Ms. * Kim Abercrombie Ms. * Humberto Acevedo Sr. * Pilar Ackerman Sra. * (5 row(s) affected)

When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded. The following table shows the behavior.

From

To

Behavior

numeric

numeric

Round

numeric

int

Truncate

numeric

money

Round

money

int

Round

money

numeric

Round

float

int

Truncate

float

numeric

Round

float

datetime

Round

datetime

int

Round

For example, the result of the following conversion is 10:

SELECT CAST(10.6496 AS int)

When you convert data types in which the target data type has fewer decimal places than the source data type, the value is rounded. For example, the result of the following conversion is $10.3497:

SELECT CAST(10.3496847 AS money)

SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

Converting Binary String Data

When binary or varbinary data is converted to character data and an odd number of values is specified following the x, SQL Server adds a 0 (zero) after the x to make an even number of values.

Binary data is made up of the characters from 0 through 9 and from A through F, or from a through f, in groups of two characters each. Binary strings must be preceded by 0x. For example, to input FF, type 0xFF. The maximum value is a binary value of 8000 bytes, each of which is FF. The binary data types are not for hexadecimal data but instead for bit patterns. Conversions and calculations of hexadecimal numbers stored as binary data can be unreliable.

When you specify the length of a binary data type, every two characters count as one. A length of 10 signifies that 10 two-character groupings will be entered.

Empty binary strings are represented by 0x and can be stored as binary data.

Examples

A. Using both CAST and CONVERT

Each example retrieves the name of the product for those products that have a 3 in the first digit of their list price and converts their ListPrice to int.

Copy Code

-- Use CAST USE AdventureWorks; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(ListPrice AS int) LIKE '3%'; GO -- Use CONVERT. USE AdventureWorks; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CONVERT(int, ListPrice) LIKE '3%'; GO

B. Using CAST with arithmetic operators

The following example calculates a single column computation (Computed) by dividing the total year-to-date sales (SalesYTD) by the commission percentage (CommissionPCT). This result is converted to an int data type after being rounded to the nearest whole number.

Copy Code

USE AdventureWorks; GO SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed' FROM Sales.SalesPerson WHERE CommissionPCT != 0; GO

Here is the result set.

Copy Code

Computed ------ 379753754 346698349 257144242 176493899 281101272 0 301872549 212623750 298948202 250784119 239246890 101664220 124511336 97688107 (14 row(s) affected)

C. Using CAST to concatenate

The following example concatenates noncharacter, nonbinary expressions by using CAST.

Copy Code

USE AdventureWorks; GO SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice FROM Production.Product WHERE ListPrice BETWEEN 350.00 AND 400.00; GO

Here is the result set.

Copy Code

ListPrice ------------------ The list price is 357.06 The list price is 364.09 The list price is 364.09 The list price is 364.09 The list price is 364.09 (5 row(s) affected)

D. Using CAST to produce more readable text

The following example uses CAST in the select list to convert the Name column to a char(10) column.

Copy Code

USE AdventureWorks; GO SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID WHERE Name LIKE 'Long-Sleeve Logo Jersey, M'; GO

Here is the result set.

Copy Code

Name UnitPrice ---------- --------------------- Long-Sleev 31.2437 Long-Sleev 32.4935 Long-Sleev 49.99 (3 row(s) affected)

E. Using CAST with the LIKE clause

The following example converts the money column SalesYTD to an int and then to a char(20) column so that it can be used with the LIKE clause.

Copy Code

USE AdventureWorks; GO SELECT p.FirstName, p.LastName, s.SalesYTD, s.SalesPersonID FROM Person.Contact p JOIN Sales.SalesPerson s ON p.ContactID = s.SalesPersonID WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%'; GO

Here is the result set.

Copy Code

FirstName LastName SalesYTD SalesPersonID ---------------- ------------------- ---------------- ------------- Carol Elliott 2811012.7151 279 Julie Estes 219088.8836 288 Janeth Esteves 2241204.0424 289 (3 row(s) affected)

F. Using CONVERT or CAST with typed XML

The following are several examples that show using CONVERT to convert to typed XML by using the xml data type.

This example converts a string with white space, text and markup into typed XML and removes all insignificant white space (boundary white space between nodes):

Copy Code

CONVERT(XML, '<root><child/></root>')

This example converts a similar string with white space, text and markup into typed XML and preserves insignificant white space (boundary white space between nodes):

Copy Code

CONVERT(XML, '<root>          <child/>         </root>', 1)

This example casts a string with white space, text, and markup into typed XML:

Copy Code

CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML)

For more examples, see Generating XML Instances.

G. Using CAST and CONVERT with datetime data

The following example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT display the date and time in the ISO 8901 format.

Copy Code

SELECT GETDATE() AS UnconvertedDateTime, CAST(GETDATE() AS nvarchar(30)) AS UsingCast, CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ; GO

Here is the result set.

UnconvertedDateTime     UsingCast                      UsingConvertTo_ISO8601

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

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM            2006-04-18T09:58:04.570

(1 row(s) affected)

The following example is approximately the opposite of the previous example. The example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type.

Copy Code

SELECT '2006-04-04T15:50:59.997' AS UnconvertedText, CAST('2006-04-04T15:50:59.997' AS datetime) AS UsingCast, CONVERT(datetime, '2006-04-04T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ; GO

Here is the result set.

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

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

2006-04-04T15:50:59.997 2006-04-04 15:50:59.997 2006-04-04 15:50:59.997

(1 row(s) affected)

 

 

SQL Server 自定义函数

标量值函数 如果RETUR NS 子句指定一种标量数据类型,则函数为标量值函数 如果RETURNS 子句指定 TABLE,则函数为表值函数 如果RETURNS 子句指定的TABLE 不附带列,则该函数...
  • QingHeShiJiYuan
  • QingHeShiJiYuan
  • 2016年01月06日 15:45
  • 2056

Microsoft SQL Server 自定义函数整理大全

SQL SERVER中是允许有自定义函数,Microsoft SQL Server 并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上,而是允许用户创建自己的用户定义函数。 函数...
  • ycl295644
  • ycl295644
  • 2016年02月29日 11:02
  • 2212

SQL自定义函数function

用户定义自定义函数像内置函数一样返回标量值,也可以将结果集用表格变量返回。 sql函数必须有返回值。标量函数:返回一个标量值。 表格值函数{内联表格值函数、多表格值函数}:返回行集(即返回多个值)...
  • qq_23833037
  • qq_23833037
  • 2016年11月15日 14:37
  • 6139

SQL自定义函数解密

  • 2008年04月22日 20:56
  • 7KB
  • 下载

sql server 自定义函数

  • 2012年08月27日 09:17
  • 51KB
  • 下载

Microsoft SQL Server 自定义函数整理大全

  • 2015年11月11日 15:41
  • 334KB
  • 下载

sql2000的自定义函数,传入汉字返回汉字拼音

  • 2009年01月04日 11:11
  • 1KB
  • 下载

在SQL2000中,自定义函数中使用getdate()

  • 2008年10月12日 10:54
  • 432B
  • 下载

SQL自定义函数讲解

  • 2011年05月10日 00:14
  • 190KB
  • 下载

sql server 自定义格式化6位数字字符的函数

  • 2010年06月25日 13:58
  • 706B
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:sql自定义函数
举报原因:
原因补充:

(最多只允许输入30个字)