|
|
Posted by Scott Marquardt on 10/09/05 09:59
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
Navigation:
[Reply to this message]
|