SQL query help - need SKUs for stringProducts

Hi Everyone,

I'm stuck on a SQL query. I'm trying to get the SKUs for some BTO Items stored in a string.

SELECT configSessions.stringProducts FROM (ProductsOrdered INNER JOIN configSessions ON ProductsOrdered.idconfigSession = configSessions.idconfigSession) WHERE ProductsOrdered.idOrder=16

returns
137,139,140,142,143,144,145,146,147,

it's a string of NTEXT type.

I need to get the SKUs for each of those products and would like it to come back to me like this in a recordset:
idProduct  | SKU
137             BSP1BMH001
139             BSP1BMH003
140             BSP1BMH004
142             BSP1BMH006
143             BSP1BMH007
144             BSP1BMH008
145             BSP1BMH009
146             BSP1BMH010
147             BSP1BMH011

I'm in SQL server on the server testing the statements before I even start the ASP code for it. I tried SQL - "IN" but it told me I had a clash with NTEXT & INT. I was going to try REPLACE and see if I could replace the commas with ',' but I think I'd still have the datatype clash. Right now, I'm trying to split the SQL string, but trying to find a code snippet that everyone agrees on is like searching for the word "the" on the internet.

Does anyone have any suggestions?

Thanks so much.

Best wishes,
Andrea





Edited by AndreaPou - 06-April-2011 at 3:17pm

Hi Mark,

Thanks for the CAST tip. I saw that in a lot of the split functions and troubleshooting tips for the datatype clash I was getting. So I read up on it. I did eventually figure it out just now. Wow. That was rough. I was seeing red errors for so long I almost didn't remember what output looked like. lol. I will keep your contact info though for the next time.

Thanks again.

Best wishes,
Andrea