Why on earth are you using full text names to search on? How well does this perform?
You will need a UDF to get some form of REGEX in SQL Server I think. "This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: Mark A Kruger To: CF-Talk Sent: Sat Nov 18 19:22:52 2006 Subject: RE: MySQL to MSSQL Help Here's some help with the conditional logic. You will have to test it of course - I'm not sure I got every logic item right. It's far more readable than the nested ifs though :) I'm not sure about the REGEX in the where clause. You can do REgex's in mssql but I believe you would need an add on extended function ... I recall one called xp_regex_... something. IF you google around you might find it. Good luck! -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com <http://www.cfwebtools.com/> www.coldfusionmuse.com <http://www.coldfusionmuse.com/> <http://www.necfug.com/> www.necfug.com SELECT o.OrderNumber, o.OrderID, o.MessageDate, o.PostageHandeling, o.DispatchTo, o.AddressLine1, o.AddressLine2, o.TownCity, o.County, o.PostCode, o.Country, i.BuyersPrice, i.Quantity, i.SKU, q.Location, m.MediaTypeName, m.MediaTypeID, m.MediaMass, p.PlatformName, s.Title, s.Artist, s.Title + CASE WHEN m.MediaTypeID = 2 THEN + ' - ' + s.Artist WHEN m.MediaTypeID = 3 THEN p.PlatformName + m.MediaTypeName END AS SuperTitle, CASE WHEN o.country = 'United Kingdom' AND i.BuyersPrice * i.Quantity > 49.99 THEN 'SD' WHEN o.country = 'United Kingdom' AND i.buyersPrice * i.Quantity > 34.99 THEN 'RD' WHEN o.country = 'United Kingdom' AND isNull(f.flatsortcode) THEN 'XX' WHEN o.country = 'United Kingdom' AND i.buyersPrice * i.Quantity > 39.99 THEN 'IS' WHEN o.country IN ('Austria','Belgium','Denmark', 'Finland','France','Germany','Ireland','Italy', 'Luxembourg','Netherlands','Portugal','Spain', 'Sweden','Switzerland') THEN 'EUR' WHEN o.country IN ('Australia','Brazil','Canada','Hong Kong','Iceland', 'Japan','Norway','Republic of Korea','Mexico','New Zealand', 'South Africa','USA') THEN 'ROW', ELSE 'CHK' AS BagNumber FROM amazonemailitemdetails i JOIN amazonemailorderdetails o ON (i.OrderID = o.OrderID) JOIN StockQuantities q ON (q.StockID = i.SKU) JOIN stockitemdetails s ON (s.ItemID = q.ItemID) JOIN MediaTypes m ON (m.MediaTypeID = s.MediaType) JOIN Platforms p ON (p.PlatformID = s.Platform) LEFT JOIN flatsortareas f ON (left(o.PostCode,CASE o.PostCode REGEXP '^[a-z|A-Z][0-9]' when 1 then 1 else 2 END) = f.postcodearea) WHERE o.Printed = 0 ORDER BY q.Location ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261056 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
