January 24, 2011 at 1:35 am
Hi
I am using the below query.But its showing Too many full-text columns or the full-text query.CityOnly field datatype i given varchar.
The values total length is 2594 only.
SELECT * FROM table1 WHERE
contains(CityOnly,'"Addyston"OR"Alexandria"OR"Alpha"OR"Amelia"OR"Anderson"OR"Arlington Heights"OR"Augusta"OR
"Aurora"OR"Avondale"OR"Batavia"OR"Batesville"OR"Bath"OR"Beavercreek"OR"Bellbrook"OR"Bellevue"OR"Bennington"OR
"Bethel"OR"Blanchester"OR"Blanchet"OR"Blue Ash"OR"Boston"OR"Bromley"OR"Brooksville"OR"Brookville"OR"Brown County
"OR"Brownsville"OR"Buford"OR"Burlington"OR"Butler"OR"California"OR"Camden"OR"Camp Dennison"OR"Canaan"OR"Carlisle
"OR"Carrollton"OR"Cedar Grove"OR"Centerville"OR"Chilo"OR"Cincinnati"OR"Clarksville"OR"Clermont Cnty"OR"Clermont County
"OR"Cleves"OR"Cold Spring"OR"Colerain Township"OR"College Corner"OR"College Hill"OR"Collinsville"OR"Corinth"OR
"Covington"OR"Crescent Park"OR"Crescent Springs"OR"Crestview Hills"OR"Crittenden"OR"Cross Plains"OR"Cuba"OR
"Day Heights"OR"Dayton"OR"De Mossville"OR"Decatur"OR"Dillsboro"OR"Dry Ridge"OR"East Enterprise"OR"Eaton"OR
"Edgewood"OR"Elsmere"OR"Erlanger"OR"Fairfield"OR"Falmouth"OR"Farmersville"OR"Fayetteville"OR"Feesburg"OR
"Felicity"OR"Florence"OR"Fort Mitchell"OR"Fort Thomas"OR"Fort Wright"OR"Foster"OR"Franklin"OR"Friendship"OR
"Georgetown"OR"Germantown"OR"Ghent"OR"Glencoe"OR"Glendale"OR"Goshen"OR"Gratis"OR"Gratz"OR"Greendale"OR
"Groesbeck"OR"Guilford"OR"Hamersville"OR"Hamilton"OR"Hamilton Township"OR"Harrison"OR"Harveysburg"OR"Hebron
"OR"Higginsport"OR"Highland Heights"OR"Hillsboro"OR"Holton"OR"Hooven"OR"Independence"OR"Indian Springs"OR
"Jonesville"OR"Kenton"OR"Kettering"OR"Kings Island"OR"Kings Mills"OR"Lakeside Park"OR"Latonia"OR"Latonia Lakes
"OR"Laurel"OR"Lawrenceburg"OR"Lebanon"OR"Liberty"OR"Liberty Township"OR"Lindenwald"OR"Lockland"OR"Loveland"OR
"Ludlow"OR"Lynchburg"OR"Madeira"OR"Maineville"OR"Marathon"OR"Martinsville"OR"Mason"OR"Melbourne"OR"Mentor
"OR"Metamora"OR"Miamisburg"OR"Miamitown"OR"Miamiville"OR"Middletown"OR"Midland"OR"Milan"OR"Milford"OR
"Minerva"OR"Monroe"OR"Montgomery"OR"Moores Hill"OR"Moraine"OR"Morning View"OR"Morris"OR"Morrow"OR"Moscow
"OR"Mount Healthy"OR"Mount Olivet"OR"Mount Orab"OR"Mount Saint Joseph"OR"Mount Washington"OR"Mowrystown"OR
"Napoleon"OR"Neville"OR"New Lebanon"OR"New Liberty"OR"New Point"OR"New Richmond"OR"New Trenton"OR
"New Vienna"OR"Newport"OR"Newtonsville"OR"Newtown"OR"North Bend"OR"North College Hill"OR"Norwood"OR"Okeana
"OR"Oldenburg"OR"Oregonia"OR"Osgood"OR"Overpeck"OR"Owen"OR"Owensville"OR"Owenton"OR"Oxford"OR"Park Hills
"OR"Parkdale"OR"Patriot"OR"Pendleton County"OR"Perry Park"OR"Petersburg"OR"Pierceville"OR"Pleasant Plain"OR
"Poetown"OR"Port Royal"OR"Port William"OR"Queen City"OR"Rabbit Hash"OR"Reading"OR"Ripley"OR"Rising Sun"OR
"Roselawn"OR"Ross"OR"Rossville"OR"Rouse"OR"Russellville"OR"Ryland Heights"OR"Sadieville"OR"Saint Bernard"OR
"Saint Leon"OR"Sanders"OR"Sardinia"OR"Sayler Park"OR"Seven Mile"OR"Shandon"OR"Sharonville"OR"Silver Grove"OR
"Somerville"OR"South Fort Mitchell"OR"South Lebanon"OR"Southgate"OR"Sparta"OR"Spence"OR"Spring Valley"OR"Springboro
"OR"Springdale"OR"Sunman"OR"Symmes"OR"Taft"OR"Taylor Mill"OR"Terrace Park"OR"Township of Washington"OR"Trenton"OR
"Trotwood"OR"Union"OR"Verona"OR"Versailles"OR"Vevay"OR"Villa Hills"OR"Walnut Hills"OR"Walton"OR"Warsaw"OR
"Waynesville"OR"West Alexandria"OR"West Carrollton"OR"West Chester"OR"West College Corner"OR"West Elkton"OR
"West Harrison"OR"Westwood"OR"Wheatley"OR"Wilder"OR"Williamsburg"OR"Williamstown"OR"Wilmington"OR"Winchester"OR
"Worthville"OR"Wyoming"OR"Xenia"') and contains(longState,'"Indiana"OR"Kentucky"OR"Ohio"')
Can anyone help for this scenario?
Visa.G
January 25, 2011 at 8:04 am
I don't have a SQL instance on which to test this, but it may be possible to do something along the following line:
;WITH C AS (
-- FILL IN ALL THE OTHER CITY NAMES HERE IN THE SAME FASHION AS BELOW...
SELECT 'Springboro' UNION ALL
SELECT 'Springdale' UNION ALL
SELECT 'Sunman' UNION ALL
SELECT 'Symmes' UNION ALL
SELECT 'Taft' UNION ALL
SELECT 'Taylor Mill' UNION ALL
SELECT 'Terrace Park' UNION ALL
SELECT 'Township of Washington' UNION ALL
SELECT 'Trenton' UNION ALL
SELECT 'Trotwood' UNION ALL
SELECT 'Union' UNION ALL
SELECT 'Verona' UNION ALL
SELECT 'Versailles' UNION ALL
SELECT 'Vevay' UNION ALL
SELECT 'Villa Hills' UNION ALL
SELECT 'Walnut Hills' UNION ALL
SELECT 'Walton' UNION ALL
SELECT 'Warsaw' UNION ALL
SELECT 'Waynesville' UNION ALL
SELECT 'West Alexandria' UNION ALL
SELECT 'West Carrollton' UNION ALL
SELECT 'West Chester' UNION ALL
SELECT 'West College Corner' UNION ALL
SELECT 'West Elkton' UNION ALL
SELECT 'West Harrison' UNION ALL
SELECT 'Westwood' UNION ALL
SELECT 'Wheatley' UNION ALL
SELECT 'Wilder' UNION ALL
SELECT 'Williamsburg' UNION ALL
SELECT 'Williamstown' UNION ALL
SELECT 'Wilmington' UNION ALL
SELECT 'Winchester' UNION ALL
SELECT 'Worthville' UNION ALL
SELECT 'Wyoming' UNION ALL
SELECT 'Xenia'
)
SELECT T.*
FROM table1 AS T
INNER JOIN C
ON CONTAINS(T.CityOnly, C.CITY)
WHERE contains(T.longState,'"Indiana"OR"Kentucky"OR"Ohio"')
Be aware that while I don't know very much about CONTAINS nor much about FULL TEXT INDEXES, ..... There are some guiding principles that are kind of screaming out of my brain about the idea of storing a city name in a field that is being used with full text indexing, as well as a state name, but as you've provided no details on what the overall scenario is, there's no way to tell if there's good reason or not, save the field names involved, and those rather strongly suggest this is a misuse of the FULL TEXT indexing concept. Let me know if a join will allow this construction, but I have no idea what performance will look like, or if it will even pass a syntax check. I'd like to hear more details so better guidance can be provided...
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply