Pulling data from a column of multiple items that are comma delimited

Mike the easy answer is to use the like operator and % signs around the search
string

So, your query would look like

SELECT Field1, Field2, ...
FROM tblName
WHERE SupportedCars Like '%MAXIMA%'

Anohter option would be to use the instr function such as the following:

SELECT Field1, Field2, ...
FROM tblName
HAVING Instr(SupportedCars,'MAXIMA',1)>0

*Order for instr criteria may be off...

Q*Bert

"Mike" <mike333@cox.net> wrote:
>
>Hi all,
>
>I have no clue if this can be done or not, I have a database of autoparts,
>one table is for cars ( that each part would fit ) I understand that to
normalize
>this you would want one car per entry in the cars table but Im getting this
>data from a customer and he has multiple cars (example: 240SX,300ZX,PATHFINDER,MAXIMA)
>set up in the car table. Meaning one part ( from the parts table) would
fit
>all those cars.
>
>I need some simple/basic instructions on how I would parse or manipulate
>the data or even if I can using an SQL statement ( the database is access
>) to match it up with the part if a user searches for a single car.
>
>
>I guess my question is ... how do you pull a single name from a list of
comma
>delimited items and display only a single item back to the user ?
>
>( as you can see im not even sure how to ask the question ) any help
>or even getting me on the right track would be great .....
>
>Thanks
>
>Mike

hope this can help u out... Use ADO... It looks bad on this text mode. E-mail
me, if u need source code.. The source code is shown as below

