wildcoard search - 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)

wildcoard search

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-04 : 04:07:05

select * from users where domain like @mydomain

Now what I really want is the like to work that any subdomain of that domain would also work -- so technically the hitdomain should ignore the http// or www - and only compare to the domains after the first .

How woudl I do this?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-04 : 04:12:09

Somthing like this



Declare @DomainName varchar(100)
Set @DomainName = 'yahoo'
Declare @Domain Table 
(
domainname varchar(100)
)
Insert @Domain 
Select 'http://www.yahoo.com' Union All
Select 'http://www.sqlteam.com' Union All
Select 'http://www.hotmail.com' Union All
Select 'http://www.google.com' Union All
Select 'http://www.rediff.com' 

Select * From @Domain Where DomainName like '%' + @DomainName + '%' 

Or 
Select * From @Domain Where PatIndex('%'+ @DomainName + '%',domainname) >0

Chirag

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-04 : 04:24:23

except that the domains entered could be xxx.google.com and yy.google.com or http://google.com or http://xx.google.com and I want all to be pulled up as google.com.
can you help me with this? AND teh doamin in the db will just have google.com
I was thinking maybe to have to take the doamin being passed in and if only to take the end part (which can be 2 cases -- if http:// then anythign after the http or if there are 2 dots - only everything after the second .) and then search that by wildcard.

how would that work?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-04 : 05:27:47

Check out this


Create Function Dbo.CrackDomain(@DomainName varchar(100))
Returns varchar
AS
Begin 
Select @DomainName = Case When CharIndex('http://',@DomainName)>0 Then 
			    Case When  CharIndex('.',@DomainName,CharIndex('.',@DomainName,CharIndex('http://',@DomainName)))-CharIndex('.',@DomainName,CharIndex('http://',@DomainName)) =0	Then 
 			     Substring(@DomainName,CharIndex('/',@DomainName)+2,CharIndex('.',@DomainName)-(CharIndex('/',@DomainName)+2))  
			    Else	
			     SubString(@DomainName,CharIndex('.',@DomainName,CharIndex('http://',@DomainName))+1,
			     CharIndex('.',@DomainName,CharIndex('.',@DomainName,CharIndex('http://',@DomainName))+1)-CharIndex('.',@DomainName,CharIndex('http://',@DomainName))-1)	
			   End
		     Else 
		     Substring(@DomainName,CharIndex('.',@DomainName)+1,(CharIndex('.',@DomainName,CharIndex('.',@DomainName)+1) -CharIndex('.',@DomainName))-1) 		
		     End 
return @DomainName

GO
Declare @DomainName varchar(100)
Set @DomainName = 'xx.google.com'

Declare @Domain Table 
(
domainname varchar(100)
)
Insert @Domain 
Select 'http://www.yahoo.com' Union All
Select 'http://www.sqlteam.com' Union All
Select 'http://google.com' Union All
Select 'http://www.rediff.com' Union All 
Select 'http://www.mail.yahoo.com'


Select * From @Domain Where DomainName like '%' + Dbo.CrackDomain(@DomainName) + '%' 
--or
Select * From @Domain Where PatIndex('%'+ Dbo.CrackDomain(@DomainName) + '%',domainname) >0

End

Chirag

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-04 : 05:38:25

i think i see what your doing but I think it's teh opposite

meanign in the table will be stored

yahoo.com
google.com
redif.com

and passed in to teh sp @mydomain will be xxx.yahho.com or http://www.google.com or http://google.com or http://xxx.google.com and all should find the right one

is that what this function is doing or not?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-04 : 05:58:20

Wait.. i guess there is some problem.. yeah the function should extactly do that

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-04 : 06:06:03

Is this what you want ?

declare @domain table
(
	domainname	varchar(100)
)

insert into @domain
select	'yahoo.com'	union all
select	'google.com'	union all
select	'redif.com'

declare @mydomain	varchar(100)
select	@mydomain = 'http://xxx.google.com'

select	*
from	@domain
where	@mydomain like '%' + domainname + '%'

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-04 : 06:08:03

when i run the function i get an error - ncorrect syntax near '@DomainName'.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-04 : 06:10:33

khtan - yes that's what i want but if you do this you'll see no records will be returned when you search a @mydomain=http://xxx.google.com when I want the google.com record to be returned

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-04 : 06:12:06

quote:
Originally posted by esthera

when i run the function i get an error - ncorrect syntax near '@DomainName'.

esthera, are you referring to the script that i posted ? If it is, there isn't a variable named @DomainName in there at all.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-04 : 06:13:33

Repost with result shown

declare @domain table
(
	domainname	varchar(100)
)

insert into @domain
select	'yahoo.com'	union all
select	'google.com'	union all
select	'redif.com'

declare @mydomain	varchar(100)
select	@mydomain = 'http://xxx.google.com'

select	*
from	@domain
where	@mydomain like '%' + domainname + '%'

/* RESULT :

domainname                                                                                           
----------
google.com

*/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-04 : 06:14:16

Check out this




Create Function Dbo.CrackDomain(@DomainName varchar(100))
Returns varchar(100)
AS
Begin 
Select @DomainName = Case When CharIndex('http://',@DomainName)>0 Then 
			    Case When  CharIndex('.',@DomainName,CharIndex('.',@DomainName,CharIndex('http://',@DomainName)))-CharIndex('.',@DomainName,CharIndex('http://',@DomainName))-1 <=0	Then 
 			     Substring(@DomainName,CharIndex('/',@DomainName)+2,CharIndex('.',@DomainName)-(CharIndex('/',@DomainName)+2))  
			    Else	
			     SubString(@DomainName,CharIndex('.',@DomainName,CharIndex('http://',@DomainName))+1,
			     CharIndex('.',@DomainName,CharIndex('.',@DomainName,CharIndex('http://',@DomainName))+1)-CharIndex('.',@DomainName,CharIndex('http://',@DomainName))-1)	
			   End
		     Else 
		     Substring(@DomainName,CharIndex('.',@DomainName)+1,(CharIndex('.',@DomainName,CharIndex('.',@DomainName)+1) -CharIndex('.',@DomainName))-1) 		
		     End 

return @DomainName
End 
GO

Declare @DomainName varchar(100)
Set @DomainName = 'xx.google.com'



Declare @Domain Table 
(
domainname varchar(100)
)
Insert @Domain 
Select 'http://google.com' Union All
Select 'http://yahoo.com' Union all 
Select 'http://hotmail.com' 


Select * From @Domain Where DomainName like '%' + Dbo.CrackDomain(@DomainName) + '%' 

Set @DomainName = 'xx.google.com'

Select * From @Domain Where DomainName like '%' + Dbo.CrackDomain(@DomainName) + '%' 

Set @DomainName = 'www.google.com'
Select * From @Domain Where DomainName like '%' + Dbo.CrackDomain(@DomainName) + '%' 

Set @DomainName = 'http://google.com'
Select * From @Domain Where DomainName like '%' + Dbo.CrackDomain(@DomainName) + '%'

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-04 : 06:25:04

if in the database you will be having the data like

]
yahoo.com
google.com
sqlteam.com

