Scott Klarenbach <[EMAIL PROTECTED]> wrote on 05/27/2005 04:38:40
PM:
> Ya, it is a little too specific...here's why I need it.
>
> I have a client that wants to search for part numbers in his DB. The
> problem is, they come into his DB from external sources, with all
> sorts of special characters in them...
>
> So, he has fields like
> field_one!,
> fi--eld 2,
> @fi#eld__3xxx
>
> etc
>
> but, he wants to do a search for 'fieldone' and return the first one,
> 'field2' returns the second, etc...basically disregard all non-alphas
> padding every character in the search string.
>
> On 5/27/05, Eric Bergen <[EMAIL PROTECTED]> wrote:
> > I'm working on a set of UDFs for preg functions.
> >
> >
> > [EMAIL PROTECTED] wrote:
> >
> > >I have a hard time figuring out when you would use such a function. I
do
> > >not believe you will be able to duplicate this behavior without
> > >constructing your own UDF or by writing a stored procedure. BTW, why
*do*
> > >you want this function?
> > >
> > >Shawn Green
> > >Database Administrator
> > >Unimin Corporation - Spruce Pine
> > >
> > >Scott Klarenbach <[EMAIL PROTECTED]> wrote on 05/27/2005
01:30:35
> > >PM:
> > >
> > >
> > >
> > >>I'm trying to replicate this PHP behavior in a MySQL stored
procedure.
> > >> The purpose is to pad every character of the string with a pad
> > >>character. For example, if the pad character is 'x' and the string
is
> > >>'STRING', the result is 'xSxTxRxIxNxGx'.
> > >>
> > >>Here is the PHP code if it helps. I'd like to use a regular
> > >>expression to replace, but I guess I could loop through the string
> > >>char by char and build a new one, it's just less elegant. Thanks in
> > >>advance.
> > >>
> > >>PHP:
> > >>-------------
> > >>$regPattern = implode('x', preg_split('//', "STRING", -1,
> > >>PREG_SPLIT_NO_EMPTY));
> > >>
Thank you very much. I find this whole "padding" process very
counterintuitive. I have a few minor questions, if you don't mind. How is
'xFxIxExLxDx1x' easier to search than 'FIELD1'? Would you, could you
please explain the theory behind why and when this kind of padding should
be done? What problem does it solve and how is it a solution to that
problem? This is completely baffling to me and I thought I had seen a lot
of weird data before :-)
However, It seems to me that this kind of data manipulation (cleanup)
needs to happen BEFORE the data enters the databse. What data import
tool/process is your client using? Can you not change the import process
to scrub the data and does it not have a better facility to interleave
padding into a string than a MySQL stored procedure or UDF?
Thanks for you patience!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
