Need a function

[Jennifer]

I'm looking for a string function that is similar to the INSTR
function in VB. I haven't seen anything in the help files that I can
use. Does anyone have any suggestions?

Here's what I'm trying to do:

There is a field in a table that will look something like this -
"XXXXXX - YY".
I want to separate it on the dash and get two strings out of it -
"XXXXXX" and "YY". I'm trying to keep it all in a stored procedure
and avoid a vb script or exe.

I'm envisioning something like this:

declare @CDT datetime

select @CDT = createdatetime from imOrderHdr
where VendorCode = 'SYG' and createdatetime is not null
and status in (1,2,3)

select d.VendorStockNu mber, substring(i.Ite mDescription, 1,
instr(iItemDesc ription, '-') - 1),
substring(i.Ite mDescription, instr(iItemDesc ription, '-') + 1),
d.QtyOrdered, d.PurchasePrice , (d.QtyOrdered * d.PurchasePrice ) as
Extension
from imOrderDetail d
join imItem i on i.ItemCode = d.ItemCode
where d.CreateDateTim e = @CDT

I'd write my own function, but the computers this will be run on have
SQL 7.

Any suggestions will be appreciated.

Thanks!
Jennifer

[Jonathan Ryan]

Re: Need a function

Have a look at CHARINDEX and PATINDEX to get the positions that you need for
subsequest SUBSTRING calls to parse out your data.

"Jennifer" <jennifer1970@h otmail.com> wrote in message
news:3358f49d.0 308150815.2a818 c27@posting.goo gle.com...[color=blue]
> I'm looking for a string function that is similar to the INSTR
> function in VB. I haven't seen anything in the help files that I can
> use. Does anyone have any suggestions?
>
> Here's what I'm trying to do:
>
> There is a field in a table that will look something like this -
> "XXXXXX - YY".
> I want to separate it on the dash and get two strings out of it -
> "XXXXXX" and "YY". I'm trying to keep it all in a stored procedure
> and avoid a vb script or exe.
>
> I'm envisioning something like this:
>
> declare @CDT datetime
>
> select @CDT = createdatetime from imOrderHdr
> where VendorCode = 'SYG' and createdatetime is not null
> and status in (1,2,3)
>
> select d.VendorStockNu mber, substring(i.Ite mDescription, 1,
> instr(iItemDesc ription, '-') - 1),
> substring(i.Ite mDescription, instr(iItemDesc ription, '-') + 1),
> d.QtyOrdered, d.PurchasePrice , (d.QtyOrdered * d.PurchasePrice ) as
> Extension
> from imOrderDetail d
> join imItem i on i.ItemCode = d.ItemCode
> where d.CreateDateTim e = @CDT
>
> I'd write my own function, but the computers this will be run on have
> SQL 7.
>
> Any suggestions will be appreciated.
>
> Thanks!
> Jennifer[/color]