By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,292 Members | 1,354 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,292 IT Pros & Developers. It's quick & easy.

parsing varchar fields

P: n/a
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
Sep 24 '05 #1
Share this Question
Share on Google+
21 Replies


P: n/a
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 using
joins.

HTH

Jerry
"Scott Marquardt" <wa***********@hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@t eranews...
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

Sep 24 '05 #2

P: n/a
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" <wa***********@hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@t eranews...
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

Sep 24 '05 #3

P: n/a
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" <wa***********@hotmail.com> wrote in message
news:1127537539.cb0c208b60e605e1a298926bee12e52f@t eranews...
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

Sep 24 '05 #4

P: n/a
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 using
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
Sep 24 '05 #5

P: n/a
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" <wa***********@hotmail.com> wrote in message
news:1127546922.53434faa9cedec41603cbc091e9f06a8@t eranews...
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
using
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

Sep 24 '05 #6

P: n/a
John Bell opined thusly on Sep 24:
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.
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 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.


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
Sep 24 '05 #7

P: n/a
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" <wa***********@hotmail.com> wrote in message
news:1127575738.f429cee989a6daeb985244c70f0bb497@t eranews...
John Bell opined thusly on Sep 24:
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.


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
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.


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

Sep 24 '05 #8

P: n/a
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 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.


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
Sep 24 '05 #9

P: n/a
John Bell (jb************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Sep 24 '05 #10

P: n/a
Scott Marquardt opined thusly on Sep 24:
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 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.


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.


John., I'm going to apologize for a short temper there. It was born of
impatience.

My principal interest was in hearing how anyone else might PARSE the field
to recover the data. Erland's trigger idea isn't bad for WHAT I do with it.

"Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@WidgetMart"

"Refund for can of beans, 100-3123&WidgetMart"

The second one would need to be tagged as a referral to the first one. But
if that's my basic technique, they're parse out the same way.

--

Scott
Sep 25 '05 #11

P: n/a
>> My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@WidgetMart" <<

Since free text requires human intelligence, we get people to encode
it. If the text is short and styled the same way, you can find AI
programs that are pretty good these days. Of course this has nothing
to do with RDBMS and nobody in their right mind would try to do it in
SQL.

Sep 25 '05 #12

P: n/a
Stu
Scott,

Have you considered an ActiveX script in a DTS package to shred the
data periodically (you can use the SQL Scheduler to run it once a
minute). We do this to parse out syslog tables; it's kludgy, but its
consistent. While not as immediate as a trigger to populate a table,
it does keep you from modifying the vendor database (which, if your
vendor has the ability to update their database, could be a big
benefit).

Obviously, your users would have to be trained on how to enter data
consistently, and you'd probably want some sort of validation to pick
up on coded entries that don't meant your
data-followed-by-a-comma-followed-by-more-data rules. You may even
look at Regular Expressions inside your DTS package to help with that.

Just thinking aloud,
Stu

Sep 25 '05 #13

P: n/a
Hi Erland/Steve

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
John Bell (jb************@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.


That was my point, guess I should have said it that way! The users will
(probably) not like having to enter a coded string, even if you keep it as
simple! They may even deliberately mangle it if there is no value in it for
themselves.

I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.
It seems to me, that if this an off the shelf package the benefits has been
lost some time ago, if this is a bespoke development then it has not been
specified thoroughly enough.
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.)
Writing a trigger may still invalidate the contract as the transactions will
be effected, the vendor may have the right to charge improportate fees to
fix a problem even if a problem is not related to this table. But if Steve
has already tweeked things that could already be the case! A trigger would
be useful for validation even if the replication route is not taken. Then
again Steve was worried about upgrades breaking things.

Dire may be an understatement!

Steve has my sympathy.

John

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Sep 25 '05 #14

P: n/a
Nothing seems to be going right in this thread!

Who is Steve?

Sorry Scott!

John

"John Bell" <jb************@hotmail.com> wrote in message
news:43***********************@news.zen.co.uk...
Hi Erland/Steve

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
John Bell (jb************@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.


That was my point, guess I should have said it that way! The users will
(probably) not like having to enter a coded string, even if you keep it as
simple! They may even deliberately mangle it if there is no value in it
for themselves.

I have not intervened into this thread before, because I don't have
much advice to offer Steve in this dire situation.

It seems to me, that if this an off the shelf package the benefits has
been lost some time ago, if this is a bespoke development then it has not
been specified thoroughly enough.
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.)


Writing a trigger may still invalidate the contract as the transactions
will be effected, the vendor may have the right to charge improportate
fees to fix a problem even if a problem is not related to this table. But
if Steve has already tweeked things that could already be the case! A
trigger would be useful for validation even if the replication route is
not taken. Then again Steve was worried about upgrades breaking things.

Dire may be an understatement!

Steve has my sympathy.

John

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Sep 25 '05 #15

P: n/a
Hi Scott

Non taken!

You can't always give good advice, sometimes the best you can do is throw
ideas into the pot.

Using a scheduled job may be less of an preblem when considering
invalidating the contract! Although running it once a minute may cause
issues such as blocking.

I have assumed that the information you are entering is not available on
this system, therefore it is not possible to pick it up at report time from
other fields.

John
"Scott Marquardt" <wa***********@hotmail.com> wrote in message
news:1127610853.841b341db0737c3d0f8b5850b289ecae@t eranews...
Scott Marquardt opined thusly on Sep 24:
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
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.


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.


John., I'm going to apologize for a short temper there. It was born of
impatience.

My principal interest was in hearing how anyone else might PARSE the field
to recover the data. Erland's trigger idea isn't bad for WHAT I do with
it.

"Invoice for 1/2 inch puce widgets and a can of beans,
100-3123@WidgetMart"

"Refund for can of beans, 100-3123&WidgetMart"

The second one would need to be tagged as a referral to the first one. But
if that's my basic technique, they're parse out the same way.

--

Scott

Sep 25 '05 #16

P: n/a
--CELKO-- opined thusly on Sep 24:
My principal interest was in hearing how anyone else might PARSE the field to recover the data. Erland's trigger idea isn't bad for WHAT I do with it. "Invoice for 1/2 inch puce widgets and a can of beans, 100-3123@WidgetMart" <<


Since free text requires human intelligence, we get people to encode
it. If the text is short and styled the same way, you can find AI
programs that are pretty good these days. Of course this has nothing
to do with RDBMS and nobody in their right mind would try to do it in
SQL.


Hey! How'd you find out about my condition?

;-)

