Searching set of characters in a string

Question

How to query if i want to check if string contains numbers(characters) like 1,2,3,4,5,6,7 in any order. It can be achieved by writing AND statements on LIKE clause for number 1,2..and so on like below,

Where days like '%1%' and days like '%2%' ...... So on

Is there any query which check specific characters present in string. or how above example can achieve with a short hand query. Please help. Thanks.

score 1 · Answer 1 · answered Dec 06 '12 at 17:25

One way to do this is create a table with all the string you like to search.

e.g.

DECLARE  @searchstr TABLE (s VARCHAR(10))
INSERT INTO @searchstr VALUES ('1'),('2'),('3'),('4'),('5'),('6'),('7')

DECLARE  @tbl TABLE (days VARCHAR(100))
INSERT INTO @tbl VALUES ('1234567'),('123'),('1122334'),('7654321')  

SELECT   t.days 
FROM @tbl t
LEFT JOIN  @searchstr s 
        ON t.days LIKE '%' + s.s+ '%'
GROUP BY t.days HAVING COUNT(DISTINCT s.s) = 7

score 0 · Answer 2 · edited May 23 '17 at 11:55

0

Will not CONTAINS() work for you?

Also notice how to split word into char array.

edited May 23 '17 at 11:55

Community

1
1

answered Dec 06 '12 at 07:40

abatishchev

98,240
88
296
433

@eraj It would, but the thing is, you have to use full-text indexing to use this predicate. – Ivan Golović Dec 06 '12 at 08:10
@eraj: if you are using full-text for this, you need to remove those letters from stoplist, because otherwise it will search only words not letters..and also full-text search is not preferable for your requirement I suppose.. – techBeginner Dec 06 '12 at 08:28

score 0 · Answer 3 · answered Dec 06 '12 at 08:00

0

I would suggest looking into [PATINDEX][1]. From MSDN:

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

You probably will end up using something like WHERE PATINDEX(%[0-9]%', foo) > 0

answered Dec 06 '12 at 08:00

SchmitzIT

9,227
9
65
92

PATINDEX return true when any characters in the string match with the expression. But i need all these characters(1234567) present in the string. – Rajaram Shelar Dec 06 '12 at 08:11
Then I misread the question. Not sure that would be possible without just using a bunch of AND statements :( – SchmitzIT Dec 06 '12 at 08:25

score 0 · Answer 4 · answered Dec 07 '12 at 14:59

You can use CHARINDEX

CHARINDEX(stringthatcontainschars1, '1', numofpositiontostartlookingat)
Output: 24, the position that your searched for char is at. returns zero if not found

if you were looking for the number 1 in a column or a string you would put:

CHARINDEX(columnname, '1', 1)
This would search for '1' beginning at position 1.

You can do an conditional statement based on whether or not it returns 0 to decide if char is in string or not and write whatever code you need after that.

score 0 · Answer 5 · answered Dec 07 '12 at 15:08

0

I'd use isnumeric to ensure they're all digits, then test for 0, 8, or 9 instead of 1-7 because it's shorter.

answered Dec 07 '12 at 15:08

Beth

9,531
1
24
43

score -2 · Answer 6 · answered Dec 06 '12 at 07:56

-2

If days are coma separated you can use FIND_IN_SET

SELECT FIND_IN_SET(1, days) AND FIND_IN_SET(2, days);

It is different, not better, but as far as I know there is no other native way. And LIKEs probably will be much faster then find_in_set

answered Dec 06 '12 at 07:56

wormhit

3,687
37
46

Searching set of characters in a string

6 Answers6