-- How is 'xFxIxExLxDx1x' easier to search than 'FIELD1'?
It's not easier. It is; however, accurate for the purpose at hand.
FIELD1 isn't completely inclusive...it would miss FIELD-1...
-- However, It seems to me that this kind of data manipulation
(cleanup) needs to happen BEFORE the data enters the databse.
Sometimes FIELD-1 is the ACTUAL data, with no erroneus
characters...and sometimes it is FIELD1 with an erroneous (or
unwanted) - character before the 1)...so cleaning the data would
actually be corrupting some of it.
I need to keep the data in it's original form, but also allow for
querying without worrying about the special characters inside the
column.
FYI, these are part numbers off of electronic components, many of them
coming from China...so, a Cisco part may have an MPN of RX321, or
RX321-TR...either is valid. Now, the corresponding Chinese part
number for the first one, may come back as RX32-1...which is out of my
control.
Cleaning the data would be the wrong approach, because it would
actually invalidate the second Cisco part number, which MEANT to
include the special characters.
For this reason, the user wants to be able to search for 'RX321' and
'RX321TR' respectively, and not worry about whether the data is
erroneous or valid; just to basically ignore all the characters and
let a human decide what they want.
If you have a more elegant solution, I'm all ears :-).
On 5/27/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
>
> 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
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
