Hey All, I am not very familiar with SQL syntax. I was asked to do the following (manually) and was hoping someone can help me do this with SQL.


I have to remove all "accounts" beginning with 00, 01, or 02 from the "allowedAccounts" column.

However, multiple accounts exists for one record. The database was not designed properly.

example of one record.

id allowedAccounts
--- ----------------
001 00_Report234, 01_Report393, 03_Report59


Can somone show me how to do this? In the meantime I will be studying SQL.

Yes your database needs some redesign. In the meantime this select query should show you the records you want to delete

SELECT id, allowedaccounts
FROM ActTable
WHERE allowedAccounts LIKE '0[0-2]%'

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Yikes! Your best bet is to parse the allowedaccounts column using the Substring function for each row. Meaning, loop through the rows and for each row, build a new string with only the values you want then update the allowedaccounts column with the new string for the row you are on.

If this doesn't make sense, let me know and I'll try to put together an example for you...

David, wouldn't that just delete the entire row? SQL Scott, I think you have the right approach, can you show me an example. I did put together some code from some books and articles I read (took me 6 hrs), but it's adding some comma's at the end, and it's cutting off some of the accounts, I think because of field length. Here it is if you want to help me fix it. Btw, CompanySubscriptionReports only has 2 fields CompanyID (varchar 50) and allowedAccounts (varchar 8000)

[code]
--Delete CompanyID Is NUll
Delete CompanySubscriptionReports where CompanyID is NUll

--Delete Duplicates rows
select distinct *
into #holding
from CompanySubscriptionReports
truncate table CompanySubscriptionReports
insert CompanySubscriptionReports
select *
from #holding
drop table #holding

--Create normalized table to Append Data
create table #ReportSubscription (
    CompanyID int not null,
    Reports varchar(200) not null)

--Need Tally Table
create table #Tally (
    ID int not null)

go

set nocount on
declare @i int
select @i = 0
while @i <5000
begin
    insert into #Tally (ID)
    select (1 + @i)
    select @i = @i + 1
end
go

--Insert Data to Normalized Table
INSERT INTO #ReportSubscription SELECT CompanyID,
NullIf(SubString(',' + allowedAccounts + ',' , ID , CharIndex(',' , ',' + allowedAccounts + ',' , ID) - ID) , '') AS Report
FROM #Tally, CompanySubscriptionReports
WHERE ID <= Len(',' + allowedAccounts + ',') AND SubString(',' + allowedAccounts + ',' , ID - 1, 1) = ','

--Delete Duplicate Report ID from ReportSubscription
select distinct *
into #holding
from #ReportSubscription
truncate table #ReportSubscription
insert #ReportSubscription
select *
from #holding
drop table #holding

--Delete Reports From 00, 01, 02
Delete #ReportSubscription Where Left(Reports,2) in ('00','01','02')

--Truncate CompanySubscriptionReports
truncate table CompanySubscriptionReports

--create a table to work with
create table #workingtable (
    CompanyID int not null,
    Reports varchar(200) not null,
    list varchar(7000),
    constraint pk_wt primary key (CompanyID,Reports) )

insert into #workingtable (CompanyID ,Reports)
select CompanyID ,Reports
from #ReportSubscription
order by CompanyID ,Reports

declare
    @list varchar(7000),
    @lasti int

select
    @list = '',
    @lasti = -1

--here is the meat of the work
update
    #workingtable
set
    @list = list = case
                       when @lasti <> CompanyID then Reports
                       else @list + ', ' + Reports
                   end,
    @lasti = CompanyID

--Insert into CompanySubscription
INSERT INTO CompanySubscriptionReports ( CompanyID, allowedAccounts )
select
    CompanyID ,
    case
        when len(max(list)) > 5000 then convert(varchar(5000), left(max(list),4700)
        else convert(varchar(5000),max(list))
    end as list
from
    #workingtable
group by
    CompanyID
order by
    newid()
go

--Remove Unused tables
drop table #workingtable
drop table #ReportSubscription
drop table #Tally
code]

Peryan77:

I suppose I misunderstood your intent. I thought you wanted to delete the whole row if it started with 00,01, or 02.

Instead try this:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create function dbo.ufsDelmListToTable
    (
            @vchList varchar(8000),
            @chDelimit char(1)
    )