bool InsertData(char ID[6], char ItemName[50], char HyperLink[150], char
Attr[150])
{

/* ******************************************** */
/* SQL Server Setups */
/* ******************************************** */
char ServerName[] = "MySQLServer";
char UID[] = "MyID";
char Passw[] = "MyPassword";
char SQLItem[] = "Driver=SQL Server; server=MySQLServer; UID=MyID; PWD=MyPassword;
database=oadb";

int PassLength, k;
char SQLCommand_01[350],;
char ItemNo[5], Shortcut[100];
size_t n, i;

PassLength = 0;
n = i = 0;

// Select Recoders that need to Be Modified
sprintf(SQLCommand_01,"select OI_Index from OAMenu_ItemList where OI_ItemName
= '%s' and OI_HypeLink = '%s'", ItemName, HyperLink);
// Select Shortcut which in user's record
sprintf(SQLCommand_02,"select OU_UserID, OU_Shortcut from OAMenu_UserInfo
where OU_UserID = '%s'", ID);

try
{
::CoInitialize(NULL);
/************************************************************/
/* Create connection for SQLCommand_01 */
/************************************************************/
_ConnectionPtr pConn_01(__uuidof(Connection));// Create a connection object.
// Open the connection.
pConn_01->Open(SQLItem, UID, Passw, PassLength);
// Opening a recordset using an existing connection.
_RecordsetPtr pRS_01(__uuidof(Recordset)); // Create a recordset object
// Get item number from OAMenu_ItemList
pRS_01->Open(SQLCommand_01, pConn_01.GetInterfacePtr(), adOpenStatic, adLockOptimistic,
adCmdUnknown);

/************************************************************/
/* Create connection for SQLCommand_02 */
/************************************************************/
_ConnectionPtr pConn_02(__uuidof(Connection));// Create a connection object.
// Open the connection.
pConn_02->Open(SQLItem, UID, Passw, PassLength);
// Opening a recordset using an existing connection.
_RecordsetPtr pRS_02(__uuidof(Recordset)); // Create a recordset object

/************************************************************/
/* Create connection for SQLCommand_03 and SQLCommand_04 */
/************************************************************/
_ConnectionPtr pConn_03(__uuidof(Connection));// Create a connection object.
// Open the connection.
pConn_03->Open(SQLItem, UID, Passw, PassLength);
// Opening a recordset using an existing connection.
_RecordsetPtr pRS_03(__uuidof(Recordset)); // Create a recordset object

/************************************************************/
/* Create connection for the following SQLCommand_01 */
/************************************************************/
_ConnectionPtr pConn_04(__uuidof(Connection));// Create a connection object.
// Open the connection.
pConn_04->Open(SQLItem, UID, Passw, PassLength);
// Opening a recordset using an existing connection.
_RecordsetPtr pRS_04(__uuidof(Recordset)); // Create a recordset object

//enumerate every record.

if(pRS_01->adoEOF == false) // This shortcut can be found in OAMenu_ItemList
{
strcpy(ItemNo, StrTrimRight( UC _bstr_t(RsITEM(pRS_01,0L))));
i = strlen(ItemNo);
// Get OU_Shortcut from OAMenu_UserInfo by employee ID
pRS_02->Open(SQLCommand_02, pConn_02.GetInterfacePtr(), adOpenStatic,
adLockOptimistic, adCmdUnknown);
if(pRS_02->adoEOF == false) // This empolyees' record exists in the database
"OAMenu_UserInfo"
{
strcpy(Shortcut, StrTrimRight( UC _bstr_t(RsITEM(pRS_02,1L))));
n = strlen(Shortcut);
for(k = 0; k < i; k++)
{
Shortcut[n] = ItemNo[k];
n++;
}
Shortcut[n] = ',';
Shortcut[n+1] = NULL;
// Update user's shortcut
sprintf(SQLCommand_03,"Update OAMenu_UserInfo set OU_Shortcut = '%s',
OU_Flag = 0 where OU_UserID ='%s'", Shortcut, ID);
pRS_03->Open(SQLCommand_03, pConn_03.GetInterfacePtr(), adOpenStatic,
adLockOptimistic, adCmdUnknown);
}
else // This empolyees' record doesn't exist in the database "OAMenu_UserInfo"
{
n = 0;
for(k = 0; k < i; k++)
{
Shortcut[n] = ItemNo[k];
n++;
}
Shortcut[n] = ',';
Shortcut[n+1] = NULL;
// Update user's shortcut
sprintf(SQLCommand_05,"insert into OAMenu_UserInfo values ('%s','','%s','',0)",
ID, Shortcut);
pRS_03->Open(SQLCommand_05, pConn_03.GetInterfacePtr(), adOpenStatic,
adLockOptimistic, adCmdUnknown);
}
}
else // This Shortcut is not in ITRI.shc
{
// Insert this item into OAMenu_ItemList
sprintf(SQLCommand_04, "insert into OAMenu_ItemList values(1,'Others','','%s','%s','%s','','')",
ItemName, HyperLink, Attr);
pRS_03->Open(SQLCommand_04, pConn_03.GetInterfacePtr(), adOpenStatic,
adLockOptimistic, adCmdUnknown);

// Retry to get item number
pRS_04->Open(SQLCommand_01, pConn_04.GetInterfacePtr(), adOpenStatic,
adLockOptimistic, adCmdUnknown);
strcpy(ItemNo, StrTrimRight( UC _bstr_t(RsITEM(pRS_04,0L))));
i = strlen(ItemNo);
// Get OU_Shortcut from OAMenu_UserInfo by employee ID
pRS_02->Open(SQLCommand_02, pConn_02.GetInterfacePtr(), adOpenStatic,
adLockOptimistic, adCmdUnknown);
if(pRS_02->adoEOF == false) // This empolyees' record exists in the database
"OAMenu_UserInfo"
{
strcpy(Shortcut, StrTrimRight( UC _bstr_t(RsITEM(pRS_02,1L))));
n = strlen(Shortcut);
for(k = 0; k < i; k++)
{
Shortcut[n] = ItemNo[k];
n++;
}
Shortcut[n] = ',';
Shortcut[n+1] = NULL;
// Upadte OAMenu_UserInfo
sprintf(SQLCommand_03,"Update OAMenu_UserInfo set OU_Shortcut = '%s',
OU_Flag = 0 where OU_UserID ='%s'", Shortcut, ID);
pRS_03->Open(SQLCommand_03, pConn_03.GetInterfacePtr(), adOpenStatic,
adLockOptimistic, adCmdUnknown);
}
else // This empolyees' record doesn't exists in the database "OAMenu_UserInfo"
{
n = 0;
for(k = 0; k < i; k++)
{
Shortcut[n] = ItemNo[k];
n++;
}
Shortcut[n] = ',';
Shortcut[n+1] = NULL;
// Upadte OAMenu_UserInfo
sprintf(SQLCommand_05,"insert into OAMenu_UserInfo values ('%s','','%s','',0)",
ID, Shortcut);
pRS_03->Open(SQLCommand_05, pConn_03.GetInterfacePtr(), adOpenStatic,
adLockOptimistic, adCmdUnknown);
}
}
pConn_01->Close();
pConn_02->Close();
pConn_03->Close();
pConn_04->Close();
return true;
}
catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bs = _bstr_t(" Error: ") + _bstr_t(e.Error()) + _bstr_t(" Msg:
") + _bstr_t(e.ErrorMessage()) + _bstr_t(" Description: ") + _bstr_t(e.Description());
::CoInitialize( NULL );
MessageBox(0,bs,bstrSource, MB_OK);
return false;
}
}