reg expression syntax error

#11

On 2010-09-17 18:32, jr wrote:
[...]

Quote:

it has to be padded with zeros.

IMO, this is an issue that should be dealt with in the in the
presentation layer and not in the data layer. I.e. store it as int (or
whatever type that is most appropiate), and transform it when it is
presented to the user.

/Lennart

#12

On 9/17/2010 2:21 PM, Lennart Jonsson wrote:

Quote:

On 2010-09-17 18:32, jr wrote:
[...]

it has to be padded with zeros.

IMO, this is an issue that should be dealt with in the in the
presentation layer and not in the data layer. I.e. store it as int (or
whatever type that is most appropiate), and transform it when it is
presented to the user.

/Lennart

Indeed, Lennart. How to display data is not a database issue.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#13

On Sep 16, 5:26*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

Quote:

On 9/16/2010 7:33 PM, jr wrote:

In the ndc column there were some imported rows from Excel with
scientific notation.
* I am trying to find those rows using preg_match so I can located
the . and then pad with zeros the number of the exponent but I get a
syntax error.The correct field has 11 digits and has leading zeros.
It is a char field.

select preg_match(^[0-9]{11}$,ndc) from inventory
select preg_match(^[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
[0-9]$,ndc) from inventory
thankx,

Which is just ONE of the reasons you don't want to use a char field to
store numeric data.

I told you it would have unwelcome consequences. *But, as usual, you
just make changes willy-nilly without understanding the consequences.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

I wrote this sql query and I found 163 records with the error with 11
digits in the exponent.
SELECT nationalDrugCode from inventory
where locate('.',ndc)=2 and substring('+11',ndc)=1

I am trying to figure out how to do the UPDATE query to format it as
a number with 11 digits and not as scientific notation when the data
type is a character. I could change the datatype for the query then
change it back or is there a way to cast it?
UPDATE inventory SET ndc=
format(ndc,11) ?????
SELECT ndc from inventory
wherE locate('.',ndc)=2 and substring('+11',ndc)=1
thanks,

#14

On 17-09-10 22:42, jr wrote:

Quote:

On Sep 16, 5:26 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> wrote:
On 9/16/2010 7:33 PM, jr wrote:

In the ndc column there were some imported rows from Excel with
scientific notation.
I am trying to find those rows using preg_match so I can located
the . and then pad with zeros the number of the exponent but I get a
syntax error.The correct field has 11 digits and has leading zeros.
It is a char field.

select preg_match(^[0-9]{11}$,ndc) from inventory
select preg_match(^[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
[0-9]$,ndc) from inventory
thankx,

Which is just ONE of the reasons you don't want to use a char field to
store numeric data.

I told you it would have unwelcome consequences. But, as usual, you
just make changes willy-nilly without understanding the consequences.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

I wrote this sql query and I found 163 records with the error with 11
digits in the exponent.
SELECT nationalDrugCode from inventory
where locate('.',ndc)=2 and substring('+11',ndc)=1

I am trying to figure out how to do the UPDATE query to format it as
a number with 11 digits and not as scientific notation when the data
type is a character. I could change the datatype for the query then
change it back or is there a way to cast it?
UPDATE inventory SET ndc=
format(ndc,11) ?????
SELECT ndc from inventory
wherE locate('.',ndc)=2 and substring('+11',ndc)=1
thanks,

you can also read the data back into Excel,
do the correct formatting,
and export the stuff again....

--
Luuk

#15

On Sep 17, 2:14*pm, Luuk <L... (AT) invalid (DOT) lan> wrote:

Quote:

On 17-09-10 22:42, jr wrote:

On Sep 16, 5:26 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> *wrote:
On 9/16/2010 7:33 PM, jr wrote:

In the ndc column there were some imported rows from Excel with
scientific notation.
* *I am trying to find those rows using preg_match so I can located
the . and then pad with zeros the number of the exponent but I get a
syntax error.The correct field has 11 digits and has leading zeros.
It is a char field.

select preg_match(^[0-9]{11}$,ndc) from inventory
select preg_match(^[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
[0-9]$,ndc) from inventory
thankx,

Which is just ONE of the reasons you don't want to use a char field to
store numeric data.

I told you it would have unwelcome consequences. *But, as usual, you
just make changes willy-nilly without understanding the consequences.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

I wrote this sql query and I found 163 records with the error with 11
digits in the exponent.
SELECT nationalDrugCode from inventory
where locate('.',ndc)=2 and substring('+11',ndc)=1

* I am trying to figure out how to do the UPDATE query to format it as
a number with 11 digits and not as scientific notation when the data
type is a character. *I could change the datatype for the query then
change it back or is there a way to cast it?
UPDATE inventory SET ndc=
format(ndc,11) * * * * * *?????
SELECT ndc from inventory
wherE locate('.',ndc)=2 and substring('+11',ndc)=1
thanks,

you can also read the data back into Excel,
do the correct formatting,
and export the stuff again....

--
Luuk

That might be difficult. It is exported from different files into one
extract and then I extract it from that.

#16

On Sep 17, 11:21*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

On 2010-09-17 18:32, jr wrote:
[...]

*it has to be padded with zeros.

IMO, this is an issue that should be dealt with in the in the
presentation layer and not in the data layer. I.e. store it as int (or
whatever type that is most appropiate), and transform it when it is
presented to the user.

/Lennart
It is mainly a transformation issue. It needs to relate to another

files to pull in the item_id, so it gets off the error list.
There is no user looking at the display.
It really should be fixed in mysql, and there are users that use the
mysql db directly, but I see your point if it is not a mysql issue
directly.

#17

On Sep 17, 11:35*am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

Quote:

On 9/17/2010 2:21 PM, Lennart Jonsson wrote:

On 2010-09-17 18:32, jr wrote:
[...]

* it has to be padded with zeros.

IMO, this is an issue that should be dealt with in the in the
presentation layer and not in the data layer. I.e. store it as int (or
whatever type that is most appropiate), and transform it when it is
presented to the user.

/Lennart

Indeed, Lennart. *How to display data is not a database issue.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

if there is a way to transform it with a php script I would use
preg_match if I had some idea of how to proceed with it.

#18

On Sep 17, 2:14*pm, Luuk <L... (AT) invalid (DOT) lan> wrote:

Quote:

On 17-09-10 22:42, jr wrote:

On Sep 16, 5:26 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> *wrote:
On 9/16/2010 7:33 PM, jr wrote:

In the ndc column there were some imported rows from Excel with
scientific notation.
* *I am trying to find those rows using preg_match so I can located
the . and then pad with zeros the number of the exponent but I get a
syntax error.The correct field has 11 digits and has leading zeros.
It is a char field.

select preg_match(^[0-9]{11}$,ndc) from inventory
select preg_match(^[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
[0-9]$,ndc) from inventory
thankx,

Which is just ONE of the reasons you don't want to use a char field to
store numeric data.

I told you it would have unwelcome consequences. *But, as usual, you
just make changes willy-nilly without understanding the consequences.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

I wrote this sql query and I found 163 records with the error with 11
digits in the exponent.
SELECT nationalDrugCode from inventory
where locate('.',ndc)=2 and substring('+11',ndc)=1

* I am trying to figure out how to do the UPDATE query to format it as
a number with 11 digits and not as scientific notation when the data
type is a character. *I could change the datatype for the query then
change it back or is there a way to cast it?
UPDATE inventory SET ndc=
format(ndc,11) * * * * * *?????
SELECT ndc from inventory
wherE locate('.',ndc)=2 and substring('+11',ndc)=1
thanks,

you can also read the data back into Excel,
do the correct formatting,
and export the stuff again....

--
Luuk
I could possibly download from inventory tbl to excel just these

errors, fix them, then upload them again, the trouble with that is not
being able to automate the process.

#19

On 9/17/2010 6:41 PM, jr wrote:

Quote:

On Sep 17, 11:35 am, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> wrote:
On 9/17/2010 2:21 PM, Lennart Jonsson wrote:

On 2010-09-17 18:32, jr wrote:
[...]

it has to be padded with zeros.

IMO, this is an issue that should be dealt with in the in the
presentation layer and not in the data layer. I.e. store it as int (or
whatever type that is most appropiate), and transform it when it is
presented to the user.

/Lennart

Indeed, Lennart. How to display data is not a database issue.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

if there is a way to transform it with a php script I would use
preg_match if I had some idea of how to proceed with it.

If you handled things CORRECTLY, you wouldn't need preg_match.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

reg expression syntax error

comp.databases.mysql comp.databases.mysql

Re: reg expression syntax error - 09-17-2010 , 01:21 PM

Re: reg expression syntax error - 09-17-2010 , 01:35 PM

Re: reg expression syntax error - 09-17-2010 , 03:42 PM

Re: reg expression syntax error - 09-17-2010 , 04:14 PM

Re: reg expression syntax error - 09-17-2010 , 05:35 PM

Re: reg expression syntax error - 09-17-2010 , 05:39 PM

Re: reg expression syntax error - 09-17-2010 , 05:41 PM

Re: reg expression syntax error - 09-17-2010 , 05:44 PM

Re: reg expression syntax error - 09-17-2010 , 06:51 PM