seperate a string 1

[tomk01]

I have a field that looks like this: E(1),D(AB),E(12345),...

I need to pull that data into a temp table that looks like this:
DET Detcode
E 1
D AB
E 12345

I was thinking somthing like this:

select left(field,1)as detcode
into temptabel
from table;

select mid(field,3,1)
into temptable
from table
where mid(field,5,1) = ","

the problem with that is it will take a tremendous code to cover all possibilities.

any help is very appreciated

 

[PHV]

I'm afraid you have to write your own function to do that.
If ac2k or above, take a look at the Split function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jdegeorge]

Let's work backwards. How does the field get populated to begin with? Could you change the input source/method so that you don't wind up with E(1),D(AB),E(12345),...?

Jim DeGeorge

 

[Golom]

tomk01

There is this ... but it's definitely not for the feint of heart

SELECT 
 Mid$("," & I1.Instring & ",", S1.num + 1 , S2.num - S1.num - 1) AS [SubString]

FROM InputStrings AS I1, Integers AS S1, Integers AS S2

WHERE Mid$("," & I1.instring & "," ,S1.num , 1 ) = ','
      and Mid$("," & I1.instring & "," ,S2.num , 1 ) = ','
      and S1.num+1 < S2.num
      and instr(1,Mid$("," & I1.instring & ",", S1.num + 1 , S2.num - S1.num - 1),",")=0;

Where
"InputStrings" is the table containing your fields
"Instring" is the field containing the comma delimited strings
"Integers" is a table containing one field (Num) with values from 1 to 2 more than the length of the longest string in "Instring"

In Your case, That will give you output of the form

E(1)
D(AB)
E(12345)

Then you can process that with another query of the form

Select (left$(SubString,instr(1,substring,"(")-1) As [DET],
       (mid$(SubString,instr(1,substring,"(")+1 , 
                       instr(1,substring,")")-1) ) As [DetCode]

From ... etc. ...

 

[tomk01]

I dont understand what you are doing with the integers. Will you please explain the logic?

 

[Golom]

It uses two copies of the integers table to move along the string examining substrings. The first copy of integers (called S1) looks at the first character of a substring and S2 looks at the last one.

The various WHERE clauses eliminate those combinations that don't give valid string comparisons. Specifically

Mid$("," & I1.instring & "," ,S1.num , 1 ) = ','
Restricts the first character of a substring to a comma.

Mid$("," & I1.instring & "," ,S2.num , 1 ) = ','
does the same thing for the last character

S1.num+1 < S2.num
ignores records where the first character (S1) is equal to or after (i.e. >=) the last (S2) character.

Instr(1,Mid$("," & I1.instring & ",", S1.num + 1 , S2.num - S1.num - 1),",")=0
gets rid of strings where there is an embedded comma between the first and last characters.


Each String processed will (because of the joins with the integers table) produce one record for each comma-delimited substring in the source.

Hope that makes a bit clearer.

 

[tomk01]

I guess I dont understand the Integers table. am I to create a table called "integers"? Im having problems with the From Statement

 

[Golom]

Yes. You Create an Integers table

CREATE TABLE Integers (num Long CONSTRAINT myConstraint PRIMARY KEY)

and then load the table with integer values from 1 to two more than the length of the longest string in your table.

The FROM statement just forms the cartesian product or cross-join of three tables (two of which are different copies of the integers table. Thus, if your source table (InputStrings in the example) had 10 rows and Integers had 30 values from 1 to 30 then the query would produce 9000 (10 * 30 * 30) records ... most of which will be eliminated by the WHERE clause.

 

[tomk01]

ok, I finally got this to work but it is not returning any data. I think the problem may have somthing to do with the filed "memo1". when I go into the design view, I see this is not a string like I thoug but it is a Memo. does that change anythang? Also, Access is prompting me for parameters. Is there a way to put in a wild card for the param's?

here is the statement that finally worked:


SELECT Mid$("," & I1.Memo1 & ",", s1.num + 1, s2.num - s1.num -1) AS [Substring]
FROM CTBL as I1, TempInt1 AS S1, TempInt1 AS S2
WHERE Mid$ ("," & I1.Memo1 & ",", s1.num, 1) =','
AND Mid$ ("," & I1.Memo1 & ",", s2.num, 1) =','
AND s1.num+1 < s2.num
AND instr(1,Mid$("," & I1.Memo1 & ",", S1.num +1, S2.num - s1.num -1),",")=0

 

[lespaul]

What parameter is the prompt asking for?

Did you name the field in table INTEGERS 'num'?


Leslie

 

[Golom]

Just tested with a Memo field and it does seem to work.

Access decides to prompt for a parameter value when it encounters something that it can't recognize as a valid field value. The implication is that something in your query is not a valid field name in your table.

What is it prompting for?

I1.Memo1 ? - Table doesn't contain a field named Memo1
S1.Num or S2.Num ? - TempInt1 doesn't have a "Num" field

 

[tomk01]

it is asking for a number. the table is named "TempInt1" and the field is "S1

 

[tomk01]

it is prompting for S1.Num or S2.Num. when i change it to "num" I get "Invalid Procedure Call

 

[PHV]

the table is named "TempInt1" and the field is "S1"
So replace S1.num by S1.S1 and S2.num by S2.S1
Or rename the S1 field to num.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

OK ... here's the problem

You should have a "TempInt1" table with One field named "Num"

"S1" and "S2" are table aliases assigned by the query to different copies of the "TempInt1" table. They are not field names in the table.

That's what the shorthand TempInt1 As S1 and TempInt1 As S2 does.

 

[tomk01]

Should I have a Primary Key? Cause when I change the filed name to "num" I get the "Invalid Procedure Call" error

 

[Golom]

Having a primary key may help from a speed perspective because that causes the fields to be indexed.

"Invalid Procedure Call" is usually a sign that arguments to a functional call are out of range. That will happen if you have Null, zero or negative values for the "Num" field because
[tt]
Mid$ ("," & I1.Memo1 & ",", 0, 1) =','
Mid$ ("," & I1.Memo1 & ",", Null, 1) =','
Mid$ ("," & I1.Memo1 & ",", -1, 1) =','
[/tt]
are all illegal.

 

[tomk01]

ok now the tempint1 table is empty and it is working but still returning no data.

 

[lespaul]

tempint1 table needs to look like this:

table: TempInt1
Field: Num

Values in Field Num:
1
2
3
4
5
6
...
22

up to 2 numbers past the longest string in the field you are searching. Your example has 20 characters, so your table would need to have numbers up to 22.

(at least that's what I would guess from Golom's explanation to me about this technique in another thread)

If you name your table "Integers" with a single field named "num", Golom's SQL doesn't need any modifications to work.

HTH


Leslie

 

[tomk01]

when i put number values in the "num" field I get the "Invalid Procedure Call