We just noticed that in a data feed that a client sends us, that how
we specified an account id to be passed was not followed exactly.
But, since they're 10,000 times larger than us and pay a lot of our
bills, it's my problem not theirs.

Here's a sample of two account IDs that would be passed to us:

000799D987654
0000799D54321

What I need to do is find an efficient way of selecting the digits to
the right of the letter in the string. It was supposed to work with a
simple right(6) to get those 6 digits, but a very few of the IDs they
sent to us only have 5 to the right of the letter.

I've experimented doing it in the web app using a regex, but with the
size of the data files it's chugging along slowly doing the updates.

I'm curious if there's a way in native sql to select those 6 (or 5 as
the case may be) direct in sql and then update those 6 (or 5) digits
to a new column in the table.

Thanks,

On Tue, 05 Feb 2008 20:49:45 +0100, Steve <steve.logan@gmail.com> wrote:

> We just noticed that in a data feed that a client sends us, that how
> we specified an account id to be passed was not followed exactly.
> But, since they're 10,000 times larger than us and pay a lot of our
> bills, it's my problem not theirs.
>
> Here's a sample of two account IDs that would be passed to us:
>
> 000799D987654
> 0000799D54321
>
> What I need to do is find an efficient way of selecting the digits to
> the right of the letter in the string. It was supposed to work with a
> simple right(6) to get those 6 digits, but a very few of the IDs they
> sent to us only have 5 to the right of the letter.
>
> I've experimented doing it in the web app using a regex, but with the
> size of the data files it's chugging along slowly doing the updates.
>
> I'm curious if there's a way in native sql to select those 6 (or 5 as
> the case may be) direct in sql and then update those 6 (or 5) digits
> to a new column in the table.

One could fidle around with SUBSTRING(), LOCATE() and CHAR_LENGTH(). This
is somewhat more transparant though:

UPDATE tablename SET field = SUBSTRING_INDEX(otherfield, 'D',-1);

--
Rik Wasmus

On Feb 5, 3:01 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 05 Feb 2008 20:49:45 +0100, Steve <steve.lo...@gmail.com> wrote:
> > We just noticed that in a data feed that a client sends us, that how
> > we specified an account id to be passed was not followed exactly.
> > But, since they're 10,000 times larger than us and pay a lot of our
> > bills, it's my problem not theirs.
>
> > Here's a sample of two account IDs that would be passed to us:
>
> > 000799D987654
> > 0000799D54321
>
> > What I need to do is find an efficient way of selecting the digits to
> > the right of the letter in the string. It was supposed to work with a
> > simple right(6) to get those 6 digits, but a very few of the IDs they
> > sent to us only have 5 to the right of the letter.
>
> > I've experimented doing it in the web app using a regex, but with the
> > size of the data files it's chugging along slowly doing the updates.
>
> > I'm curious if there's a way in native sql to select those 6 (or 5 as
> > the case may be) direct in sql and then update those 6 (or 5) digits
> > to a new column in the table.
>
> One could fidle around with SUBSTRING(), LOCATE() and CHAR_LENGTH(). This
> is somewhat more transparant though:
>
> UPDATE tablename SET field = SUBSTRING_INDEX(otherfield, 'D',-1);
>
> --
> Rik Wasmus

Thanks Rik - one more step though - that 'D' could be A-Z - I've been
trying a solution similar to yours but I can't seem to figure out the
use of REGEX to find the position in the string of the [A-Z].

On Tue, 05 Feb 2008 21:09:14 +0100, Steve <steve.logan@gmail.com> wrote:

> On Feb 5, 3:01 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Tue, 05 Feb 2008 20:49:45 +0100, Steve <steve.lo...@gmail.com> wrote:
>> > We just noticed that in a data feed that a client sends us, that how
>> > we specified an account id to be passed was not followed exactly.
>> > But, since they're 10,000 times larger than us and pay a lot of our
>> > bills, it's my problem not theirs.
>>
>> > Here's a sample of two account IDs that would be passed to us:
>>
>> > 000799D987654
>> > 0000799D54321
>>
>> > What I need to do is find an efficient way of selecting the digits to
>> > the right of the letter in the string. It was supposed to work with a
>> > simple right(6) to get those 6 digits, but a very few of the IDs they
>> > sent to us only have 5 to the right of the letter.
>>
>> > I've experimented doing it in the web app using a regex, but with the
>> > size of the data files it's chugging along slowly doing the updates..
>>
>> > I'm curious if there's a way in native sql to select those 6 (or 5 as
>> > the case may be) direct in sql and then update those 6 (or 5) digits
>> > to a new column in the table.
>>
>> One could fidle around with SUBSTRING(), LOCATE() and CHAR_LENGTH().
>> This
>> is somewhat more transparant though:
>>
>> UPDATE tablename SET field = SUBSTRING_INDEX(otherfield, 'D',-1);
>>
> Thanks Rik - one more step though - that 'D' could be A-Z - I've been
> trying a solution similar to yours but I can't seem to figure out the
> use of REGEX to find the position in the string of the [A-Z].
>

Ah, that would be tricky, regex manipulation is not a standard possibility
in MySQL.

UPDATE tablename
SET field = REVERSE(CAST(REVERSE(otherfield) AS UNSIGNED));
--
Rik Wasmus

