April 28, 2011 at 11:47 am
Hi
Having searched for 'Using an Update Cursor in SQL' and only receiving replies that state that an alternative to the CURSOR be used, I have now decided to ask for your opinions.
Please examine the code below and advise if there is a set based way to improve it. I have a list of telephone numbers that I need to remove any non digits from.
----------- CREATE TABLE / VALUES ------------
if exists (select * from information_schema.tables where [table_name] = 'CLI_CleanUp')
begin
drop table CLI_CleanUp
end
create table CLI_CleanUp
(CLI varchar(20))
insert into CLI_CleanUp (CLI) values ('123456789')
insert into CLI_CleanUp (CLI) values ('123_456789')
insert into CLI_CleanUp (CLI) values ('0123456789')
insert into CLI_CleanUp (CLI) values ('1234567a89')
insert into CLI_CleanUp (CLI) values ('1234567 89')
insert into CLI_CleanUp (CLI) values ('#00000123456789')
insert into CLI_CleanUp (CLI) values ('')
insert into CLI_CleanUp (CLI) values (' ')
insert into CLI_CleanUp (CLI) values ('000000fffff')
------------- REMOVE NON DIGITS FROM CLI ------------
declare @temp varchar(20)
DECLARE Clean_cursor CURSOR
FOR SELECT CLI from CLI_CleanUp where patindex( '%[^0-9]%',CLI) <>0
FOR UPDATE
OPEN Clean_cursor
FETCH NEXT from Clean_cursor into @temp;
IF @@FETCH_STATUS <> 0
PRINT 'TABLE IS EMPTY' ;
WHILE @@FETCH_STATUS = 0
BEGIN
while patindex( '%[^0-9]%', @temp) <>0
begin
set @temp = stuff (@temp, patindex( '%[^0-9]%', @temp),1,'')
update CLI_CleanUp set CLI = @temp where CURRENT OF Clean_cursor end
FETCH NEXT from Clean_cursor into @temp;
END
CLOSE Clean_cursor;
DEALLOCATE Clean_cursor;
--------------- DELETE CLI THAT IS NOT BETWEEN 8 AND 11 DIGITS ------------
delete
--select *
from CLI_CleanUp
where LEN(CLI ) not between 8 and 11
----------------- OUTPUT RESULTS -------------------
select * from CLI_CleanUp
The cursor is used to remove non digits from one row at a time, as the telephone number has to be scanned and non digits removed one at a time. Please advise if this could be one use of
CURSORs in SQL Server, or do I need to replace STUFF with another function?
April 28, 2011 at 12:44 pm
How about this update statement?
UPDATE CLI_CleanUp
SET CLI = stuff (CLI, patindex( '%[^0-9]%', CLI),1,'')
WHERE patindex( '%[^0-9]%',CLI) <>0
April 28, 2011 at 1:01 pm
Chrissy321 (4/28/2011)
How about this update statement?UPDATE CLI_CleanUp
SET CLI = stuff (CLI, patindex( '%[^0-9]%', CLI),1,'')
WHERE patindex( '%[^0-9]%',CLI) <>0
This doesn't handle non-numeric values that aren't at the start of the string.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 28, 2011 at 1:03 pm
How's this work for you?
(You'll have to modify it to do the actual update; I'm just selecting the corrected data.)
DECLARE @CLI_CleanUp table
(CLI varchar(20))
insert into @CLI_CleanUp (CLI) values ('123456789')
insert into @CLI_CleanUp (CLI) values ('123_456789')
insert into @CLI_CleanUp (CLI) values ('0123456789')
insert into @CLI_CleanUp (CLI) values ('1234567a89')
insert into @CLI_CleanUp (CLI) values ('1234567 89')
insert into @CLI_CleanUp (CLI) values ('#00000123456789')
insert into @CLI_CleanUp (CLI) values ('')
insert into @CLI_CleanUp (CLI) values (' ')
insert into @CLI_CleanUp (CLI) values ('000000fffff');
-- For information on how a tally table works, please see the article
-- 'The "Numbers" or "Tally" Table - What it is and how it replaces a loop'
-- at http://qa.sqlservercentral.com/articles/T-SQL/62867/
;WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM TENS t1 CROSS JOIN TENS t2),
-- add a row number for each item in the cli_cleanup table so that it can be used to recompile the string.
cte AS (SELECT RowID = ROW_NUMBER() OVER (ORDER BY (SELECT 0)), CLI FROM @CLI_CleanUp),
cte2 AS
(
-- split the data in each row apart into individual characters.
-- keep the position number for when putting it back together
SELECT cte.RowID, ca.N, ca.ColChar
FROM cte
CROSS APPLY (SELECT N, ColChar = SUBSTRING(cte.CLI, N, 1)
FROM TALLY
WHERE N <= 20) ca -- 20 is the size of the field, no need to go higher
-- only get the numeric positions
WHERE ca.ColChar LIKE '[0-9]%'
)
SELECT RowID,
CleanData
FROM cte
-- put back together into the column order for this row.
CROSS APPLY (SELECT CleanData = (SELECT '' + ColChar
FROM cte2
WHERE RowID = cte.RowID
ORDER BY N
FOR XML PATH(''),TYPE).value('.','varchar(max)')) ds
WHERE CleanData IS NOT NULL
ORDER BY cte.RowID
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 28, 2011 at 1:39 pm
Thanks Wayne.
This seems to work. Will have to understand it and test it before I use it though :blush:. I've worked with CTE's but not a TALLY table. Thanks for the pointer :cool:, I never would have thought of this.
April 28, 2011 at 3:05 pm
No problem. If you have any questions, just ask away. As the tagline in my signature states, don't use the code in production until you can understand what it's doing.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 15, 2011 at 9:32 pm
WayneS (4/28/2011)
How's this work for you?
I know this post is a couple of weeks old BUT...
The answer to the quoted question is "not so good" in this case. This is one of the few places where a scalar function with a WHILE loop will beat the tar out of a cteTally table and send the dust bunnies running for cover.
Of course, I'd never ask anyone to just take my word for it so, here's the usual million rows of test data...
--===== Conditionally drop temp tables to make reruns easier in SSMS
IF OBJECT_ID('TempDB..#DirtyData','U') IS NOT NULL DROP TABLE #DirtyData;
SELECT TOP (1000000)
RowNum = IDENTITY(INT,1,1),
CleanMe = CAST(NEWID() AS VARCHAR(36))
INTO #DirtyData
FROM sys.all_columns ac1,
sys.all_columns ac2
;
Here's the "clean string" function that uses a WHILE loop...
CREATE FUNCTION dbo.CleanString
--===== Declare obviously named parameters
(
@pString VARCHAR(8000),
@KillPattern VARCHAR(256)
)
RETURNS VARCHAR(8000) WITH SCHEMABINDING AS
BEGIN
--===== Prime the pump
DECLARE @Posit INT
;
--===== See if there're any characters to kill
SELECT @Posit = PATINDEX('%'+@KillPattern+'%',@pString)
;
--===== If there are no characters to kill, skip the loop
-- If there are, then kill them in the loop using STUFF
WHILE @Posit > 0
SELECT @pString = STUFF(@pString,@Posit,1,''),
@Posit = PATINDEX('%'+@KillPattern+'%',@pString)
;
RETURN @pString
;
END
;
Ok... let's first make sure it works correctly...
--===== Prove that it works.
-- Note that the pattern is WHICH CHARACTERS TO KILL. So, to kill everything
-- but numeric digits, you have to tell it to NOT kill numeric digits
SELECT top 100 CleanMe, CleanedString = dbo.CleanString(CleanMe,'[^0-9]')
FROM #DirtyData
And now for the million row speed test... a cteTally function can't touch this for speed...
--drop table #MyHead
Go
--===== Speed Test
SELECT CleanMe, CleanedString = dbo.CleanString(CleanMe,'[^0-9]')
INTO #MyHead
FROM #DirtyData
;
Try that against anything you could write with a Tally Table or a cteTally. You will be amazed.
Yeah... I know. It's strange that the proverbial leader of the "Anti-RBAR Alliance" would actually endorse any form of RBAR but sometimes there are exceptions and this is one of them.
--Jeff Moden
May 15, 2011 at 9:35 pm
terrykzncs (4/28/2011)
Thanks Wayne.This seems to work. Will have to understand it and test it before I use it though :blush:. I've worked with CTE's but not a TALLY table. Thanks for the pointer :cool:, I never would have thought of this.
As you can tell by my article on the Tally Table, I'm normally the first to stand up and say, "USE A TALLY TABLE!!!". Not this time, though. Please see the post immediately above this one.
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply