33 lines
1.1 KiB
Transact-SQL
33 lines
1.1 KiB
Transact-SQL
|
|
/****** Object: StoredProcedure [vefn_FixSearchString] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vefn_FixSearchString]') AND OBJECTPROPERTY(id,N'IsScalarFunction') = 1)
|
|
DROP FUNCTION [vefn_FixSearchString];
|
|
GO
|
|
|
|
/*
|
|
select ID,ss,.dbo.vefn_FixSearchString(ss)
|
|
from (
|
|
select 1 ID,'%' ss union
|
|
select 2 ID,'50[%]' ss union
|
|
select 3 ID,'IF%' ss union
|
|
select 4 ID,'%then:' ss union
|
|
select 5 ID,'530`F' ss union
|
|
select 6 ID,'check' ss union
|
|
select 7 ID,'RCP%Cooling' ss) tt order by ID
|
|
*/
|
|
CREATE FUNCTION [dbo].[vefn_FixSearchString](@SearchString varchar(MAX))
|
|
RETURNS varchar(MAX)
|
|
AS
|
|
BEGIN
|
|
-- This code adds % at the beginning and end if the beginning and end
|
|
-- of the search string if it does not have % at the beginning or end
|
|
IF(@SearchString like '[%]%') RETURN @SearchString
|
|
IF(@SearchString like '%[%]') RETURN @SearchString
|
|
RETURN '%' + @SearchString + '%'
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'ScalarFunction Creation: vefn_FixSearchString Succeeded'
|
|
ELSE PRINT 'ScalarFunction Creation: vefn_FixSearchString Error on Creation'
|
|
GO
|