0

I'm looking to find the last Common Subsequence between two strings in sql. For instance, I have this two strings "A#CDE#FGH" and "FGH#IJK#CDE", in this case the last common subsequence is FGH (the last one in the first string).

Does anyone know how to implement that in sql ? I've been trying for the last couple of hours without any breakthrough.

Thanks.

jacen44
  • 168
  • 3
  • 18
  • 1
    I don't have Teradata to try this out on but my approach would be: 1) Use STRTOK_SPLIT_TO_TABLE to split the delimited string to rows (do this twice so you have two tables) 2) Join the two tables created above, order it per your requirement and return only the first row. I am a bit unsure from your question what the criteria is for the latest match, is it the lowest alpha search, or does where it placed in the first string matter. If it's the later I would add a rownumber when creating that table and use it in the sort – Darryls99 Jul 17 '18 at 16:15
  • How do you define "last Common Subsequence"? Would `A#CDE#FGX` match `YFG#IJK#CDE` on `FG`? – dnoeth Jul 17 '18 at 16:30
  • @jacen44 . . . Such string operations are really not what SQL is designed for. – Gordon Linoff Jul 17 '18 at 17:19
  • 1
    I think this question is an excellent example of why SQL Data needs to be Normalized. – Kevin Jul 17 '18 at 18:13
  • @Darryls99, yes, I thought the same thing with STRTOK_SPLIT_TO_TABLE and with the tokennum return I can play with it to select the last one. But performance wise, it wasn't great, of course. I was able to convince people on our project to get the second string split and stored into lines. So now, I can do a simple join and get the last one matching. – jacen44 Jul 18 '18 at 06:07
  • @dnoeth No, the entire string between the hashtag must match an other entire string between a hashtag from the other one. I wasn't really precise on this part. – jacen44 Jul 18 '18 at 06:09

0 Answers0