On Feb 5, 3:18 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> On Tue, 05 Feb 2008 21:09:14 +0100, Steve <steve.lo...@gmail.com> wrote:
> > On Feb 5, 3:01 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
> >> On Tue, 05 Feb 2008 20:49:45 +0100, Steve <steve.lo...@gmail.com> wrote:
> >> > We just noticed that in a data feed that a client sends us, that how
> >> > we specified an account id to be passed was not followed exactly.
> >> > But, since they're 10,000 times larger than us and pay a lot of our
> >> > bills, it's my problem not theirs.
>
> >> > Here's a sample of two account IDs that would be passed to us:
>
> >> > 000799D987654
> >> > 0000799D54321
>
> >> > What I need to do is find an efficient way of selecting the digits to
> >> > the right of the letter in the string. It was supposed to work with a
> >> > simple right(6) to get those 6 digits, but a very few of the IDs they
> >> > sent to us only have 5 to the right of the letter.
>
> >> > I've experimented doing it in the web app using a regex, but with the
> >> > size of the data files it's chugging along slowly doing the updates.
>
> >> > I'm curious if there's a way in native sql to select those 6 (or 5 as
> >> > the case may be) direct in sql and then update those 6 (or 5) digits
> >> > to a new column in the table.
>
> >> One could fidle around with SUBSTRING(), LOCATE() and CHAR_LENGTH().
> >> This
> >> is somewhat more transparant though:
>
> >> UPDATE tablename SET field = SUBSTRING_INDEX(otherfield, 'D',-1);
>
> > Thanks Rik - one more step though - that 'D' could be A-Z - I've been
> > trying a solution similar to yours but I can't seem to figure out the
> > use of REGEX to find the position in the string of the [A-Z].
>
> Ah, that would be tricky, regex manipulation is not a standard possibility
> in MySQL.
>
> UPDATE tablename
> SET field = REVERSE(CAST(REVERSE(otherfield) AS UNSIGNED));
> --
> Rik Wasmus


This gives an error - truncated incorrect integer value:

UPDATE _temptable SET
newvalue = REVERSE(CAST(REVERSE(oldvalue) AS UNSIGNED));

In my first test, old value was: '0532J632252', and I wanted to pull
out 632252.

Maybe doing this in code is the better way of handling it this time.

On Tue, 05 Feb 2008 23:04:17 +0100, Steve <steve.logan@gmail.com> wrote:

> On Feb 5, 3:18 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> On Tue, 05 Feb 2008 21:09:14 +0100, Steve <steve.lo...@gmail.com> wrote:
>> > On Feb 5, 3:01 pm, "Rik Wasmus" <luiheidsgoe...@hotmail.com> wrote:
>> >> On Tue, 05 Feb 2008 20:49:45 +0100, Steve <steve.lo...@gmail.com>
>> wrote:
>> >> > We just noticed that in a data feed that a client sends us, that
>> how
>> >> > we specified an account id to be passed was not followed exactly..
>> >> > But, since they're 10,000 times larger than us and pay a lot of our
>> >> > bills, it's my problem not theirs.
>>
>> >> > Here's a sample of two account IDs that would be passed to us:
>>
>> >> > 000799D987654
>> >> > 0000799D54321
>>
>> >> > What I need to do is find an efficient way of selecting the digits
>> to
>> >> > the right of the letter in the string. It was supposed to work
>> with a
>> >> > simple right(6) to get those 6 digits, but a very few of the IDs
>> they
>> >> > sent to us only have 5 to the right of the letter.
>>
>> >> > I've experimented doing it in the web app using a regex, but with
>> the
>> >> > size of the data files it's chugging along slowly doing the
>> updates.
>>
>> >> > I'm curious if there's a way in native sql to select those 6 (or5
>> as
>> >> > the case may be) direct in sql and then update those 6 (or 5)
>> digits
>> >> > to a new column in the table.
>>
>> >> One could fidle around with SUBSTRING(), LOCATE() and CHAR_LENGTH().
>> >> This
>> >> is somewhat more transparant though:
>>
>> >> UPDATE tablename SET field = SUBSTRING_INDEX(otherfield, 'D',-1);
>>
>> > Thanks Rik - one more step though - that 'D' could be A-Z - I've been
>> > trying a solution similar to yours but I can't seem to figure out the
>> > use of REGEX to find the position in the string of the [A-Z].
>>
>> Ah, that would be tricky, regex manipulation is not a standard
>> possibility
>> in MySQL.
>>
>> UPDATE tablename
>> SET field = REVERSE(CAST(REVERSE(otherfield) AS UNSIGNED));
>
> This gives an error - truncated incorrect integer value:


Hehe, the truncation is actually key to way this works... At least here it
does in a SELECT statement.

> UPDATE _temptable SET
> newvalue = REVERSE(CAST(REVERSE(oldvalue) AS UNSIGNED));
>
> In my first test, old value was: '0532J632252', and I wanted to pull
> out 632252.

Ah, we have to ignore are actually desired error:

UPDATE IGNORE _temptable
SET newvalue = REVERSE(CAST(REVERSE(oldvalue) AS UNSIGNED));

> Maybe doing this in code is the better way of handling it this time.

Not a bad idea, more control.
--
Rik Wasmus

Bingo! You're the man.

I've never had to use 'ignore' before and didn't even realize that
could be used to plow through errors (sounds dangerous!).

I just ran a test across 20,000 records and it worked perfectly (and
very quick).

Thanks - have a good one.



>
> Ah, we have to ignore are actually desired error:
>
> UPDATE IGNORE _temptable
> SET newvalue = REVERSE(CAST(REVERSE(oldvalue) AS UNSIGNED));
>
> > Maybe doing this in code is the better way of handling it this time.
>
> Not a bad idea, more control.
> --
> Rik Wasmus