parsing varchar fields

What are some good strategic approaches to using freeform text fields for
data that needs to be queried? We have a product whose tables we can't
change, and I need to count on a "description" field for storing a value.
Two, actually. I'm thinking of adopting this convention:
InvoiceNumber@.VendorAcronym
There'd be a lot of vendors.
Additional issue: sometimes these values would be referred to in the
description field, and I'd need to distinguish them as referrals rather
than as original recorded instances of the values. For that, I imagined
either:
InvoiceNumber@.@.VendorAcronym
or
InvoiceNumber&VendorAcronym
InvoiceNumber//VendorAcronym
etc. -- something like that.
I'm just wondering if there's best practice for doing anything this stupid
(hey, I'm stuck with this as our only option just now; hopefully it's only
temporary). How to parse out whatever I end up implementing -- well, it
needs to be tractable.
Thoughts?
ScottScott,
Instead of changing the existing product's tables why not create an
additional table(s) to store these values and then write your queries using
joins.
HTH
Jerry
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to do, but creating a concatenated field
is probably not a good idea. Separate fields and a status will be far easier
to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Hi
It is not clear what you are trying to achieve, but creating a concanenated
field is probably not a good idea. Separate fields and a status will be alot
easier to maintain and understand.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127537539. cb0c208b60e605e1a298926bee12e52f@.teranew
s...
> What are some good strategic approaches to using freeform text fields for
> data that needs to be queried? We have a product whose tables we can't
> change, and I need to count on a "description" field for storing a value.
> Two, actually. I'm thinking of adopting this convention:
> InvoiceNumber@.VendorAcronym
> There'd be a lot of vendors.
> Additional issue: sometimes these values would be referred to in the
> description field, and I'd need to distinguish them as referrals rather
> than as original recorded instances of the values. For that, I imagined
> either:
> InvoiceNumber@.@.VendorAcronym
> or
> InvoiceNumber&VendorAcronym
> InvoiceNumber//VendorAcronym
> etc. -- something like that.
> I'm just wondering if there's best practice for doing anything this stupid
> (hey, I'm stuck with this as our only option just now; hopefully it's only
> temporary). How to parse out whatever I end up implementing -- well, it
> needs to be tractable.
> Thoughts?
> --
> Scott|||Jerry Spivey opined thusly on Sep 24:
> Scott,
> Instead of changing the existing product's tables why not create an
> additional table(s) to store these values and then write your queries usin
g
> joins.
Jerry, John -- sorry for the lack of clarity.
We have a web app from a vendor, and in this particular case I don't want
to hack anything (I've done some hacks where practical, and will be doing
more, but there's some intractable stuff in view of upgrade management and
my available time).
Our people need to reference invoice numbers and vendors in a description
field, so I'm wanting to accommodate them until such time comes along that
the software supports something like that. But they're wanting to be able
to generate reports on the vendor and invoice numbers, so I'm wanting to do
this kludgey thing as elegantly as kludges can be. I figured if I specified
that they enter the two fields in some string format that was simple enough
for them this would be practical from their standpoint, and if I could
parse it it'd be practical from mine. The concatenation John's concerned
about is mostly as a way of avoiding separate ways of identifying the
"fields". That would be a bit onerous for the staff entering the
information, even if it did make parsing easier.
So imagine a lot of records with brief descriptions of invoices, with
vendor and invoice #s embedded in the descriptions. Oh, yeah, almost forgot
-- no chance of their being more than one of these per description (per
record).
Scott|||Hi
I am not sure why you think the users would want to enter some cryptic code
rather than exact values in two separate fields. That is more of a usability
with the UI, for instance if your parts were both numeric you may be slowing
your users down forcing them to use non-keypad characters that require
shifting. You should also look at the business processes, for instance if
you processed everything for one vendor together then retaining the vendor
information would remove the need to type it in again.
If you need to concatenate the values for display purposes then you can do
that in the code for your stored procedure or create a view.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127546922. 53434faa9cedec41603cbc091e9f06a8@.teranew
s...
> Jerry Spivey opined thusly on Sep 24:
> Jerry, John -- sorry for the lack of clarity.
> We have a web app from a vendor, and in this particular case I don't want
> to hack anything (I've done some hacks where practical, and will be doing
> more, but there's some intractable stuff in view of upgrade management and
> my available time).
> Our people need to reference invoice numbers and vendors in a description
> field, so I'm wanting to accommodate them until such time comes along that
> the software supports something like that. But they're wanting to be able
> to generate reports on the vendor and invoice numbers, so I'm wanting to
> do
> this kludgey thing as elegantly as kludges can be. I figured if I
> specified
> that they enter the two fields in some string format that was simple
> enough
> for them this would be practical from their standpoint, and if I could
> parse it it'd be practical from mine. The concatenation John's concerned
> about is mostly as a way of avoiding separate ways of identifying the
> "fields". That would be a bit onerous for the staff entering the
> information, even if it did make parsing easier.
> So imagine a lot of records with brief descriptions of invoices, with
> vendor and invoice #s embedded in the descriptions. Oh, yeah, almost
> forgot
> -- no chance of their being more than one of these per description (per
> record).
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> I am not sure why you think the users would want to enter some cryptic cod
e
> rather than exact values in two separate fields.
Developing more fields in this N-tier application whose upgrades are
controlled by a vendor whose dev I'm not privy to, isn't an option. If I
could do that, I would. I'm not posting to inquire about better
alternatives; I'm wanting to wring juice out of an unfortunate turnip.

