CODEWITHSUNDEEP
c#asp.netsql-server-2008-r2
OnlyOneEA
OnlyOneEA

Reputation: 203

Replace comma separated values with actual names

Hope someone can help me with this. I have a table that has column with values that either has a single value or a value that is comma separated and they're all single letter values. What I want to accomplish to be able to (at the presentation level) display the actual names for the letter values. Here's what I want

Original results

  PersonID    MentorList
  1           A
  2           K, L
  3           F

What I want

  PersonID    MentorList
  1           Big Brother/Big Sister
  2           Youth Motivators, Other Mentoring
  3           Gear Up

I've tried the following SQL statement but it's not producing the result that I want

  SELECT PersonID, REPLACE(REPLACE(MentorList, 'A', 'Big Brother/Big Sister'), 'B', 'Best Buddies') AS MentorList
  FROM Mentors

I get the following

  PersonID    MentorList
  1           Best Buddiesig Best Buddiesrother/Best Buddiesig Sister
  2           Youth Motivators, Other Mentoring
  3           Gear Up

As you can see, the query statement is replacing all the Bs with "Best Buddies" in the first row. How do I get it to work the way I want it?

Thanks in advace...

Upvotes: 1

Views: 326

Answers (2)

Bayeni
Bayeni

Reputation: 1046

Here is an example that I created hope it will help or guid you into solving your requirement:

    -- create a temp table to store Letters and their description
    CREATE TABLE #Test1
    (
        ID int identity(1,1),
        Letter varchar(10),
        LetterDescription varchar(100)
    )

    GO 
    --Insert letter into temp table
    INSERT INTO #Test1(Letter, LetterDescription)
    SELECT 'K', 'Youth Motivators'

    INSERT INTO #Test1(Letter, LetterDescription)
    SELECT 'L', 'Other Mentoring'

    INSERT INTO #Test1(Letter, LetterDescription)
    SELECT 'F', 'Gear Up'

    --@Value is just a variable I use to test, your can replace this with a table
    DECLARE @Value VARCHAR(20)
    SELECT @Value = 'K'
    -- SELECT @Value = 'K,L'

    --Query to do the job 
    SELECT @Value AS  AcualChar, 
            CASE WHEN Desc2 IS NULL THEN Desc1 ELSE Desc1 + ','+ Desc2   END AS MentorList
    FROM 
    (
        SELECT T.LetterDescription AS Desc1, y.Desc2 FROM 
        (
            SELECT
            CASE WHEN Charindex(',', @Value) > 0 THEN  Substring(@Value, 1,Charindex(',', @Value)-1) ELSE @Value END AS Letter1

        ) AS L 
        JOIN  #Test1 AS T ON L.Letter1 = T.Letter 

         FULL OUTER JOIN 
        (
            SELECT T.LetterDescription AS Desc2 FROM 
            (
                SELECT
                CASE WHEN Charindex(',', @Value) > 0 THEN Substring(@Value, Charindex(',', @Value)+1, LEN(@Value))  ELSE '' END Letter2
            ) AS L 
            JOIN  #Test1 AS T ON L.Letter2 = T.Letter 
        ) as y ON 1=1
    ) X

    GO
    --Drop the temp table
    DROP TABLE #Test1

Upvotes: 0

Laloutre
Laloutre

Reputation: 172

If you really want to go with chained REPLACE, then first replace each letter by something that got no chance of interfering ( such as #A, #B, ... ) then replace #A, #B , ... by the actual expanded text you want.

But why don't you have those values accessible separately ? why storing this as a concatened string ?

You probably should have a Table Mentor with MentorLetter as primary key, and a field MentorFullName. Then a link table with (MentorLetter, PersonId), to be able to have more than Mentor per Person.

Good luck.

Upvotes: 2

Related Questions