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(@ValuesLi
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(@Ol
If @NewValues <> ''
Select @NewValues = Left(@NewValues, Len(@NewValues)-1)
Update Admin_Priv Set DevPref = @NewValues
Where ID_Col = <SomeValue>
Imran
Main Topics
Browse All Topics
by: rafranciscoPosted on 2006-06-28 at 09:55:33ID: 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')