Normally you have it as rows in a column rather than a string in a column but if that's how you have it:
-
declare @tmpstr as varchar(1024)
-
select @tmpstr = State from Products where Product = 'Apple'
-
@tmpstr=replace (@tmpstr,'|1|','auckland')
-
@tmpstr=replace (@tmpstr,'|2|','northland')
-
...
-
-
select @tmpstr
-
Thanks for the reply there are two columns one is product, the other is state
product data is - Apple
State data is -|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|
As mentioned the state ids need to be replaced by state names and this is the resulting string which i need to get and display on the webform.
|Nor|Auc|Cor|Bay|Wai|Rot|Eas|Tau|Rua|Haw|Tar|Wan|M an|Wai|Wel|Nel|Mar|Wes|Chr|Can|Mtc|Wan|Que|Ota|Dun |Fio|Sou|
Your suggestions are much appreciated, Cheers, Shilpa.