Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: Selecting from comma seperated string Post Reply Post New Topic
Author Message
zionz
Newbie
Newbie


Joined: 03 Jan 2008
Online Status: Offline
Posts: 1
Quote zionz Replybullet Topic: Selecting from comma seperated string
    Posted: 03 Jan 2008 at 10:19am
Hello all. I'm sorry if this has been discussed (i've searched the forums for an hour to no avail)
 
I have a data column in a SQL db table that has a large varchar string seperated by commas (lets call the column name *raw_data*.
 
An example is
 
 
55 NE HAGY ST, , CAMAS, ZZ, 94407-.  , , , ,P ,  -, . 
or
 
188/8 MVP CCD TR, , VIZ, DD 52201-. EFFECTIVE DATE: 11/21/2007.  , , , ,P ,  -, . EFFECTIVE DATE: 11/21/2007. 
 
Very confusing data.
 
I need to pull outa single piece if data (say where the P is) always between the 8th and 9th commas. The data and lengths vary from record to record so all I have to go by are the commas.
 
Any insight would be extremely helpful.
 
Thanks a lot.
 
ZZ
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 03 Jan 2008 at 11:17am
Well, one trick, depending on what the rest of the data looks like, is to search for ",P," in the string.  But, that may give false positives.

A more certain method might look like:


Local NumberVar StrLoc := 1;
Local NumberVar i;

For i := 1 to 8 Do
(StrLoc := InStr(StrLoc,",",{raw_data}) +1);

If {raw_data}[StrLoc] = "P" Then
True
Else
False;


As a note, that is completely untried.  You may need to futz with a +1 here or there to get it to land on the right character.


IP IP Logged
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet Posted: 19 Feb 2008 at 3:14pm
Another solution is to import the data into Excel or an Access database using the commas as the field delimiter. You would then need to run the report on Excel/Access instead of your SQL database.
IP IP Logged
rvink
Groupie
Groupie
Avatar

Joined: 04 Feb 2008
Location: New Zealand
Online Status: Offline
Posts: 55
Quote rvink Replybullet Posted: 20 Feb 2008 at 12:54pm
Another solution is to use the Split function:

array = Split ({table.raw_data}, ",")

This will create an array of substrings so you can easily reference the 8th item without manually counting the commas.

My earlier suggestion of importing the data into an Access database and using the comma as a field delimiter will be more efficient - especially if you are dealing with a lot of records - since Access will have done all the work, Crystal will have an easier job of retrieving the data.
IP IP Logged
saoco77
Senior Member
Senior Member


Joined: 26 Jun 2007
Online Status: Offline
Posts: 104
Quote saoco77 Replybullet Posted: 31 Mar 2008 at 7:10am
taking the array concept one step further - try this formula

(split((table.field),","))[9]


[9] - represents the position of the block of text you are trying to extract. Each comma will delineate one position.

Hope this helps.

Sarah
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.