T-SQL How to translate multiple sub-strings to new values

Question

First of all, sorry because I don't know how to title my problem.

My situation is, I have 1 lookup table with this format:

+----+-----------+------------+
| ID | Fruit     |  Color     |
+----+-----------+------------+
|  1 | Banana    | Yellow     |
|  2 | Apple     | Red        |
|  3 | Blueberry | NotYetBlue |
+----+-----------+------------+

And my main table is like this:

+-------+------------------------+------------+
| MixID |        Contains        | MixedColor |
+-------+------------------------+------------+
|     1 | Banana                 |            |
|     2 | Apple:Blueberry        |            |
|     3 | Banana:Apple:Blueberry |            |
+-------+------------------------+------------+

I want to make a look-up on the first table and fill in the MixedColor column as below:

+-------+------------------------+-----------------------+
| MixID |        Contains        |      MixedColor       |
+-------+------------------------+-----------------------+
|     1 | Banana                 | Yellow                |
|     2 | Apple:Blueberry        | Red:NotYetBlue        |
|     3 | Banana:Apple:Blueberry | Yellow:Red:NotYetBlue |
+-------+------------------------+-----------------------+

Any help will be very appreciated.

Thank you

Do you have any control over the table schema (design)? cause this table schema where you have a column COntains with multiple data elememnts in one attribute, violates proper design principles and makes any queries much more difficult. If you can change it, add another table to hold the contains data, with one row for each MixID-fruit combination. Otherwise, you'd be better off performing your task in client code. — Charles Bretana, Jul 11 '13 at 02:27
My situation is, I have around 15 types of fruit, and the second data table is existing data with unknown number of fruit combinations (mixes). Hence, I don't think the solution of creating MixID-fruit table could help me find the corresponding MixedColors. I guess I need to perform it in my application level? By the way, thank you for your help. — Harry Ninh, Jul 11 '13 at 03:04

score 3 · Answer 1 · edited May 23 '17 at 11:49

As "Charles Bretana" suggested it would be best to modify you schema to something like this:

+--------+-------+----------+
| RowID  | MixID |  FruitID |
+--------+-------+----------+
|    0   |     1 |     1    | 
|    1   |     2 |     2    | 
|    2   |     2 |     3    | 
|    3   |     3 |     1    | 
|    4   |     3 |     2    | 
|    5   |     3 |     3    | 
|--------+-------+----------+

now using a simple inenr join you can select the correct color and match the fruit.

if it is not possible for you to achieve that construct you could use a recursive query mentioned here : Turning a Comma Separated string into individual rows. to manipulate your data to look like that.

Here is a SQL Fiddle: http://sqlfiddle.com/#!3/8d68f/12

table data :

create table Mixses(MixID int, ContainsData varchar(max))
insert Mixses select 1,  '10:11:12'
insert Mixses select 2,  '10:11'
insert Mixses select 3,  '10'
insert Mixses select 4,  '11:12'

create table Fruits(FruitID int, Name varchar(200), Color varchar(200))
insert Fruits select 10,  'Bannana'    , 'Yellow'
insert Fruits select 11,  'Apple'      , 'Red'
insert Fruits select 12,  'BlueBerry'  , 'Blue'
insert Fruits select 13,  'Pineapple'  , 'Brown'

Query:

;with tmp(MixID, DataItem, Data)  as 
(
  select 
        MixID, 
        LEFT(ContainsData, CHARINDEX(':',ContainsData+':')-1),
        STUFF(ContainsData, 1, CHARINDEX(':',ContainsData+':'), '')
  from Mixses

  union all

  select MixID, 
        LEFT(Data, CHARINDEX(':',Data+':')-1),
        STUFF(Data, 1, CHARINDEX(':',Data+':'), '')
  from tmp
  where Data > ''
)

select t.MixID, t.DataItem, f.Color
from         tmp t
inner join   Fruits f on f.FruitID=t.DataItem
order by MixID

I agree that the data should be structured in this way, and your code is doing this well. However, since I will only deal with this existing data (not mine) for a short time, a workaround solution proposed by "Goat CO" seems more straightforward. Many thanks for your help, I'm sure I will re-use these code some days. — Harry Ninh, Jul 11 '13 at 03:28

score 3 · Accepted Answer · answered Jul 11 '13 at 03:15

I agree that ideally your table structure should be altered. But, you can get what you want with:

SELECT   MIXID, [CONTAINS],
STUFF((
          SELECT ':' + Color
          FROM Table1 a
          WHERE ':'+b.[Contains]+':' LIKE '%:'+a.Fruit+':%'
                FOR XML PATH('')
            ), 1, 1, '') AS Color
FROM Table2 b
GROUP BY MIXID, [CONTAINS]

Demo: SQL Fiddle

T-SQL How to translate multiple sub-strings to new values

2 Answers2