--

Scott
Sep 25 '05 #17

P: n/a
Stu opined thusly on Sep 24:
Scott,

Have you considered an ActiveX script in a DTS package to shred the
data periodically (you can use the SQL Scheduler to run it once a
minute). We do this to parse out syslog tables; it's kludgy, but its
consistent. While not as immediate as a trigger to populate a table,
it does keep you from modifying the vendor database (which, if your
vendor has the ability to update their database, could be a big
benefit).

Obviously, your users would have to be trained on how to enter data
consistently, and you'd probably want some sort of validation to pick
up on coded entries that don't meant your
data-followed-by-a-comma-followed-by-more-data rules. You may even
look at Regular Expressions inside your DTS package to help with that.

Just thinking aloud,
Stu


Less intrusive, yeah. I'll be needing to roll my own shredder, though --
unless you'd care to name some good controls. Haven't tried any, ever,
really. It's been nice to have never needed to!

I anticipate we'll see now more than 3000 such records a year, so this is
probably something that could be done at runtime. Users won't be running
anything that queries a result of this, so parsing it all out when running
reports (not sure where to implement that, yet, but it won't be in the
application itself) is an inelegant but tenable option.

--

Scott
Sep 25 '05 #18

P: n/a
John Bell opined thusly on Sep 25:
That was my point, guess I should have said it that way! The users will
(probably) not like having to enter a coded string, even if you keep it as
simple! They may even deliberately mangle it if there is no value in it for
themselves.


In this case, the only users needing to enter these have the only stake in
these particular data points. This isn't something they'll grumble about;
in this case, they're the ones demanding it. ;-)

The vendor's likely to be cool with this. I'm documenting things pretty
well, and a few of my other hacks to the system may end up as features.
They have an aggressive development cycle and are responsive. Best of
worlds for trying stuff.

--

Steve ;-)
Sep 25 '05 #19

