Check existence of each character in a string with

问题:

I have a string

@Str1 = '123'

I have another string

@Str2 = '12345'

I need to compare both strings and return 1 if each character in @Str1 have an existence in @Str2

even If the @Str2 is '45132' it must return 1,

If @Str2 is 456 It must return 0

Even If the @Str2 is '45132' it must return 1

I don't suggest use of any functions, because my live table has 1 million rows in it. Possibly I could avoid performance hit.

回答1:

declare @str1 VARCHAR(20) = '123'
declare @str2 VARCHAR(20) = '12345'


SELECT CASE WHEN EXISTS (
        SELECT 1
        FROM master.dbo.spt_values V 
        WHERE V.type='P' 
            AND V.number    BETWEEN 1 AND LEN(@str1)
            AND CHARINDEX(SUBSTRING(@str1, v.number, 1), @str2) = 0
    ) THEN 0
    ELSE 1
    END

回答2:

One quick option is the sign() of charindex(). This will return 1 or 0.

Declare @Str1 varchar(25) = '123'
Declare @Str2 varchar(25) = '12345'

Select sign(charindex(@Str1,@Str2))

Returns

1

回答3:

Using what Zohar ansewered you in How can I insert each character of a string which is not comma delimited to separate row in a table?

you can use:

SELECT CASE WHEN SUM(X)=LEN(@STR) THEN 1 ELSE 0  END AS ALL_PRESENT
FROM (
    SELECT CASE WHEN CHARINDEX( SUBSTRING(@Str, Number, 1), @STR2) >0 THEN 1 ELSE 0 END AS X
    FROM Tally
    WHERE Number <= LEN(@Str)
    ) A

or

SELECT CASE WHEN COUNT(X)=LEN(@STR) THEN 1 ELSE 0  END AS ALL_PRESENT
FROM (
    SELECT CASE WHEN CHARINDEX( SUBSTRING(@Str, Number, 1), @STR2) >0 THEN 1 END AS X
    FROM Tally
    WHERE Number <= LEN(@Str)
    ) A

Output using

DECLARE @STR VARCHAR(10)='123'
DECLARE @STR2 VARCHAR(10)='456123345'

ALL_PRESENT
-----------
1

Output using

DECLARE @STR VARCHAR(10)='123'
DECLARE @STR2 VARCHAR(10)='45613345'

ALL_PRESENT
-----------
0

回答4:

Assuming col1 is like @str1 and col2 is like @str2 then your query can be

SELECT Col1, Col2, CASE WHEN Col2 LIKE Col1+'%' THEN 1 ELSE 0 END FROM YourTable

W/o using any functions and adding for the case

Even If the @Str2 is '45132' it must return 1

query can be like

SELECT Col1, Col2, CASE WHEN Col2 LIKE '%'+ Col1+'%' THEN 1 ELSE 0 END FROM YourTable

回答5:

select count(*) from (select '1928345' as Col1) as Table1
where Table1.Col1 like '%1%2%3%'