Re: [U2] The opposite of Export is.. sometimes not Import...
This was the setup I have used in the past. The advantage over MC/N , MC/A is that it is more flexible if you want to be very specific, or if you want to allow some non alphanumerics as well, since you choose which characters are allowed to stay. George -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Anthony W. Youngman Sent: Sunday, January 24, 2010 7:06 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] The opposite of Export is.. sometimes not Import... CONVERT(OCONV(OCONV(PartNumber,'MC/N'),'MC/A'),'',PartNumber) Slightly longer, but I'd do it as follows CONVERT( ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890, , UPCASE(PartNumber)); CONVERT( @1, , UPCASE(PartNumber)) ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] The opposite of Export is.. sometimes not Import...
In message f07cf5651001211306h41458f3bkf3a2452d48f1d...@mail.gmail.com, Richard Lewis rbl...@gmail.com writes I would probably start by making an index on the Part Number, based on an I-descriptor with an expression like this: CONVERT(OCONV(OCONV(PartNumber,'MC/N'),'MC/A'),'',PartNumber) Slightly longer, but I'd do it as follows CONVERT( ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890, , UPCASE(PartNumber)); CONVERT( @1, , UPCASE(PartNumber)) where PartNumber is a D-pointer to the raw data field that is the Part Number. This will strip out all the non-alphanumeric characters. You could then easily access by Part Number no matter how many dashes, dots, etc. were entered on either system, via the index. Gets the same result as Richard (but uppercase), but it's generic - change the char string to whatever characters you want to keep, and everything else will disappear... Oh - and even when you're doing the exact match select, it's probably a good idea to select on the index at the same time - it'll speed things up even further unless PartNumber is your @ID. Cheers, Wol -- Anthony W. Youngman pi...@thewolery.demon.co.uk 'Yings, yow graley yin! Suz ae rikt dheu,' said the blue man, taking the thimble. 'What *is* he?' said Magrat. 'They're gnomes,' said Nanny. The man lowered the thimble. 'Pictsies!' Carpe Jugulum, Terry Pratchett 1998 Visit the MaVerick web-site - http://www.maverick-dbms.org Open Source Pick ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] The opposite of Export is.. sometimes not Import...
Off Topic but has anyone else noticed the Trend for the last three months is Help my boss, customer, friend wants to get ALL his columns, all his data or all his junk out of U2, and I'm not worried about getting it back in... I'm in the opposite boat, I want to leave it in there, and I'm VERY comfortable with SQL Server, I'd rather they work together than choose one technology over the other. My Question today is, if I'm writing a quick view in SQL Server I can write a nice little function to strip a field to just it's alpha numeric characters. Then I'll be using this on a webpage to strip the Input of it's non-alphanumeric characters as well, thus showing more matches when values are entered for (in this case) part numbers were entered in an odd fashion. Example: User enters 1-2-3-4 My data for a Part Number, might have 1-2-34 12-34 AND 123-4 My systems on the Intranet (Employees only) would say no matches found I'm not ok with this response. I think a direct match should be done first, but THEN additional searches are done, I even have a fuzzy one off Search using the SQL single Char replace (using _ based on the LEN of the input) and then a massive union query. However I want to do this in our UniVerse system instead, as that's where the data truly resides, and so I would hope would be the fastest place to run this type of search. I have no idea how to write an I type or Paragraph or Subroutine that will take a string in as the Variable and then pass back another string stripped to the AlphaNumeric only response so that I can search it. I'm assuming a Subroutine would do the trick and then I can use that Sub in an Dict Entry as an Type I, no? Where to begin? . Thanks for your time, - John J. Wahl Employee Owner Programmer / Analyst (Born in Microsoft, but now a U2 lover...) Tel: (330) 528-0091 Ext. 120 Fax: (330) 655-8458 Email: jw...@joseph.com Web: http://www.Joseph.com/ If the ship is not sinking why are you all jumping off? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] The opposite of Export is.. sometimes not Import...
I would probably start by making an index on the Part Number, based on an I-descriptor with an expression like this: CONVERT(OCONV(OCONV(PartNumber,'MC/N'),'MC/A'),'',PartNumber) where PartNumber is a D-pointer to the raw data field that is the Part Number. This will strip out all the non-alphanumeric characters. You could then easily access by Part Number no matter how many dashes, dots, etc. were entered on either system, via the index. Best Regards, Richard Lewis Nu Skin Enterprises On Thu, Jan 21, 2010 at 1:33 PM, John J. Wahl jw...@joseph.com wrote: Off Topic but has anyone else noticed the Trend for the last three months is Help my boss, customer, friend wants to get ALL his columns, all his data or all his junk out of U2, and I'm not worried about getting it back in... I'm in the opposite boat, I want to leave it in there, and I'm VERY comfortable with SQL Server, I'd rather they work together than choose one technology over the other. My Question today is, if I'm writing a quick view in SQL Server I can write a nice little function to strip a field to just it's alpha numeric characters. Then I'll be using this on a webpage to strip the Input of it's non-alphanumeric characters as well, thus showing more matches when values are entered for (in this case) part numbers were entered in an odd fashion. Example: User enters 1-2-3-4 My data for a Part Number, might have 1-2-34 12-34 AND 123-4 My systems on the Intranet (Employees only) would say no matches found I'm not ok with this response. I think a direct match should be done first, but THEN additional searches are done, I even have a fuzzy one off Search using the SQL single Char replace (using _ based on the LEN of the input) and then a massive union query. However I want to do this in our UniVerse system instead, as that's where the data truly resides, and so I would hope would be the fastest place to run this type of search. I have no idea how to write an I type or Paragraph or Subroutine that will take a string in as the Variable and then pass back another string stripped to the AlphaNumeric only response so that I can search it. I'm assuming a Subroutine would do the trick and then I can use that Sub in an Dict Entry as an Type I, no? Where to begin? . Thanks for your time, - John J. Wahl Employee Owner Programmer / Analyst (Born in Microsoft, but now a U2 lover...) Tel: (330) 528-0091 Ext. 120 Fax: (330) 655-8458 Email: jw...@joseph.com Web: http://www.Joseph.com/ If the ship is not sinking why are you all jumping off? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] The opposite of Export is.. sometimes not Import...
On Unidata the MCB conversion returns alphanumerics only. -Kevin (mobile) On Jan 21, 2010, at 2:06 PM, Richard Lewis rbl...@gmail.com wrote: I would probably start by making an index on the Part Number, based on an I-descriptor with an expression like this: CONVERT(OCONV(OCONV(PartNumber,'MC/N'),'MC/A'),'',PartNumber) where PartNumber is a D-pointer to the raw data field that is the Part Number. This will strip out all the non-alphanumeric characters. You could then easily access by Part Number no matter how many dashes, dots, etc. were entered on either system, via the index. Best Regards, Richard Lewis Nu Skin Enterprises On Thu, Jan 21, 2010 at 1:33 PM, John J. Wahl jw...@joseph.com wrote: Off Topic but has anyone else noticed the Trend for the last three months is Help my boss, customer, friend wants to get ALL his columns, all his data or all his junk out of U2, and I'm not worried about getting it back in... I'm in the opposite boat, I want to leave it in there, and I'm VERY comfortable with SQL Server, I'd rather they work together than choose one technology over the other. My Question today is, if I'm writing a quick view in SQL Server I can write a nice little function to strip a field to just it's alpha numeric characters. Then I'll be using this on a webpage to strip the Input of it's non-alphanumeric characters as well, thus showing more matches when values are entered for (in this case) part numbers were entered in an odd fashion. Example: User enters 1-2-3-4 My data for a Part Number, might have 1-2-34 12-34 AND 123-4 My systems on the Intranet (Employees only) would say no matches found I'm not ok with this response. I think a direct match should be done first, but THEN additional searches are done, I even have a fuzzy one off Search using the SQL single Char replace (using _ based on the LEN of the input) and then a massive union query. However I want to do this in our UniVerse system instead, as that's where the data truly resides, and so I would hope would be the fastest place to run this type of search. I have no idea how to write an I type or Paragraph or Subroutine that will take a string in as the Variable and then pass back another string stripped to the AlphaNumeric only response so that I can search it. I'm assuming a Subroutine would do the trick and then I can use that Sub in an Dict Entry as an Type I, no? Where to begin? . Thanks for your time, - John J. Wahl Employee Owner Programmer / Analyst (Born in Microsoft, but now a U2 lover...) Tel: (330) 528-0091 Ext. 120 Fax: (330) 655-8458 Email: jw...@joseph.com Web: http://www.Joseph.com/ If the ship is not sinking why are you all jumping off? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
