|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
June 10th, 2009, 02:38 PM
|
|||
|
|||
|
Compare string to multipe fields
i have a string and I want to find the best match in the database
in the example below the best match would be record 2 Code:
string may be "Dupont wharehouse houston TX" (freetext from input) database will have nameCol | cityCol | stateCol Dupont containers Inc | Houston | TX Dupont Wharehouse A | Houston | TX Dupont Wharehouse | Chicago | IL any suggestions welcome.. 
|
|
#2
June 11th, 2009, 07:23 AM
|
|||
|
|||
|
Code:
declare @t table (nameCol varchar(32),cityCol varchar(32), stateCol varchar(32))
INSERT INTO @T select 'Dupont containers Inc', 'Houston', 'T' union all select
'Dupont Wharehouse A', 'Houston' , 'TX' union all select
'Dupont Wharehouse', 'Chicago' , 'IL'
declare @str varchar(128)
select @str ='Dupont wharehouse houston TX'
select namecol,citycol,statecol,sum(col1+col2+col3)as sumval
from (
select *,case when right(@str,charindex(' ',reverse(@str))-1) = statecol then 1 else 0 end col1
,case when left(@str, charindex(' ',@str,charindex(' ',@str)+1))= namecol then 1 else 0 end col2,
case when parsename(replace(@str,' ','.'),2)= citycol then 1 else 0 end col3
from @t)s
group by namecol,citycol,statecol
|
|
#3
October 13th, 2009, 02:42 AM
|
|||
|
|||
|
Re: Compare Strings to multiple fields
Hi,
I hope that the below query will be very useful for you.. Code:
select namecol +' '+ citycol+' '+ Statecol from table Thanks& Regards Sakthimeenakshi.S
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Compare string to multipe fields |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
