WHERE includes all letters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

WHERE includes all letters

Author

Topic

r0bbb
Starting Member

3 Posts

Posted - 2006-04-18 : 04:31:46

Hi

I would like to perform a stored procedure which takes a string of numbers and searches for strings 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)

is there any way to make it search for strings which include 'all' numbers given?

thanks

Robert

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 04:44:22

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

r0bbb
Starting Member

3 Posts

Posted - 2006-04-18 : 04:50:19

well theyre just telephone numbers, 020 8647 8463, 0181 465 4756 etc... i want the procedure to take a string of numbers, eg 135, and find telephone numbers which contain all the numbers 1 3 5, so 020 8570 1320 would be returned, but 020 8570 1456 wouldnt (doesnt contain the no. 3). so far its only possible to find telephone numbers which contain any of the numbers in the given string.

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 05:16:37

Consider this as an example



declare @digit table(digit int)
insert into @digit select 1 union all select 3 union all select 5

select t.tel_no from 
(
	select '020 8647 8463, 0181 465 4756' as tel_no union all
	select '020 8647 8463, 0181' as tel_no union all
	select '15020 8647 8463' as tel_no
) T , @digit d
where t.tel_no like '%'+cast(d.digit as varchar(1))+'%' 
group by t.tel_no having count(*)>=3

Madhivanan

Failing to plan is Planning to fail

pootle_flump

1064 Posts

Posted - 2006-04-18 : 05:33:55

Hi

I had a play too. This is pretty well the same as madhivanan's just using the variables (wasn't fast enough):

CREATE TABLE #tbl_Person
	(Phone VarChar(20))

INSERT INTO
	#tbl_Person (Phone)
SELECT	'020 8647 8463' UNION
SELECT 	'0181 465 4756' UNION
SELECT	'020 8570 1456' UNION
SELECT 	'020 8570 1320' 

DECLARE @IncludeNumbers VARCHAR(50)

SELECT @IncludeNumbers = '135'

SELECT	Phone
FROM	#tbl_Person
		CROSS JOIN--Required Numbers
			(SELECT CAST(Number AS Char(1)) AS Number
			FROM 	dbo.Numbers N
			WHERE	N.Number BETWEEN 0 AND 9
				AND @IncludeNumbers LIKE '%' + CAST(Number AS Char(1)) + '%') AS N
WHERE	Phone LIKE '%' + Number + '%'
GROUP BY
	Phone
HAVING 	COUNT(*) = LEN(@IncludeNumbers)

DROP TABLE 
	#tbl_Person

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685 is a way of getting the numbers without having a numbers table.

r0bbb
Starting Member

3 Posts

Posted - 2006-04-18 : 09:21:58

hi guys, this isnt really what i was looking for. i was wondering if there was a character for instance, which could be included in the brackets:

WHERE (p.Phone LIKE '[^1234]')

i believe if a ^ is included it means that the string should not contain any of the characters in the brackets. i was hoping it would be that simple.

Thanks

pootle_flump

1064 Posts

Posted - 2006-04-18 : 09:30:45

Hi r0bbb

How do the solutions not cover what you are looking for? Please could you supply more comprehensive sample data and expected results if there is an error. If you mean the answers are too complicated then I am afraid the question is too complicated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources