using replace function, Please help

[SharonRao]

Hello all, I have a products table (see below) Product State Apple |1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|2 0|21|22|23|24|25|26|27| I have to display the state data on a webform replacing the numbers with actual state names for example replace 1 with auckland, 2 with northland and so on. How to do this in SQL. the returned data from my sql query should look like this string, how do i write the query to acheive this. Auckland
Northland
Waikatoo Please help. Cheers, Shilpa.

Thanks in Advance for your help. Best Regards

[John ph]

tblProductsS (table) (columns) Product_ID int Product_Name Nvarchar State Nvarchar tblState (table) (Columns) State_Id int State_Name Nvarchar sql ---- DECLARE @SQLQuery AS NVARCHAR(2000) DECLARE @SubQuery AS NVARCHAR(100) select @SubQuery=Replace(State,'|',',') from tblProductsS where Product_ID = 1 --@ProductID SET @SQLQuery = 'Select State_Name from tblstate where State_ID IN (' SET @SQLQuery = @SQLQuery + @SubQuery SET @SQLQuery = @SQLQuery + ')' EXEC sp_executesql @SQLQuery

---

Regards
John

---

[edukulla]

We can do this, if you can give us more information about other tables like a)Where is the state information stored? Is it in a separate table and how "product" table and this table are related? b)What input you pass to the query EX: you just wanted to pass the number Ex: 1 or 2 etc right? I can provide you currently the abstract code it will look some thing like this. Suppose the input request from webform to db is '15' Declare @check_cond int Select @check_cond = PATINDEX('%15%',product state) from products Now, @check_cond contains the position of your input in the product state column of products table. patindex returns zero if it does not match your input. Thanks!

Santhosh Kumar Edukulla

[John ph]

PATINDEX function returns the starting position of the pattern within the string being searched. I don't think this is neccessary...

---

Regards
John

---

[edukulla]

Patindex is used to check whether the given 'id' is in products table. Then Use the same input id to query the productstate table to know the statename accordingly. Thanks!

Santhosh Kumar Edukulla