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

[Jonathan Ryan]

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

"Jennifer" <je**********@hotmail.com> wrote in message
news:33**************************@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