Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- IF EXISTS (
- SELECT * FROM sysobjects WHERE id = object_id(N'function_name')
- AND xtype IN (N'FN', N'IF', N'TF')
- )
- DROP FUNCTION function_name
- GO
- IF object_id(N'function_name', N'FN') IS NOT NULL
- DROP FUNCTION function_name
- GO
- if object_id('FUNCTION_NAME') is not NULL
- DROP FUNCTION <name>
- IF EXISTS (SELECT *
- FROM sysobjects
- WHERE name='<function name>' and xtype='FN'
- xtype in ('FN','TF')
- IF OBJECT_ID('YourObjectName') IS NOT NULL
- IF OBJECT_ID('YourFunction') IS NOT NULL
- DROP FUNCTION YourFunction
- GO
- IF EXISTS
- (SELECT * FROM sys.objects
- WHERE object_id = OBJECT_ID(N'functionName')
- AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
- DROP FUNCTION functionName
- GO
- DROP FUNCTION [ IF EXISTS ] { [ schema_name. ] function_name } [ ,...n ]
- [;]
- CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
- IF EXISTS
- (SELECT *
- FROM schema.sys.objects
- WHERE name = 'func_name')
- DROP FUNCTION [dbo].[func_name]
- GO
- DROP Function IF EXISTS udf_name
- if(object_id(N'[dbo].[fn_Nth_Pos]', N'FN')) is not null
- drop function [dbo].[fn_Nth_Pos];
- GO
- CREATE FUNCTION [dbo].[fn_Nth_Pos]
- (
- @find char, --char to find
- @search varchar(max), --string to process
- @nth int --occurrence
- )
- RETURNS int
- AS
- BEGIN
- declare @pos int --position of nth occurrence
- --init
- set @pos = 0
- while(@nth > 0)
- begin
- set @pos = charindex(@find,@search,@pos+1)
- set @nth = @nth - 1
- end
- return @pos
- END
- GO
- --EXAMPLE
- declare @files table(name varchar(max));
- insert into @files(name) values('abc_1_2_3_4.gif');
- insert into @files(name) values('zzz_12_3_3_45.gif');
- select
- f.name,
- dbo.fn_Nth_Pos('_', f.name, 1) as [1st],
- dbo.fn_Nth_Pos('_', f.name, 2) as [2nd],
- dbo.fn_Nth_Pos('_', f.name, 3) as [3rd],
- dbo.fn_Nth_Pos('_', f.name, 4) as [4th]
- from
- @files f;
- IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE
- object_id = OBJECT_ID(N'[Schema].[function_Name]')
- AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
- BEGIN
- DROP FUNCTION [Schema].[function_Name]
- Print('function dropped => [Schema].[function_Name]')
- END
- GO
- IF EXISTS (
- SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'FunctionName'
- )
- DROP FUNCTION [dbo].[FunctionName]
- GO
Add Comment
Please, Sign In to add comment