August 13, 2019 at 3:22 am
Hi Forum,
I have an SP developed by Pinal Dave which is great for removing all text from a given table value. I want to apply the same logic via a Stored Proc that loops down a Column of a table, removes the text characters & then outputs the results to a temp table, external .csv or whatever is easiest.
Can anyone help here?
Thanks
SET NOCOUNT ON
DECLARE @loop INT
DECLARE @str VARCHAR(8000)
SELECT @str = 'ab123ce234fe'
SET @loop = 0
WHILE @loop < 26
BEGIN
SET @str = REPLACE(@str, CHAR(65 + @loop), '')
SET @loop = @loop + 1
END
SELECT @str
What I want to do is insert the code into a SQL Server Stored Proc & get the results to output.
My attempts with execute meant the request would run for ages.
Below is my attempt to do what I need but I'm missing an efficient output method.
Can anyone assist?
Thanks
USE [Reports]
GO
CREATE PROCEDURE sp_remove_ph_no_text (@Telephone VARCHAR(MAX))
AS
SET NOCOUNT ON
DECLARE @id INT
DECLARE @MAXCOUNT INT
SET @id = 1
SET @MAXCOUNT = (SELECT COUNT(id) FROM [dbo].[phone_dataset]) -- Table 'phone_dataset' has 2 Columns, 'id' & 'Telephone'
WHILE @id < @MAXCOUNT
BEGIN
SET @Telephone = (SELECT Telephone1 FROM [dbo].[phone_dataset] WHERE [id] = @id)
SET @Telephone = REPLACE(@Telephone, CHAR(65 + @id),'')
SET @id = @id + 1
--OUTPUT TO SOMEWHERE???
END
GO
--EXECUTE sp_remove_ph_no_text (Telephone)
August 13, 2019 at 5:36 am
First of all, using a loop in a stored procedure is one of the slowest things you can do. Only an rCTE (recursive CTE) would make it slower.
My suggestion is to read the following article to understand what a "TALLY Table" is in SQL Server so that you'll have a better understanding of the answer I'm working on for you. Here's the link...
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2019 at 5:40 am
Thanks Jeff,
I'm happy to learn, I'll have a read.
Thanks again.
August 13, 2019 at 5:52 am
Ok, first you need to follow the link to the "fnTally" function in my signature lines below this post. It's a high performance iTVF (inline Table Valued Fuction) that we can use in place of the normal inline cCTEs (cascading CTEs) that me and a lot of others have been using. This is one of the few times where a function calling a function isn't a bad thing.
Once you've created the fnTally function, then create this one. It has an interesting history where a fellow and good friend by the name of Eirikur Eiriksson and I kept build on the other's previous work to make things faster and faster for this type of thing. Here's the code.
CREATE FUNCTION dbo.DigitsOnly
/******************************************************************************************************************************
Purpose:
Given a VARCHAR(8000) or less string, return only the numeric digits from the string.
Programmer's Notes:
1. This is an iTVF (Inline Table Valued Function) that will be used as an iSF (Inline Scalar Function) in that it returns a
single value in the returned table and should normally be used in the FROM clause as with any other iTVF.
2. CHECKSUM r eturns an INT and will return the exact number given if given an INT to begin with. It's also faster than a CAST
or CONVERT and is used as a performance enhancer by changing the BIGINT of ROW_NUMBER() to a more appropriately sized INT.
3. Another performance enhancement is using a WHERE clause calculation to prevent the relatively expensive XML PATH
concatentation of empty strings normally determined by a CASE statement in the XML "loop".
4. Another performance enhancement is not making this function a generic function that could handle a pattern. That allows
us to use all integer math to do the comparison using the high speed ASCII function convert characters to their numeric
equivalent. ASCII characters 48 through 57 are the digit characters of 0 through 9.
5. Last but not least, added another of Eirikur's later optimizations using 0x7FFF which he says is a "simple trick to shift
all the negative values to the top of the range so a single operator can be applied, a lot less expensive than using
between.
Kudos:
1. Hats off to Eirikur Eiriksson for the ASCII conversion idea and for the reminders that dedicated functions will always
be faster than generic functions and that integer math beats the tar out of character comparisons that use LIKE or
PATINDEX.
2. Hats off to all of the good people that submitted and tested their code on the following thread. It's this type of
participation and interest that makes code better. You've just gotta love this commmunity.
http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx#bm1629360
Usage:
--===== CROSS APPLY example
SELECT ca.DigitsOnly
FROM dbo.SomeTable
CROSS APPLY dbo.DigitsOnly(SomeVarcharCol) ca
;
Revision History:
Rev 00 - 29 Oct 2014 - Jeff Moden - Initial Creation
Re 01 - 13 Aug 2019 - Jeff Moden - Simplified the code by changing the cascading CTEs that formed the sequence to a call to
an equivalent fnTally function and incorporate Eirikur's last performance enhancement.
******************************************************************************************************************************/
--===== Declare the I/O for this function
(@pString VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT DigitsOnly =
(
SELECT SUBSTRING(@pString,N,1)
FROM dbo.fnTally(1,ISNULL(LEN(@pString),0)) t
WHERE ((ASCII(SUBSTRING(@pString,N,1)) - 48) & 0x7FFF) < 10
ORDER BY t.N
FOR XML PATH('')
)
;
Once those two functions are in place, here's a couple of snippets of code to demonstrate how to use it and what is returned.
--===== Mock up the "phone_dataset" table for testing.
-- We'll use a TempTable for the test.
-- This is not a part of the solution. We're just making test data here.
SELECT ID = t.N
,Telephone = CONVERT(CHAR(36),NEWID()) --Just a simulation.
INTO #phone_dataset
FROM dbo.fnTally(1,10000) t -- Just testing on 10K rows.
;
--===== Use the function to return only the digits of the GUIDs
-- using the DigitsOnly function.
-- All you need to do is change the drop the "#" from the table
-- name and see if it works for your telephone numbers.
SELECT pds.ID
,pds.Telephone
,dig.DigitsOnly
FROM #phone_dataset pds
CROSS APPLY dbo.DigitsOnly(Telephone) dig
;
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2019 at 5:57 am
Thanks Jeff,
I'll have a read & give it a try.
Thanks again
August 13, 2019 at 6:08 am
Thanks Jeff,
I'll have a read & give it a try.
Thanks again
I hit submit by accident. I left a message at the top of the previous post. Please have a look. I'm still working on it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2019 at 6:26 am
Ok... my previous post with the code and some example code on how to use it are done. Read the comments in the demo code near the end.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply