Solved

Appending/removing values with comma (,)

Posted on 2006-06-28
5
347 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
  • 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now