/************************************************** **********
* Goal: Convert a delimited list into a table (each item as its own row)
* Created by David Lundell, Principal Consultant and Trainer
* Mutually Beneficial Inc Copyright 2006
* Provided as is with no warranties express or implied
************************************************** ***********
*/

RETURNS @tblList TABLE (vchChar varchar(255)) AS
begin
    declare @iStart int,
            @iEnd int

--Extract the list

    select @iStart = 1

    Select @iEnd = charindex(@chDelimit,@vchList)

    while @iEnd > 0
    begin
        insert @tblList (vchChar)
        select LTRIM(RTRIM(substring(@vchList, @iStart, @iEnd - @iStart)))

        select @iStart = @iEnd + 1

        Select @iEnd = charindex(@chDelimit,@vchList, @iStart)

    end

    --Get last value
    insert @tblList (vchChar)
    select LTRIM(RTRIM(substring(@vchList, @iStart, len(@vchList))))


return

end
/* Test Scenario
Select * from dbo.ufsDelmListToTable('1,2,3,4,5,6,7,8',',')
*/



SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create function dbo.ufs_DelElem_in_DelmList
    (
             @vchList varchar(8000)
            ,@chDelimit varchar(3)
            ,@DeleteMask varchar(10)
            ,@chNewDelimit varchar(3) = NULL
    )
/************************************************** **********
* Goal: Remove certain items from a delimited list @vchList
* (@chDelimit is the delimiter)
* based on @DeleteMask as the criteria for NOT LIKE
* and optionally replace the delimiter with @chNewDelimit
* Depends on ufsDelmListToTable
* Created by David Lundell, Principal Consultant and Trainer
* Mutually Beneficial Inc Copyright 2006
* Provided as is with no warranties express or implied
************************************************** ***********
*/
RETURNS varchar(8000) AS
begin
    declare @iListCount int,
            @iRowNum int,
            @vchstrList varchar(8000),
            @vchItem varchar(255)

    SELECT @chNewDelimit = ISNULL(@chNewDelimit,@chDelimit)

--Extract the list
DECLARE @tblList TABLE (tID int Identity(1,1) , vchChar varchar(255))

    INSERT @tblList (vchChar)
    SELECT vchChar
    FROM dbo.ufsDelmListToTable(@vchList, @chDelimit)
    WHERE vchChar NOT LIKE @DeleteMask

    SELECT @iListCount = (Select Count(*) from @tblList)

    SELECT @iRowNum = 1

    WHILE @iRowNum < @iListCount
    BEGIN
        SELECT TOP 1 @vchstrList = ISNULL(@vchstrList,'') + vchChar + @chNewDelimit
        FROM @tblList
        WHERE tID <= @iRowNum
        ORDER BY tID DESC

        SELECT @iRowNum = @iRowNum + 1
    END
    IF (@iRowNum = @iListCount)
    BEGIN
        SELECT TOP 1 @vchstrList = ISNULL(@vchstrList,'') + vchChar
        FROM @tblList
        WHERE tID <= @iRowNum
        ORDER BY tID DESC
    END

return (@vchstrList)

end
/* Test Scenario
CREATE TABLE #ActTable
    (actID int Identity(1,1)
    ,AllowedAccounts varchar(8000)
    )

INSERT #ActTable (AllowedAccounts) VALUES ('00_Report234, 01_Report393, 03_Report59, 04_Report81, 05_Reports84') -- first two
INSERT #ActTable (AllowedAccounts) VALUES ('00_Report234, 02_Report393, 03_Report59') -- first two
INSERT #ActTable (AllowedAccounts) VALUES ('03_Report234, 04_Report393, 05_Report59') -- won't be affected
INSERT #ActTable (AllowedAccounts) VALUES ('00_Report234, 01_Report393, 02_Report59') -- all accounts affected
INSERT #ActTable (AllowedAccounts) VALUES ('00_Report234, 05_Report393, 04_Report81, 05_Reports84, 01_Report59') -- first and last

SELECT actID, AllowedAccounts, dbo.ufs_DelElem_in_DelmList( AllowedAccounts, ',','0[0-2]%',', ')
FROM #ActTable

DROP TABLE #ActTable
*/





David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

David, I have one slight..well big problem, my field type in allowedAccounts is text since it is over 8000 characters. Any ideas on how to change your procedure to handle this.