Solved

Appending/removing values with comma (,)

Posted on 2006-06-28
5
353 Views
Last Modified: 2012-08-14
I have a field called, DevPref, on a table (Admin_Priv), and the field has values like below:

default,splits,installments
default,splits
default,splits
default,installments,splits
NULL

I need to update the field with adding and removing "sortasc" or "sortdesc".

for instance,

adding "sortasc" will be:  i.e. default,splits,installments,sortasc
updating this one will be: default,splits,installments,sortdesc

for the NULL row
it would simply be:
                       sortasc
updating this one will be:
                       sortdesc

When I append "sortasc" or "sortdesc" to the tail so that the existing values (i.e. default,splits,installments) won't be impacted.

0
Comment
Question by:ethanjohnsons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 17002804
Your update statement will look like this:

UPDATE Admin_Priv
SET DevPref = ISNULL(DevPref + ',', '') + 'sortasc'

To update sortasc to sortdesc:

UPDATE Admin_Priv
SET DevPref = REPLACE(DevPref, 'sortasc', 'sortdesc')
0
 
LVL 10

Assisted Solution

by:imrancs
imrancs earned 100 total points
ID: 17002815
First create this function
---------------


--This UDF returns string values

CREATE FUNCTION dbo.GetValuesFromListS
(
      @ValuesList varchar(8000)
)
RETURNS
@ParsedList table
(
      Value Varchar(1000)
)
AS
BEGIN
      DECLARE @Value varchar(1000), @Pos int

      SET @ValuesList = LTRIM(RTRIM(@ValuesList))+ ','
      SET @Pos = CHARINDEX(',', @ValuesList, 1)

      IF REPLACE(@ValuesList, ',', '') <> ''
      BEGIN
            WHILE @Pos > 0
            BEGIN
                  SET @Value = LTRIM(RTRIM(LEFT(@ValuesList, @Pos - 1)))
                  IF @Value <> ''
                  BEGIN
                        INSERT INTO @ParsedList (Value)
                        VALUES (@Value) --Use Appropriate conversion
                  END
                  SET @ValuesList = RIGHT(@ValuesList, LEN(@ValuesList) - @Pos)
                  SET @Pos = CHARINDEX(',', @ValuesList, 1)

            END
      END      
      RETURN
END
---------------

1. Append a value
Declare @NewValue Varchar(100)
Select @NewValue = 'sortasc'

Update Admin_Priv Set DevPref = IsNull(DevPref, '') = @NewValue
Where ID_Col = <SomeValue>

2. Removing a value
Declare @ValueToRemove Varchar(100)
Declare @OldValues Varchar(1000)
Declare @NewValues Varchar(1000)

Select @ValueToRemove = 'sorasc'
Select @OldValue = ''
Select @NewValue = ''

Select @OldValues = DevPref From Admin_Priv Where ID_Col = <SomeValue>
Select @NewValues = Coalesce(@NewValues, '') + Value + ',' From dbo.GetValuesFromListS(@OldValues) Where Value <> @ValueToRemove

If @NewValues <> ''
  Select @NewValues = Left(@NewValues, Len(@NewValues)-1)

Update Admin_Priv Set DevPref =  @NewValues  
Where ID_Col = <SomeValue>


Imran
0
 

Author Comment

by:ethanjohnsons
ID: 17003069
UPDATE Admin_Priv
SET DevPref = ISNULL(DevPref + ',', '') + 'sortasc'

To update sortasc to sortdesc:

UPDATE Admin_Priv
SET DevPref = REPLACE(DevPref, 'sortasc', 'sortdesc')

This works fine, but I need to make sure that ONLY one of 'sortasc' or 'sortdesc' is added.


UPDATE Admin_Priv
SET DevPref = ISNULL(DevPref + ',', '') + 'sortasc'

adds mutiple of 'sortasc', so, if 'sortasc' or 'sortdesc' exists, it has to skip adding.
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 400 total points
ID: 17003115
Just put a WHERE clause to make sure that sortasc is not in the string yet:

UPDATE Admin_Priv
SET DevPref = ISNULL(DevPref + ',', '') + 'sortasc'
WHERE DevPref IS NOT LIKE '%sortasc%'
0
 

Author Comment

by:ethanjohnsons
ID: 17024717
that will work.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question