A few useful special functions in SQL Server

During the use of SQL Server, several functions (stored procedures) that are useful, but less common (or less detailed) are found:

Isnumeric,isdate,patindex,newid,collate,sp_executesql,checksum

Write it down for future enquiries. Dare not to enjoy alone, with the king of the total. Useful and look, useless to skip over.

1> isnumeric (expression)

--return value 1 | 0 To determine whether it is a numeric type.

numeric types include (int, bigint, smallint, tinyint, numeric, money, smallmoney, float, decimal, real)

Example:

SELECT * FROM tablename
where IsNumeric (columnname) <> 1;
Go

The above example uses IsNumeric to return all non-numeric data rows.

2> isdate (expression)

--Returns 1 if expression is a valid date, time, or datetime value, otherwise 0.

Example:

If IsDate (' 2009-05-12 10:19:41.177 ') = 1
print ' Valid date '
Else
print ' Invalid Date '

The above example uses IsDate to test whether a string is a valid DateTime.

3> patindex ('%pattern% ', expression)

-Returns the starting position of the first occurrence of a pattern in the specified expression;

--Returns zero if the pattern is not found in all valid text and character data types.

' pattern ': a wildcard character string. There must be a% character before and after the pattern (except when searching for the first or last character). expression: Typically the String data type column in which you want to search for a specified pattern.

Example:

Select Patindex ('%bb% ', ' aa_bb_cc_dd_aa_bb_cc_dd ')
--Return: 4

The above example returns the start position of the first ' BB '.

In fact, using the CHARINDEX function can also implement the above example query, as follows:

Select CHARINDEX (' BB ', ' aa_bb_cc_dd_aa_bb_cc_dd ')
--Return: 4

The difference between the PATINDEX function and the CHARINDEX function: Select Patindex ('%[0-9][a-z]% ', ' aa_bb_9c_dd_aa_9f_cc_dd ')
--Return: 7
Select CHARINDEX ('%[0-9][a-z]% ', ' aa_bb_9c_dd_aa_9f_cc_dd ')
--Return: 0

Do you see that? the PATINDEX function can use wildcards, while the CHARINDEX function cannot. In other words: Patindex function is more powerful!

4> newid ()

--Creates a unique value for the uniqueidentifier type. This function always returns a new GUID number, which is never repeated and is not regular.

Example:

DECLARE @myid uniqueidentifier
Set @myid = NEWID ()
The value of print ' @myid is: ' + CONVERT (varchar (255), @myid)

--The value of @myid is: 0b939411-4827-485e-884b-5beb1699cfee

5> Collate

--a clause that can be applied to a database definition or column definition to define a collation, or applied to a string expression to apply a collation transformation. The COLLATE clause can only be applied to char, varchar, text, nchar, nvarchar, and ntext data types.

Example:

drop table #tempTalbe
Go
CREATE TABLE #tempTalbe
(
_id int,
_name varchar (30)
)
Go
INSERT into #tempTalbe values (1, ' Medium ');
INSERT into #tempTalbe values (2, ' country ');
INSERT into #tempTalbe values (3, ' people ');

SELECT * FROM #tempTalbe
ORDER BY _name
COLLATE LATIN1_GENERAL_CS_AS_KS_WS ASC;
Go
/* Show Results:
_id _name
----------- ------------------------------
1 in
2 countries
3 people
*/

SELECT * FROM #tempTalbe
ORDER BY _name
COLLATE CHINESE_PRC_CS_AS_KS_WS ASC;
Go
/* Show Results:
_id _name
----------- ------------------------------
2 countries
3 people
1 in
*/

Note:

You can perform a system function fn_helpcollations to retrieve a list of all valid collation names for Windows collations and SQL Server collations:

SELECT * from Fn_helpcollations () 6> sp_executesql stored Procedure It is recommended that you use the sp_executesql stored procedure instead of the EXECUTE statement when you execute the string.

Because this stored procedure supports parameter substitution, sp_executesql has more functionality than execute;

Because SQL Server is more likely to reuse the execution plan generated by sp_executesql, sp_executesql is more efficient than execute.

Example:

CREATE TABLE #tb_suer (ID int)
Go
INSERT into #tb_suer values (1234)
Go

declare @tbname nvarchar (20)
declare @sql nvarchar (500)
Set @tbname = ' #tb_suer '
Set @sql = ' select * from ' + @tbname
Execute sp_executesql @sql
/* Results:
Id
-----------
1234
*/The above example shows the concatenation of SQL statements. 7> Checksum

--Returns the checksum value computed from a row or set of expressions in a table. The checksum is used to generate a hash index.

Checksum (* | expression [,... n])
* Specifies that all columns of the table are evaluated. If either column is a non-comparable data type, checksum returns an error.
Non-comparable data types have text, ntext, image, XML, and cursor, and also include sql_variant with either of these types as the base type.
expression except for any type that is beyond the data type.

Example:

--Find the records in the T1 that have not been found in the T table.
SELECT * from t1 where checksum (*) isn't in (select checksum (*) from T)

The above example, is equal to the T1 table of a row of data hash and the T table row of data hash, that is, two tables are not exactly equivalent.

http://blog.csdn.net/qq_25627143/article/details/50646405

A few useful special functions in SQL Server