Splitting a Batch ID into two parts at a [-] ie CA232-01233 1

[Bretcutler]

I want to be able to split this type of data at the hyphen. I don't want the hyphen to be included on either part.
Here is a good example of a one. CA100231-01231H01X
I want to be able to split it into CA100231 and 01231H01X. I am using the right and left expressions now but it isn't always gonna work. Please let me know in as much detail as you can if you could. Thanks alot,
Bret

 

[tlbroadbent]

Use INSTR to identify the position of the hyphen. Then use Left and Right to obtain the substrings.

In a query:
Select
Left([BatchID], Instr([BatchID],"-&quot-1) As B1,
Right([BatchID], Len([BatchID])-Instr([BatchID],"-&quot) As B2
From tbl

In VB code:
Dim b1 As String, b2 As String
b1 = Left([BatchID], Instr([BatchID],"-&quot-1)
b2 = Right([BatchID], Len([BatchID])-Instr([BatchID],"-&quot)
Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[MichaelRed]

In code with ver 2K, you could use split:

MyVar = Split(BatchId, "-&quot

MyVar will be returned as an array with 0 or more elements (assuming that BatchId contains SOMETHING). If has zero elements, BathhId included no hyphen and the original value of batchId is in the oth element. Else, element 0 has the left most portioon of the input, and each successive element has the substrings following the hyphen characters.

As in the following:

MyBatchId = "CA100231-01231H01X"

BySplit = Split(MyBatchId, "-&quot
? UBound(BySplit)
1

for idx = 0 to ubound(BySplit): ? BySplit(Idx): Next Idx
CA100231
01231H01X
'_______________________________________________

If you always want to limit the number of substrings, this may be done by adding the optional limit to the split arg, as in:

MyBatchId = "CA100231-0123-1H0-1X"
MySplit = Split(MyBatchId, "-", 2)
? UBound(MySplit)
1
for idx = 0 to ubound(MySplit): ? MySplit(Idx): Next Idx
CA100231
0123-1H0-1X
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

Michael,

Thanks for cleaning up after me. You've done it twice today - probably worth more than the two stars I gave you.

Someday this stuff will sink in and I'll remember the new techniques you and others have taught. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Bretcutler]

thanks you both,
I am using 97 right now and used the first method but will be using 2000 soon and might try the second one. Thanks alot,
Bret
I would never have gotten that!