Some useful special functions in SQL Server

Several functions (Stored Procedures) that are useful but not commonly used (or with unclear details) are found during SQL server usage ):

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

Write it down for future query. Do not dare to exclusive, and share with the king. Useful and helpful, useless.

 

1> isnumeric (expression)

-- Return Value: 1 | 0, which indicates whether it is a number.

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

Example:

Select * From Tablename
Where Isnumeric (Columnname) <> 1 ;
Go

 

The preceding example uses isnumeric to return all non-numeric data rows.

 

2>Isdate (expression)

 

-- If expression is a valid value of date, time, or datetime, 1 is returned; otherwise, 0 is returned.

Example:

If Isdate ( ' 10:19:41. 177 ' ) = 1
Print ' Valid date '
Else
Print ' Invalid date '

 

The preceding 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 a specified expression;

--If this mode is not found in all valid text and character data types, zero is returned.

'Pattern': a wildcard string. Before and after a pattern, it must contain % characters (except when the first or last character is searched ). Expression: it is usually a string data type column in which you want to search for the specified mode.

Example:

Select Patindex ( ' % BB % ' , ' Aa_bb_cc_dd_aa_bb_cc_dd ' )
-- Return Value: 4

In the preceding example, the start position of the first 'bb' is returned.

In fact, the charindex function can also be used to implement the query in the preceding example, as shown below:

Select Charindex ( ' Bb ' , ' Aa_bb_cc_dd_aa_bb_cc_dd ' )
-- Return Value: 4

 

 

Differences between the patindex function and the charindex function: Select Patindex ( ' % [0-9] [A-Z] % ' , ' Aa_bb_9c_dd_aa_9f_cc_dd ' )
-- Return Value: 7
Select Charindex ( ' % [0-9] [A-Z] % ' , ' Aa_bb_9c_dd_aa_9f_cc_dd ' )
-- Return Value: 0

No?The patindex function can use wildcards, but the charindex function cannot. That is to say, the patindex function is more powerful!

 

 

4> newid ()

 

-- Create a unique value of the uniqueidentifier type. This function can always return a new guid number, which will never be repeated and is irregular.

Example:

Declare @ Myid Uniqueidentifier
Set @ Myid = Newid ()
Print ' @ Myid: ' + Convert ( Varchar ( 255 ), @ Myid )

--@ Myid: 0b939411-4827-485e-884b-5beb1699c133

 

5>Collate

 

-- A sub-statement can be applied to database definitions or column definitions to define sorting rules, or to string expressions to apply sorting rule conversion. 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 , ' Person ' );

select * from # temptalbe
order by _ name
collate latin1_general_cs_as_ks_ws ASC ;
go
/* display result:
_ id _ name
----------- ----------------------------
medium
2 countries
3 people
*/

Select * From# Temptalbe
Order By_ Name
Collate chinese_prc_cs_as_ks_wsASC;
Go
/*Display result:
_ Id _ name
-----------------------------------------
2 countries
3 persons
Medium
*/

 

Note:

You can execute the system function fn_helpcollations to retrieve the list of all valid sorting rule names for Windows and SQL Server sorting rules:

Select * From Fn_helpcollations () 6> sp_executesql Stored Procedure We recommend that you use the sp_executesql stored procedure instead of the execute statement when executing a string.

Because this stored procedure supports parameter replacement, sp_executesql has more functions than execute;

Since SQL Server is more likely to reuse the execution plan generated by sp_executesql, sp_executesql is more effective 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
/* Result:
ID
-----------
1234
*/ The preceding example demonstrates the concatenation of SQL statements. 7> checksum

-- Returns the validation value calculated based on a row or a group of expressions in the table. Checksum is used to generate a hash index.

Checksum (* | expression [,... n])
* All columns of the table are calculated. If any column is of a non-comparable data type, checksum returns an error.
Non-comparable data types include text, ntext, image, XML, and cursor, and SQL _variant of any of the above types as the base type.
Expression unless it is of any type other than the comparable data type.

Example:

-- Find the records in Table T1 and table t.
Select * From T1 Where Checksum ( * ) Not In ( Select Checksum ( * ) From T)

In the preceding example, the hash of a row in Table T1 is compared with that in Table T, that is, whether there are rows in the two tables is exactly the same.