> That is more of a usability
> with the UI, for instance if your parts were both numeric you may be slowi
ng
> your users down forcing them to use non-keypad characters that require
> shifting. You should also look at the business processes, for instance if
> you processed everything for one vendor together then retaining the vendor
> information would remove the need to type it in again.
> If you need to concatenate the values for display purposes then you can do
> that in the code for your stored procedure or create a view.
Again, beyond my control. I've done a few necessary hacks of the software
(both the asp and some sprocs), but I can't expand the number of such edits
beyond reason. Next vendor upgrade, I'd be flailing about.
The users are more worried about being able to have reports on data they're
unfortunately left to enter in a free-form description field, type varchar.
My posting is to inquire whether a string formatting convention I'm
considering is the best I can do under that circumstance. I'd be glad of
any hints at how to most efficiently query the data as well.
Scott|||Hi
If you are not writing a new interface then you are probably better using a
fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part is
a fixed length then you don't need a separator, but it may help the users
differentiate the different parts. I would have expect that you can
determine the fact that an invoice is a referral by a different means.
John
"Scott Marquardt" <wasREMOVEket5@.hotmail.com> wrote in message
news:1127575738. f429cee989a6daeb985244c70f0bb497@.teranew
s...
> John Bell opined thusly on Sep 24:
> Developing more fields in this N-tier application whose upgrades are
> controlled by a vendor whose dev I'm not privy to, isn't an option. If I
> could do that, I would. I'm not posting to inquire about better
> alternatives; I'm wanting to wring juice out of an unfortunate turnip.
>
> Again, beyond my control. I've done a few necessary hacks of the software
> (both the asp and some sprocs), but I can't expand the number of such
> edits
> beyond reason. Next vendor upgrade, I'd be flailing about.
> The users are more worried about being able to have reports on data
> they're
> unfortunately left to enter in a free-form description field, type
> varchar.
> My posting is to inquire whether a string formatting convention I'm
> considering is the best I can do under that circumstance. I'd be glad of
> any hints at how to most efficiently query the data as well.
>
> --
> Scott|||John Bell opined thusly on Sep 24:
> Hi
> If you are not writing a new interface then you are probably better using
a
> fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If each part i
s
> a fixed length then you don't need a separator, but it may help the users
> differentiate the different parts. I would have expect that you can
> determine the fact that an invoice is a referral by a different means.
Well, we could get into "I would have expected" exchanges, or we can skip
the stuff I already know -- and posted in the original post. With all due
respect, "duh."
Good grief. Outta here.
Scott|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you are not writing a new interface then you are probably better
> using a fixed notation. Such as InvoiceNumber/VendorAcronym/Status. If
> each part is a fixed length then you don't need a separator, but it may
> help the users differentiate the different parts. I would have expect
> that you can determine the fact that an invoice is a referral by a
> different means.
Now, how is that for a user interface? This is free text, and users will
mangle it anyway, but they have better odds with a separator than fixed
length.
I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.
Hm, possibly could have a trigger on the table that loaded a new table
with the fields split in pieces. Still iffy with regards to the vendor,
but at least no existing object is changed. (Then again, being on the
vendor side of the fence, I know what I would think if I found that one
of our customers had added a trigger to one of our tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp