47 lines
3.1 KiB
Transact-SQL
47 lines
3.1 KiB
Transact-SQL
USE [VEPROMS]
|
||
GO
|
||
/****** Object: UserDefinedFunction [dbo].[vefn_FixSearchString] Script Date: 03/11/2009 09:54:23 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
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 union
|
||
select 8 ID,'14%[34%]' ss union
|
||
select 9 ID,'\*' ss union
|
||
select 10 ID,'\?' ss union
|
||
select 11 ID,'_' ss union
|
||
select 12 ID,'[' ss union
|
||
select 13 ID,']' ss union
|
||
select 14 ID,'%' ss union
|
||
select 15 ID,'_' ss union
|
||
select 16 ID,'-' ss
|
||
) tt order by ID
|
||
*/
|
||
ALTER FUNCTION [dbo].[vefn_FixSearchString](@SearchString nvarchar(MAX))
|
||
RETURNS nvarchar(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
|
||
Set @SearchString = replace(@SearchString,'[','[[]')
|
||
Set @SearchString = replace(@SearchString,'_','[_]')
|
||
Set @SearchString = replace(@SearchString,'%','[%]')
|
||
Set @SearchString = replace(@SearchString,'*','%')
|
||
Set @SearchString = replace(@SearchString,'?','_')
|
||
Set @SearchString = replace(@SearchString,'\%','*')
|
||
Set @SearchString = replace(@SearchString,'\_','?')
|
||
Set @SearchString = replace(@SearchString,'-','[-'+nchar(8209)+']')
|
||
IF(@SearchString like '[%]%') RETURN @SearchString
|
||
IF(@SearchString like '%[%]') RETURN @SearchString
|
||
RETURN '%' + @SearchString + '%'
|
||
END |