Re: String Slicing in Queries

Ian Logan wrote: > Hi > > I have a little problem to resolve and am not sure if I can easily do it in > a stored procedure. I may have to revert to VB. Here is the problem: > If you're going to do string comparisons outside of the database use ESQL/C. (Speed and versatility.) You could also do this in Java... there are some advantages but you'd take a speed hit. > I have a table which contains a text column with contains various string > descriptions e.g. "ABC-XYZ", "HAM LAND SLIP". I need to relate each row to > another table with a similar column, but with slightly different format e.g. > "ABC XYZ", "HAM LAND-SLIP". This problem is due to information being passed > on several times. > So you have a column of data which may contain a substring of alpha with an unknown length and then a non-alpha or non-alpha numeric string then another alpha numeric string of another unknown length. So using your example you could have "BC/BS" or "BC BS" "BCBS" to represent Blue Cross Blue Shield. Or do you mean that you would have "BC BS" or "BC-BS" or "BC/BS" or "BC-TED" or "BCP-LAND-Duh". The point is that you want the "BC BS" to only match "BC-BS" or "BC/BS" and not the other ones. And if you have a "BC BS TN" it doesn't match "BC BS" right? What about " BS BS" do you want that to match? Or "bc bs" to also match? > I think the way to do this is to break up the first column into complete > words and then check for instances of them in the second table. I can use > the LIKE %string% for the check. However the problem is breaking up the > first column. I need to check for spaces and then extract the whole word. I > thought I could use the String[1,3] (for example) to do this by using a > variable e.g. String[intCounter, 1] to step through the column data. However > it is not accepted. I do not want to build a huge procedure that goes > through every option [1,1], [2,1], [3,1], etc. So any ideas folks? > > In VB it would be easy with InStr, but I really want to get all the > processing done on the server. It would also be slow, and you'd want to write a parsing function. Can you do it in VB? If thats all you know than sure. First assumption... You can take this production machine and issolate it such that you are the only user. (It makes locking the tables easier...) Second assumption... You have a column in Table A that you can use as a unique identifier, relational id, to join the other tables and that you have a column in Table B that you can store the relational ID. Third assumption... You will clean your data so that the first character is a alpha/alpha numeric and not a space character. (No leading " " , "-", ...) Fourth Assumption ... You will want "BC BS" to match "BC--BS".... The program is relatively trivial.... You would need to write a simple function that you would pass in a string and a pointer to a linked list returning the pointer to the linked list. The function will do a single pass on the string, stripping off the tokens/substrings and creating a substring and placing it on the list. You would then write a two cursor loop. The outer cursor would do a select from table A the unique id and the string to be parsed.... You then parse the string and in the inner cursor, you select all the rows that match the first substring. Based on your requirements, for each row in the inner cursor you would use the same function to parse the second string. If the number of sub elements don't match, you don't have a match and you can skip to the next row. Otherwise you then do a string compare on the substrings. If they all match, then you have a match and you update the second table with the appropriate id so you can do a table join. > > BTW The system is Informix 9.40 on W2K, with VB6 and .NET. > Ok, so then you won't have a native C compiler since you're on an inferior platform. Sorry but I am a UNIX/LINUX bigot for a good reason. ;-) Then do this in Java. I guess you could do it in VB if thats all you have. Actually this would be an excellent programming exercise for an undergrad. In either Java or C. C would deal with malloc()/calloc() memory allocation and freeing it. Also pointers... Java would deal with JDBC, use of meta data. A vector or list pointer, garbage collection, etc ... > Many thanks > Ian Logan > Just a lonley voice on the silicon prarie... But hey, what do I know. I'm not supposed to be technical anymore. ;-)