SQL Server compares whether a string contains one character in another string _mssql

Source: Internet
Author: User
version One: comparing by separator
Algorithm idea: Intercept the search string loop by separator and compare the characters to be searched
Copy Code code as follows:

Use [Fly]
Go
--Parameters: @inStr to search strings, @fndStr search strings, @doc separator
For example: Select Dbo.fsearch (' 1,2,3,4,5,6 ', ' 3,6,5,8,2 ', ', ') return 0-mismatch, return 1-match (with @fndstr content in @instr)
--can be used for table searches, such as:
--Select *,dbo.fsearch (str, ' 3,6,5,8,2 ', ', ') as to match the from table name
--@fndStr and @doc two parameters need to be provided by yourself, @inStr can be the name of a field to be searched in the datasheet
CREATE FUNCTION Cgf_fn_search (@inStr VARCHAR, @fndStr VARCHAR (), @doc VARCHAR (5))
RETURNS INT
As
BEGIN
DECLARE @i int,@c VARCHAR (+), @fStr VARCHAR (500)
SET @fStr = @fndStr
while (LEN (@fStr) > 0)
BEGIN
SET @i = Charindex (@doc, @fStr)
IF (@i = 0)
BEGIN
IF (CHARINDEX (@fStr, @inStr) > 0)
Return 1
ELSE
return 0
End
ELSE
BEGIN
SET @c = SUBSTRING (@fStr, 1,@i-1)
IF (CHARINDEX (@c, @inStr) > 0)
Return 1
ELSE
SET @fStr = SUBSTRING (@fStr, @i+len (@doc), LEN (@fStr))
End
End
return 0
End

version Two: verbatim comparisons
Algorithm thinking: Verbatim interception search string loops compared to the characters to be searched
Copy Code code as follows:

Use [Fly]
Go
/****** object:userdefinedfunction [dbo]. [Cgf_fn_searchchar] Script date:09/03/2010 16:42:12 ******/
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
CREATE FUNCTION [dbo]. [Cgf_fn_searchchar] (@inStr VARCHAR, @fndStr VARCHAR (500))
RETURNS INT
As
BEGIN
DECLARE @i int,@f int,@c VARCHAR (1)
SET @i = 1
SET @f = LEN (@fndStr)
while (@i <= @f)
BEGIN
SET @c = SUBSTRING (@fndStr, @i, @i)
IF (CHARINDEX (@c, @inStr) > 0)
BEGIN
Return 1
End
SET @i = @i + 1
End
return 0
End
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.