WHERE includes all letters

Hi

I would like to perform a procedure which takes a string of numbers and searches for strings in a table which include all the given numbers in any particular order. problem is i can only return strings which include 'any' of the letters in the search string and not 'all'.

this is how i perform it so far:

@IncludeNumbers VARCHAR(50)

AS
BEGIN

SELECT Phone AS [Telephone Number]

FROM tbl_Person AS p

WHERE (p.Phone LIKE '%[' + @IncludeNumbers + ']%')

END

(ive removed some unecessary bits but this shows the basics)

As you can see currently it will return any phone numbers which contain any numbers given in the includeNumbers string, I would only like it to return phone numbers which contain all the numbers given in the includeNumbers string. is there any way to make it search for strings which include 'all' numbers given

thanks

Robert

Answer this question

WHERE includes all letters

PioM
What you could do is to ask for numbers that has only the range in the wildcard digits, but no other digit. 'Not like not your range' will do that.

WHERE phone NOT LIKE '%[^' + @includeNumbers + ']%'

...this however, has a limitation.
If the variable contains '123', you will match against '123', '321', but not '1234', but you'll get '12' since it contains only 'allowed' digits. To correct mismatches against too short numbers, you could just filter out the shorties.

WHERE phone NOT LIKE '%[^' + @includeNumbers + ']%'
AND LEN(phone) >= LEN(@includeNumbers)

..should do what you want regarding 'exclusive' matching. It seems to work along this small example anyway =;o)

create table #x ( phone varchar(10) not null )
go
insert #x
select 123 union all
select 7717 union all
select 7727 union all
select 321 union all
select 12 union all
select 1234
go

declare @includeNumbers varchar(10)
set @includeNumbers = '123'

select * from #x
WHERE phone NOT LIKE '%[^' + @includeNumbers + ']%'
AND LEN(phone) >= LEN(@includeNumbers)
go
drop table #x
go

phone
----------
123
321

(2 row(s) affected)

=;o)
/Kenneth

-- edit --
Though not working anyway... '122' will also match, so it's not including all members of @includeNumbers anyway.. =:o/ bummer...

What you need I belive is relational division. Not sure if that is possible without breaking up each digit in @includeNumbers as it's own element, though..
aladdinm

Oh, OK. I've not seen that before. So you want 123 to match 321 but not 732, right I don't know of a more obvious solution than having a LIKE clause for each digit.
LonnieBest

LIKE will return anything that contains exactly the string contained in @includeNumbers, with anything you like either side of it, because you have a % on both sides. LIKE '%123%' will NOT match 7717, 7727, or 321 for that matter.
hoangle
the variable is between square brackets [ ], so will find telephone strings which contain any of the characters in the variable.

thanks
ttoennies

Using KeWin's schema above, you can use the following query:

declare @includeNumbers varchar(10)
set @includeNumbers = '123'
select * from #x
where phone like '%[' + @includeNumbers + ']%'
and len(@includeNumbers) = (select count(distinct pd)
from (select substring(phone, n.d, 1) as pd
from (select 1 union all select 2 union all

select 3 union all select 4 union all

select 5 union all select 6 union all

select 7 union all select 8 union all

select 9 union all select 10) as n(d)
where substring(phone, n.d, 1) like '[' + @includeNumbers + ']'
) as t)

Basically, I count the distinct matching digits in the phone number to make sure they are same as the search pattern. The first LIKE is actually redundant.
LordActon
yer thats what i was thinking but the input will vary in length and i dont want to have to split up each individual character. i suppose i could do a loop with substr or something like that. anyone care to venture a guess
WHERE includes all letters

Answer this question