P: n/a
Scott Marquardt at wit's-end:
Less intrusive, yeah. I'll be needing to roll my own shredder, though --
unless you'd care to name some good controls. Haven't tried any, ever,
really. It's been nice to have never needed to! I anticipate we'll see now more than 3000 such records a year, so this is
probably something that could be done at runtime. Users won't be running
anything that queries a result of this, so parsing it all out when running
reports (not sure where to implement that, yet, but it won't be in the
application itself) is an inelegant but tenable option.


More stuff to drive you even crazier - Log Parser from Microsoft:
http://www.microsoft.com/technet/scr...r/default.mspx

It'll either drive you over the edge, or restore your sanity.
GeoSynch
Sep 27 '05 #20

P: n/a
GeoSynch opined thusly on Sep 27:
Scott Marquardt at wit's-end:
Less intrusive, yeah. I'll be needing to roll my own shredder, though --
unless you'd care to name some good controls. Haven't tried any, ever,
really. It's been nice to have never needed to!

I anticipate we'll see now more than 3000 such records a year, so this is
probably something that could be done at runtime. Users won't be running
anything that queries a result of this, so parsing it all out when running
reports (not sure where to implement that, yet, but it won't be in the
application itself) is an inelegant but tenable option.


More stuff to drive you even crazier - Log Parser from Microsoft:
http://www.microsoft.com/technet/scr...r/default.mspx

It'll either drive you over the edge, or restore your sanity.


No. You don't understand. We're so understaffed and overbooked that I could
have this operating right now somewhere, in a scheduled task or a script,
and I might not even know about it. I can't begin to count the number of
things we've been grateful for and implemented without documenting it.

That's the world of education, folks! ;-)

Hey! Download to the usual folder asks whether to overwrite. Guess I've got
it already! ;-)

--

Scott
Sep 27 '05 #21

P: n/a
Scott Marquardt opined thusly on Sep 25:
John Bell opined thusly on Sep 25:
That was my point, guess I should have said it that way! The users will
(probably) not like having to enter a coded string, even if you keep it as
simple! They may even deliberately mangle it if there is no value in it for
themselves.


In this case, the only users needing to enter these have the only stake in
these particular data points. This isn't something they'll grumble about;
in this case, they're the ones demanding it. ;-)


OK, this is maybe (maybe) the kludgiest thing I've done in a while. If
anyone has any ideas for cleaning it up, I'm all ears. It works, though.

| select
| case
| when charindex(' ',ltrim(rtrim(comment))) < charindex('@',ltrim(rtrim(comment))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| right(left(comment, charindex('@',ltrim(rtrim(comment)))-1) ,charindex(' ', reverse(left( ltrim(rtrim(comment)), charindex('@',ltrim(rtrim(comment)))-2))))
| else
| left( ltrim(rtrim(comment)), charindex('@',ltrim(rtrim(comment)))-1)
| end
| as InvoiceNumber,
| case
| when charindex(' ',ltrim(rtrim(reverse(comment)))) < charindex('@',ltrim(rtrim(reverse(comment)))) and charindex(' ',ltrim(rtrim(comment))) <> 0 then
| left(right(comment, charindex('@',ltrim(rtrim(reverse(comment))))-1),charindex(' ',ltrim(rtrim(reverse(comment)))))
| else
| reverse(left( ltrim(rtrim(reverse(comment))), charindex('@',ltrim(rtrim(reverse(comment))))-1))
| end
| as VendorID, case when charindex('@@',comment) > 0 then 'True' else 'False' end as Reference, comment
| from bill_lineitems
| where comment like '%@%'

That translates into this:

This allows for putting an invoice@vendor (or invoice@@vendor) anywhere in
a description field, so long as it's set off on either side by spaces or
appears at the very beginning or end of the string. You can't, for example,
use it at the end of a grammatical sentence and append a period.

These work:

invoice@vendor yada yada yada
yada yada yada invoice@vendor
yada yada yada invoice@vendor yada yada yada
yada yada yada invoice@vendor yada yada yada
invoice@vendor
invoice@vendor [with spaces at the end as well]

These will not work:

There are problems with this invoice@vendor!
Use the invoice that was not paid (invoice@vendor)

Also, you can't use more than one such thing in a single record's
description.

--

Scott
Oct 9 '05 #22

This discussion thread is closed

Replies have been disabled for this discussion.