Shulou(Shulou.com)05/31 Report--

This article mainly explains the "practical SQL Server script function methods", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "what are the practical SQL Server script function methods" bar!

1. The string specifies that the character is divided into list

The string specifies that the character is divided into list CREATE FUNCTION [dbo]. [splitl] (@ String VARCHAR (MAX), @ Delimiter VARCHAR (MAX)) RETURNS @ temptable TABLE (items VARCHAR (MAX)) AS BEGIN DECLARE @ idx INT=1 DECLARE @ slice VARCHAR (MAX) IF LEN (@ String)

< 1 OR LEN(ISNULL(@String,'')) = 0 RETURN WHILE @idx != 0 BEGIN SET @idx = CHARINDEX(@Delimiter,@String) IF @idx != 0 SET @slice = LEFT(@String,@idx - 1) ELSE SET @slice = @String IF LEN(@slice) >

0 INSERT INTO @ temptable (items) VALUES (@ slice) SET @ String = RIGHT (@ String, LEN (@ String)-@ idx) IF LEN (@ String) = 0 BREAK END RETURN END GO-- call method SELECT * FROM dbo.splitl ('aaa | bbb | ccc',' |')

2. Remove the zero at the end of the number

-- numeric removal of 0 CREATE function [dbo]. [ClearZero] (@ inValue varchar (50)) returns varchar (50) as begin declare @ returnValue varchar (20) if (@ inValue='') set @ returnValue=''-empty else if (charindex ('., @ inValue) ='0') set @ returnValue=@inValue-for else if without decimal point (substring (reverse (@ inValue), patindex ('% [^ 0]%', reverse (@ inValue)) 1) ='.) Set @ returnValue = left (@ inValue,len (@ inValue)-patindex ('% [^ 0]%', reverse (@ inValue))-- for else set @ returnValue = left (@ inValue,len (@ inValue)-patindex ('% [^ 0]%.%) for all zeros after the decimal point.%' Reverse (@ inValue) + 1)-- in any other case return @ returnValue end-- call sample SELECT dbo.ClearZero (258.250300)

3. Create tables, views, functions and stored procedures to determine whether they exist.

/ * determine whether the function / method exists. If so, delete the function / method * / IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'Func_Name') DROP FUNCTION Func_Name GO-create function / method CREATE FUNCTION Func_Name (@ an INT) RETURN INT AS BEGIN-- coding END GO / * determine whether the stored procedure exists, and if so delete the stored procedure * / IF EXISTS (OBJECT_NAME ('Proc_Name','P') IS NOT NULL DROP PROC Proc_Name) GO-create a stored procedure CREATE PROC Proc_Name AS SELECT * FROM Table_Name GO / * to determine whether the data table exists, and if so delete the data table * / IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'Table_Name') DROP VIEW Table_Name GO-create data table CREATE TABLE Table_Name (Id INT PRIMARY KEY NOT NULL) / * to determine whether the view exists, and if so delete the view * / IF EXISTS (SELECT * FROM sys.views WHERE name = 'View_Name') DROP VIEW View_Name GO-create the view CREATE VIEW View_Name AS SELECT SELECT * FROM table_name GO

4. Convert the amount to uppercase

/ * description: digital amount to Chinese amount example: 187.4 is converted to one hundred and eighty-eight dollars * / CREATE FUNCTION [dbo]. [CNumeric] (@ num numeric (14jue 2)) returns nvarchar (100) BEGIN Declare @ n_data nvarchar (20), @ c_data nvarchar (100), @ n_str nvarchar (10) @ i int Set @ n_data=right (space (14) + cast (cast (abs (@ num*100) as bigint) as nvarchar (20)), 14) Set @ Set @ iTun1 WHILE @ I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.