Formatting a field in a database

Hi,
I want to format an address field that is in a database as 123 Happy Street|
|Montgomery|AL|36104
I want to break it out into address1, address2, etc without the pipes.
I found some code that seems to fix my problem below pretty well, but about
half the time it doesn't work. I get an error message when I run it that
says - An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Cannot read the next data row for the data set DAIS.
(rsErrorReadingNextDataRow) Get Online Help
Invalid length parameter passed to the substring function.
The following is part my code. Can anybody PLEASE help me figure why this
doesn't always work or tell me a better way to do it?
Thanks
Jill
SELECT TOP 1 SUBSTRING(NAMEAttributes1.Attribute, 0, CHARINDEX('|',
NAMEAttributes1.Attribute, 0)) AS Addr1, SUBSTRING(NAMEAttributes1.Attribute,
CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1,
CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1)
- (CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1))
AS Addr2, SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute,
CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1,
CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute,
CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1)
- CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) - 1)
AS City, SUBSTRING(NAMEAttributes1.Attribute,
CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) + 1) + 1,
CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) - CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) + 1) - 1) AS State,
SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|',
NAMEAttributes1.Attribute, CHARINDEX('|', NAMEAttributes1.Attribute,
CHARINDEX('|',
NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) + 1, 5)
AS ZIP,Suppose that you wouldn't want to rethink how you are keeping your
address.....
Anyway, the code you have should work as long as you can absolutely
guarantee that your addresses will be in the desired format. If you are
missing one pipe as an example you will end up with the error message that
you supplied. I think that is probably your problem and that you need to
clean up the data.
Note that you could work around thie problem of missing pipes in a stored
proc except for the fact that if a pipe is missing there's no way to know
exactly what piece of data is missing. With a normal table structure holding
the address in separate fields this would not be an issue.
"Jill" wrote:
> Hi,
> I want to format an address field that is in a database as 123 Happy Street|
> |Montgomery|AL|36104
> I want to break it out into address1, address2, etc without the pipes.
> I found some code that seems to fix my problem below pretty well, but about
> half the time it doesn't work. I get an error message when I run it that
> says - An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> Cannot read the next data row for the data set DAIS.
> (rsErrorReadingNextDataRow) Get Online Help
> Invalid length parameter passed to the substring function.
> The following is part my code. Can anybody PLEASE help me figure why this
> doesn't always work or tell me a better way to do it?
> Thanks
> Jill
> SELECT TOP 1 SUBSTRING(NAMEAttributes1.Attribute, 0, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0)) AS Addr1, SUBSTRING(NAMEAttributes1.Attribute,
> CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1,
> CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1)
> - (CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1))
> AS Addr2, SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute,
> CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1,
> CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute,
> CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1)
> - CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) - 1)
> AS City, SUBSTRING(NAMEAttributes1.Attribute,
> CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) + 1) + 1,
> CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) - CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) + 1) - 1) AS State,
> SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|',
> NAMEAttributes1.Attribute, CHARINDEX('|', NAMEAttributes1.Attribute,
> CHARINDEX('|',
> NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) + 1, 5)
> AS ZIP,
>|||Perhaps you can use this code in Reporting Service.
=Split(Fields!Address.Value, "|")(0)
=Split(Fields!Address.Value, "|")(1)
=Split(Fields!Address.Value, "|")(2)
=Split(Fields!Address.Value, "|")(3)
Hope this helps ..
> "Jill" wrote:
> > Hi,
> > I want to format an address field that is in a database as 123 Happy Street|
> > |Montgomery|AL|36104
> > I want to break it out into address1, address2, etc without the pipes.
> >
> > I found some code that seems to fix my problem below pretty well, but about
> > half the time it doesn't work. I get an error message when I run it that
> > says - An error has occurred during report processing. (rsProcessingAborted)
> > Get Online Help
> > Cannot read the next data row for the data set DAIS.
> > (rsErrorReadingNextDataRow) Get Online Help
> > Invalid length parameter passed to the substring function.
> >
> > The following is part my code. Can anybody PLEASE help me figure why this
> > doesn't always work or tell me a better way to do it?
> >
> > Thanks
> > Jill
> >
> > SELECT TOP 1 SUBSTRING(NAMEAttributes1.Attribute, 0, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0)) AS Addr1, SUBSTRING(NAMEAttributes1.Attribute,
> > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1,
> > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1)
> > - (CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1))
> > AS Addr2, SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute,
> > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1,
> > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute,
> > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1)
> > - CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) - 1)
> > AS City, SUBSTRING(NAMEAttributes1.Attribute,
> > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1,
> > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) - CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) + 1) - 1) AS State,
> > SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|',
> > NAMEAttributes1.Attribute, CHARINDEX('|', NAMEAttributes1.Attribute,
> > CHARINDEX('|',
> > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) + 1, 5)
> > AS ZIP,
> >|||Thank You, Thank You!! This worked!!
"Keenan" wrote:
> Perhaps you can use this code in Reporting Service.
> =Split(Fields!Address.Value, "|")(0)
> =Split(Fields!Address.Value, "|")(1)
> =Split(Fields!Address.Value, "|")(2)
> =Split(Fields!Address.Value, "|")(3)
> Hope this helps ..
> > "Jill" wrote:
> >
> > > Hi,
> > > I want to format an address field that is in a database as 123 Happy Street|
> > > |Montgomery|AL|36104
> > > I want to break it out into address1, address2, etc without the pipes.
> > >
> > > I found some code that seems to fix my problem below pretty well, but about
> > > half the time it doesn't work. I get an error message when I run it that
> > > says - An error has occurred during report processing. (rsProcessingAborted)
> > > Get Online Help
> > > Cannot read the next data row for the data set DAIS.
> > > (rsErrorReadingNextDataRow) Get Online Help
> > > Invalid length parameter passed to the substring function.
> > >
> > > The following is part my code. Can anybody PLEASE help me figure why this
> > > doesn't always work or tell me a better way to do it?
> > >
> > > Thanks
> > > Jill
> > >
> > > SELECT TOP 1 SUBSTRING(NAMEAttributes1.Attribute, 0, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0)) AS Addr1, SUBSTRING(NAMEAttributes1.Attribute,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1)
> > > - (CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1))
> > > AS Addr2, SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, 0) + 1) + 1)
> > > - CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) - 1)
> > > AS City, SUBSTRING(NAMEAttributes1.Attribute,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1,
> > > CHARINDEX('|', NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) - CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) + 1) - 1) AS State,
> > > SUBSTRING(NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|',
> > > NAMEAttributes1.Attribute, CHARINDEX('|', NAMEAttributes1.Attribute,
> > > CHARINDEX('|',
> > > NAMEAttributes1.Attribute, 0) + 1) + 1) + 1) + 1, 5)
> > > AS ZIP,
> > >