Then Tan method is the best way..

but if in the table you will be having the records like


www.yahoo.com
www.google.com
www.sqlteam.com

Then i guess you need to work around with my method...

Chirag

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-04 : 06:41:13

khtan - it's not working either

i have in my domain table
google.com

now when i do select * from domains where mydomain like '%xx.google.com%'
it returns no records

when i do
select * from domains where mydomain like '%google.com%' then it works but I want it to return the record in the first scenario as well.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-04 : 06:46:13

It should be like this


select * from domains where 'xx.google.com' Like '%' + mydomain + '%'

Chirag

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-04 : 07:04:14

It should be like this

select * from domains where 'xx.google.com' Like '%' + mydomain + '%'
BUT I have the opposite -- i'm passing to the sp xx.google.com and the google.com is in the domains table (and the record I want to select)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-04 : 07:06:43

Does the above query fetches you the record which are the intended?

Chirag

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-04 : 07:09:06

What about:

SELECT id FORM domains
WHERE domainname = REVERSE(LEFT(REVERSE(@domain), CHARINDEX('.', REVERSE(REPLACE('.' + @domain, '/', '.')), CHARINDEX('.', REVERSE(@domain)) + 1) - 1))

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-04 : 07:10:56

I can't do it like that

i have a table called domains with a domainname (formated as google.com)

then I have a stroed procedure that takes in the domain name (but with the additioanl part xxx.google.com) -- I want to do a select statement to pull up that record (google.com) (but obviously it's not always google teh xxx.google.com is getting passed to the sp.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-06-04 : 07:13:37

Can you post some sample data from your table?

Chirag

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-06-04 : 07:16:13

table --domains

sample data

domains -- id

google.com 1
yahoo.com 2
altavista.com 3

now the stored procedure is being called with a varialbe called domain and for example = xxx.google.com
I want to be able to pull it up as Id 1

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources