Access MS Jet SQL parse delimited text in field.

[blueknight529]

Table contains a field as below:

record_id
FL|8|B-94|12|3
FL|8|E336|12|9
GA|59|SL12BTJ507|12|1

Due to program limitations, I MUST use ONLY MS Jet SQL.
I need to SPLIT this field into 5 Variables, using the "|" as a delimiter.
No VBA, No Function Building, No Access Features, strictly using MS Jet SQL.

Desired Result

Var1=FL, Var2=8, Var3=B-94, Var4=12, Var5=3
Var1=FL, Var2=8, Var3=E336, Var4=12, Var5=9
Var1=GA, Var2=59, Var3=SL12BTJ507, Var4=12, Var5=1

 

[PHV]

You may try this (typed, untested):

SELECT "Var1=" & Replace(Replace(Replace(Replace(record_id,"|",", Var2=",1,1),"|",", Var3=",1,1),"|",", Var4=",1,1),"|",", Var5=",1,1) AS DesiredResult
FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blueknight529]

Tried it, got this:
Undefined function 'Replace' in expression

 

[PHV]

How do you execute your JetSQL query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blueknight529]

The code will run in Access when run as an SQL statement.
The application I'm building utilizes JET only.
For testing purposes, I use AnySQL Maestro Professional.
This is where I get the error.
Not sure I was clear enough.
What I need, is to parse the field by the "|" delimiter
getting
FL as STATE, 8 AS HOUSE, B-94 AS LOT, 12 AS YEAR, 3 AS LOCATION
etc