Removing Commas From a field

[mactech999]

Hello I have 2 fields that are name the same in different tables. One is called
: ProductGrade.GradeID and the other is
Parts.GradeID.

now my problem is this I need a way to link these two tables however the
Parts.GradeID has some of the entries typed in like this:

3,4

is there a way for me to get them to be like this instead

3
4

if i can then I would be able to compare the tables like this Parts.GradeID =
ProductGrade.GradeID I can't right now because they do not equal each other..

thanks for the help

[Dan Bracuk]

Normalize your database. Then you won't have problems like this.

[philh]

You need a database redesign. If Parts.GradeID is supposed to be the foreign
key to the GradeID field in ProductGrade, i.e. it's the "link" that joins the
two tables, then it's designed incorrectly. Each field should hold one
discrete piece of information, not a list. Lists don't cut it for normalized
relational DB design, and you're going to drive yourself crazy trying to work
around this flaw.

[mactech999]

yeah the only problem with that is we have a program that need the information
that way with the commas in the field... so i'm looking for a work around I
know there are ways just need to find one.

if anyone has any ideas other than redoing the database please let me know..

thanks

[Dan Bracuk]

Change the program as well.

[mactech999]

lol wish i could but we can't

[Dan Bracuk]

Look for another job.

[MikerRoo]

Your options vary hugely based on what RDBMS you are using and how the program
accesses the database.

The best solution is to normalize your database but create a view that the
program accesses for its bastardized format.
The view could even have the same name as the old table and the program would
be none the wiser.

Details depend on RDBMS and what else the program does.

Just a pure workaround, with no database changes whatsoever, is short-sighted
but possible -- depending (again) on what DB is in use.

[jorgepino]

you could use a loop

<cfloop index = "ListElement"
list = "1,2,3,4">
<cfoutput>#ListElement#</cfoutput><br>
</cfloop>
1
2
3
4