Need a function

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.VendorStockNumber, substring(i.ItemDescription, 1,
instr(iItemDescription, '-') - 1),
substring(i.ItemDescription, instr(iItemDescription, '-') + 1),
d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) as
Extension
from imOrderDetail d
join imItem i on i.ItemCode = d.ItemCode
where d.CreateDateTime = @.CDT

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

Any suggestions will be appreciated.

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

"Jennifer" <jennifer1970@.hotmail.com> wrote in message
news:3358f49d.0308150815.2a818c27@.posting.google.c om...
> 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.VendorStockNumber, substring(i.ItemDescription, 1,
> instr(iItemDescription, '-') - 1),
> substring(i.ItemDescription, instr(iItemDescription, '-') + 1),
> d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) as
> Extension
> from imOrderDetail d
> join imItem i on i.ItemCode = d.ItemCode
> where d.CreateDateTime = @.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