Check existence of each character in a string with another string in SQL

Question

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.

Apply to this what the answerer told you in How can I insert each character of a string to separate row in a table? — etsa, Apr 27 '17 at 10:13
Are those columns int type or varchar(which may contain alphabets) ? — Samanvitha - user2519049, Apr 27 '17 at 10:14
What about repeating? Str1=112 amd Str2=12345 Is that a hit or miss? — John Cappelletti, Apr 27 '17 at 10:39

John Cappelletti · Answer 1 · 2017-04-27T10:17:22.727

1

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

edited Apr 27 '17 at 10:17

answered Apr 27 '17 at 10:10

John Cappelletti

79,615
7
44
66

1

What if the second string is '14235' ? – Tino Jose Thannippara Apr 27 '17 at 10:21
1

@TinoJoseThannippara It will return 0 – Jibin Balachandran Apr 27 '17 at 10:22
@TinoJoseThannippara Did I misread the question. Are you looking for ALL individual characters? – John Cappelletti Apr 27 '17 at 10:23
Yes. If the second string is '14235' it must return 1. – Tino Jose Thannippara Apr 27 '17 at 10:23
@TheGameiswar Updated – Tino Jose Thannippara Apr 27 '17 at 10:31

score 1 · Answer 2 · edited May 23 '17 at 12:25

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

score 1 · Accepted Answer · answered Apr 27 '17 at 10:44

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

answered Apr 27 '17 at 10:44

Denis Rubashkin

2,151
1
9
14

It seems an exact answer I was looking for. – Tino Jose Thannippara Apr 27 '17 at 10:47

DhruvJoshi · Answer 4 · 2017-04-27T11:54:31.983

0

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

edited Apr 27 '17 at 11:54

answered Apr 27 '17 at 10:17

DhruvJoshi

17,041
6
41
60

Even If the @Str2 is '45132' it must return 1, In this case it may return 0 – Tino Jose Thannippara Apr 27 '17 at 10:42

score 0 · Answer 5 · answered Sep 13 '18 at 18:44

0

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

answered Sep 13 '18 at 18:44

Dalsier

377
3
14

Check existence of each character in a string with another string in SQL

5 Answers5

Linked