Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access MS Jet SQL parse delimited text in field.

Status
Not open for further replies.

blueknight529

IS-IT--Management
Oct 7, 2013
3
US
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


 
You may try this (typed, untested):
Code:
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
 
Tried it, got this:
Undefined function 'Replace' in expression
 
How do you execute your JetSQL query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top