Counting occurances of strings in table column

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  May 11, 2011 at 5:18 am

  #1322861

  I've already tackled the problem when doing change documentation (or more change list).

  Let's say you alter the invoice object. What are all the points in the application that you need to look over to make sure they don't need to be altered.

  What I did then was to first make a list of possible conflicts >> InvoiceDetails, InvoiceWhatever.

  Then I would run the query for those first and save the hits (exact place in the string where I find it).

  Then I would finally run the "master" search and eleminate the false hits by comparing the master results with the previous runs.

  That worked 99.99%. I still had false positives for various reasons that I never ironed out but could have been.

  If the screen option is not working for you you can always go that route.

  Start with recursive query to figure out the order in which to run the search and run in it that sequence. That will be longer to code, probably way longer to run but will give you the precision you need.

• Fal

  Hall of Fame

  Points: 3143

  More actions

  May 12, 2011 at 2:38 am

  #1323520

  Not sure if this is still an issue, and it's late for me. Both of those are reasons I shouldn't post code. 😛

  It occurs to be that a string within a string should be shorter then the containing string, hence removing those longer strings first should remove the problem. I've also avoided the use of a cursor. There's also some shortcomings. However I offer the below in case it gives you some ideas.

  1) Determine order by length of string

  2) Make an (ikky) REPLACE string which substitutes a dud string 1 less then the original

  3) Run an UPDATE with the ikky REPLACE

  4) TheCount contains the number of matches, being the difference between the original and updated strings

  You mentioned that you have 1300 lookups. That's like 1300 REPLACE functions. Eew. But there might be a concept in there that helps you.

  Steve.

  ----------------------------------------------

  --drop table MyString;

  create table MyString

  (

  TheStringvarchar(10)

  , TheLengthas len(TheString) persisted

  );

  -- Force an order

  create clustered index SortOrder on MyString (TheLength desc, TheString);

  insert into MyString values('A1');

  insert into MyString values('A10');

  insert into MyString values('A11');

  insert into MyString values('A12');

  insert into MyString values('A122');

  insert into MyString values('A3');

  insert into MyString values('A22');

  --select* from MyString

  ----------------------------------------------

  --drop table MySearchables;

  create table MySearchables

  (

  TheSearchablevarchar(100)

  , TheReplacedvarchar(100) default('')

  , TheCountas len(TheSearchable) - len(TheReplaced) persisted

  );

  insert into MySearchables (TheSearchable) values('A10A10A1A11dfgaA12');-- 5

  insert into MySearchables (TheSearchable) values('asdfgzgaf');-- 0

  insert into MySearchables (TheSearchable) values('A12asdfagA122');-- 2

  --select* from MySearchables;

  ----------------------------------------------

  declare @replacevarchar(8000)

  , @corevarchar(1);

  select@replace = '';

  select@replace = 'replace(' + coalesce(@Core,'TheSearchable') + @replace + ',''' + TheString + ''',''' + replicate('X',TheLength-1) + ''')'

  , @core = ''

  fromMyString

  select@replace = 'update MySearchables set TheReplaced = ' + @replace

  exec(@replace)

  select*

  fromMySearchables;