0

I am creating a prototype of a data warehouse in SQL Server 2008 R2. The customer wants several key fields put into a single, comma-delimited column, where they can do a key-word search. I have implemented Full Text Searching, but is there a way to search only a specific part of the column? For example, if the column data looks like:

DATA_Column

A,B,C,D,A,E

B,B,D,C,A,G

Is there a way to do a full text search for an "A" that only exists in the 5th position?

Such a query should return the second row in the above example, but NOT the first.

user1873604
  • 163
  • 1
  • 1
  • 12
  • 1
    Did you try to talk the client out of doing it this way? Distinct pieces of data merged into a column is not considered good practice. With that said, you could try to split the string and then search for the Nth string. This link may help: http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco – RonaldBarzell Dec 03 '12 at 19:28
  • 2
    ......This sounds like a **colossally** bad idea. Why can't you just have a secondary table to hold those values? – Mike G Dec 03 '12 at 19:35
  • Thanks, I'll take a look at that. It's a government client, so changing their mind isn't always the easiest thing :) – user1873604 Dec 03 '12 at 19:39
  • #winces# - besides the usual problems with searching/updating these sorts of columns, they're also usually effectively impossible to use with an index. I don't know enough about 2008-r2 specifically, are things like `SUBSTR()` (or just using the full-text searching) able to use an index intelligently? Otherwise.... full table scans, whee! – Clockwork-Muse Dec 03 '12 at 20:58

0 Answers0