Click here to Skip to main content
11,578,707 members (60,736 online)
Rate this: bad
good
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 22:31pm
Edited 13-Jan-13 23:41pm
v2
Comments
CHill60 at 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
0 Peter Leow 135
1 OriginalGriff 113
2 Richard MacCutchan 85
3 Afzaal Ahmad Zeeshan 75
4 Frankie-C 75
0 OriginalGriff 933
1 Sergey Alexandrovich Kryukov 740
2 Abhinav S 573
3 F-ES Sitecore 420
4 Suvendu Shekhar Giri 403


Advertise | Privacy | Mobile
Web03 | 2.8.150603.1 | Last Updated 14 Jan 2013
Copyright © CodeProject, 1999-2015
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