Print Page | Close Window

Selecting from comma seperated string

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1971
Printed Date: 06 May 2024 at 11:10pm


Topic: Selecting from comma seperated string
Posted By: zionz
Subject: Selecting from comma seperated string
Date 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


Replies:
Posted By: Lugh
Date 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.

Posted By: rvink
Date 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.

Posted By: rvink
Date 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.

Posted By: saoco77
Date 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


Print Page | Close Window