Appending/removing values with comma (,)

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.

ethanjohnsonsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rafranciscoConnect With a Mentor Commented:
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
 
rafranciscoCommented:
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
 
imrancsConnect With a Mentor Commented:
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
 
ethanjohnsonsAuthor Commented:
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
 
ethanjohnsonsAuthor Commented:
that will work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.