Click here to Skip to main content
12,763,299 members (38,359 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
Hi all,

I'm struggling a bit with a Jet SQL coding problem. I have a column with strings in the form 'EV1.EV2' or 'EV4.EV5.EV6'. I am trying to replace the '.'s in this column with the new line delimiter '\n'. In normal SQL the Replace function could be used but the application I am using only uses Jet SQL. I have been able to solve the problem with one '.' using the code:
iif([GateCutSets].[CutSetID] like '%.%',(Left([GateCutSets].[CutSetID],InStr([GateCutSets].[CutSetID], '%.%')-1) & Chr(13) & Chr(10) & Right([GateCutSets].[CutSetID],(Len([GateCutSets].[CutSetID])-Instr([GateCutSets].[CutSetID], '%.%')))),[GateCutSets].[CutSetID])) as CutSet

This replaces the string with a substring of everything before the first '.', followed by the '\n' delimiter, followed by everything after the '.'.

When I try something similar for the situation where there are two '.' in the string however, I'm unsure how to find the position of the second '.'. Is there a function which will do this or is there an easier way of doing what I am trying to do?

All advice is appreciated.

Regards,
Stephen.
Posted 13-Jan-13 23:31pm
Updated 14-Jan-13 0:41am
v2
Comments
CHill60 14-Jan-13 10:47am
   
Could you abstract the replace out of the SQL and into Access VBA or whatever you're using to call jet?

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

JET SQL = Access query? If yes, see here: http://www.techonthenet.com/access/functions/string/replace.php[^]
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170217.1 | Last Updated 14 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100