String Slicing in Queries
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: 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. 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. BTW The system is Informix 9.40 on W2K, with VB6 and .NET. Many thanks Ian Logan
Use SUBSTR(string,start,length) instead of string[start,end] -- Regards, Doug Lawry www.douglawry.webhop.org "Ian Logan" <ian.logan@straker.com> 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: > > 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. > > 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. > > BTW The system is Informix 9.40 on W2K, with VB6 and .NET. > > Many thanks > Ian Logan
Get the regexp datablade and use that 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: > > 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. > > 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. > > BTW The system is Informix 9.40 on W2K, with VB6 and .NET. > > Many thanks > Ian Logan -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #