February 18, 2014 at 1:58 am
Hi Everyone,
I am trying to replace a set of characters which a user inputs from a given string.
I got it to work for a single string as follows:
Declare @string varchar(400), @items varchar(100), @itemCount int, @sql varchar(MAX), @replacement char(1), @sql1 varchar(MAX)
Select @items = 'a,b', @string = 'A quick brown dog jumped over the lazy fox.', @replacement = 'z'
Select @itemCount = ((len(@items) - LEN(replace(@items,',',''))) + 1)
Select @sql = 'Select ' + REPLICATE('Replace(', @itemCount)
Select @sql1 = Char(39) + @string + Char(39) + ', ' + Char(39) + REPLACE(@items, ',', ''', ''' + @replacement + '''),''') + ''', ''' + @replacement + ''')'
Execute(@sql)
The problem with this is that I can't use it as a Scalar UDF(to work on an entire column) as it uses Dynamic SQL. Is there a work around to this? Can I implement something like this as a Scalar function for an entire column of a table?
Please help guys.
Here is some sample data that you can use to work with:
Declare @table table(Email varchar(1000))
Insert into @table
SELECT '886666999.com'
UNION ALL
SELECT 'b.gomez.paz.net'
UNION ALL
SELECT 'fenxts.com.cn'
UNION ALL
SELECT 'houmq.ac.cn'
UNION ALL
SELECT 'jiefu992000.com'
UNION ALL
SELECT 'l.miotti.it'
UNION ALL
SELECT 'laporte.danielle.fr'
UNION ALL
SELECT 'love6465.com.cn'
UNION ALL
SELECT 'mininoferoz.com'
UNION ALL
SELECT 'nvsunjia.com'
February 18, 2014 at 7:55 am
Here is what I did. I do not know if it is what you were looking for but I used a global temporary table.
create table ##t (Email varchar(1000))
Insert into ##t
SELECT '886666999.com'
UNION ALL
SELECT 'b.gomez.paz.net'
UNION ALL
SELECT 'fenxts.com.cn'
UNION ALL
SELECT 'houmq.ac.cn'
UNION ALL
SELECT 'jiefu992000.com'
UNION ALL
SELECT 'l.miotti.it'
UNION ALL
SELECT 'laporte.danielle.fr'
UNION ALL
SELECT 'love6465.com.cn'
UNION ALL
SELECT 'mininoferoz.com'
UNION ALL
SELECT 'nvsunjia.com'
Declare
@string varchar(400),
@items varchar(100),
@itemCount int,
@sql varchar(MAX),
@replacement char(1),
@sql1 varchar(MAX)
Select
@items = 'a,b',
@string = 'Email',
@replacement = 'z'
Select
@itemCount = ((len(@items) - LEN(replace(@items,',',''))) + 1)
Select @sql = 'Select ' + REPLICATE('Replace(', @itemCount)
Select @sql1 = @string + ', ' + Char(39) + REPLACE(@items, ',', ''', ''' + @replacement + '''),''') + ''', ''' + @replacement + ''') FROM ##t'
print @sql
Execute(@sql)
February 18, 2014 at 8:30 am
This might work for an iTVF. But I'm not sure is the best option with real data.
Note that it uses the DelimitedSplit8K that you can find here along with the explanation on how it works: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Declare @items varchar(100),
@replacement char(1)
Select @items = 'a,b',
@replacement = 'z';
WITH Items AS(
SELECT Item, ItemNumber
FROM DelimitedSplit8K(@items, ',')
),
rCTE AS(
SELECT REPLACE( Email, Item, @replacement) Email, 1 AS n
FROM @table t
JOIN Items i ON ItemNumber = 1
UNION ALL
SELECT REPLACE( Email, Item, @replacement), N + 1
FROM rCTE
JOIN Items i ON ItemNumber = n + 1
)
SELECT Email
FROM rCTE
WHERE N = (SELECT MAX(ItemNumber) FROM Items)
February 18, 2014 at 6:00 pm
I wrote this FUNCTION a long time ago. Note that it is case sensitive.
CREATE FUNCTION [dbo].[NestedReplace]
-- Author: D. Camps
-- Date: 24-Jan-2013
-- Remarks: NestedReplace is designed to repeatedly apply the REPLACE built-in function using
-- two delimited lists of target strings (those to be replaced in @Target) and source
-- strings (the final values to be replaced).
-- Note: Since strings are replaced in the order they are supplied, beware of dependencies
-- during successive replacements. The direction the replacement is applied may be changed
-- using the fifth parameter (@Direction).
--
-- For example, try this:
-- SELECT Left2Right=a.Item, Right2Left=b.Item
-- FROM NestedReplace('abcdd', 'abc,ddd', 'd,ee', NULL, NULL) a
-- CROSS APPLY NestedReplace('abcdd', 'abc,ddd', 'd,ee', NULL, 'R') b
-- Left2Right Right2Left
-- First replace results in: ddd abcdd
-- Second replace results in: ee ddd
(
-- On calling the FUNCTION this is the source string. Upon completion it holds the target (replaces applied).
@Target VARCHAR(8000),
-- Delimited list of strings to be replaced
@ToReplaceList VARCHAR(8000),
-- Delimited list of strings that replace the list in @ToReplaceList
@ReplaceWithList VARCHAR(8000),
-- The delmiter character for both lists: defaults to comma if NULL specified
@Delim CHAR(1),
-- Direction that the replacements are applied: LEFT (to Right) or RIGHT (to Left)
@Direction VARCHAR(5)
)
RETURNS @Results TABLE (Item VARCHAR(8000))
WITH SCHEMABINDING
AS
BEGIN
DECLARE @SplitStrings TABLE
-- CLUSTERED INDEX to drive the QU
(ID INT PRIMARY KEY CLUSTERED, Item VARCHAR(8000));
-- Defaults for input parameters 4 and 5
SELECT @Delim = LEFT(ISNULL(@Delim, ','), 1), @Direction = LEFT(ISNULL(@Direction, 'L'),1);
-- Initialize the table from the strings to be replaced (@ToReplaceList)
INSERT INTO @SplitStrings
SELECT ItemNumber * CASE @Direction WHEN 'R' THEN -1 ELSE 1 END, Item
FROM dbo.DelimitedSplit8K(@ToReplaceList, @Delim);
-- QU the strings in the table with the nested replacements, while
-- retaining the final result in @Target
UPDATE a
SET @Target = REPLACE(@Target COLLATE Latin1_General_BIN, a.Item, b.Item)
,Item = @Target
FROM @SplitStrings a
-- This section splits the list of items that will replace the original strings
INNER JOIN (
SELECT ItemNumber=ItemNumber * CASE @Direction WHEN 'R' THEN -1 ELSE 1 END, Item
FROM dbo.DelimitedSplit8K(@ReplaceWithList, @Delim)) b
ON a.ID = b.ItemNumber
OPTION (MAXDOP 1);
-- Put the resulting string into the @Results table
INSERT INTO @Results SELECT @Target;
RETURN;
END
GO
DECLARE @string VARCHAR(8000) = 'A quick brown dog jumped over the lazy fox.';
SELECT YourString=@string, *
FROM dbo.NestedReplace(@string, 'A,a,b', 'z,z,z', ',', 'LEFT');
Edit: And it also uses DelimitedSplit8K.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 27, 2016 at 7:25 am
We were looking for a CTE based solution, but that proved to be far too slow. We ended up making this function, while very different, initially inspired by yours.
In our informal testing, it is 20+ times faster than a nested CTE solution.
First, we use a user defined table type (rather than parsing the strings) to specify the strings to be replaced and their replacements. It has an ApplyOrder, usually the descending order of the length of the strings being replaced.
CREATE TYPE [dbo].[NestedReplaceTbl] AS TABLE(
[SearchFor] [nvarchar](max) NULL,
[ReplaceWith] [nvarchar](max) NULL,
[ApplyOrder] [int] NULL
)
The function, which simply calls replace (using the MAXDOP hint) repeatedly
CREATE FUNCTION [dbo].[NestedReplaceWithMap]
-- Based on (below) from http://www.sqlservercentral.com/Forums/Topic1542417-392-1.aspx
-- Author: Gabriel Garza
-- Date: 2016/04/20
-- Inspired by D. Camps NestedReplace () on http://www.sqlservercentral.com/Forums/Topic1542417-392-1.aspx
-- Remarks: NestedReplaceWithMap is designed to repeatedly apply the REPLACE built-in function using
-- a table map of strings and their replacements in a given order
(
-- On calling the FUNCTION this is the source string
@Target nvarchar(max),
-- Delimited list of strings to be replaced
@ReplaceMap dbo.NestedReplaceTbl READONLY
)
RETURNS @Results TABLE (Item nvarchar(max))
WITH SCHEMABINDING
AS
BEGIN
SELECT @Target = REPLACE (@Target, rpl.SearchFor, rpl.ReplaceWith)
FROM @ReplaceMap rpl
ORDER BY rpl.ApplyOrder
OPTION (MAXDOP 1);
-- Put the resulting string into the @Results table
INSERT INTO @Results SELECT @Target;
RETURN;
END
Unfortunately, you can't pass a CTE to it directly (a CTE is not compatible with a UDT Table), so you have to insert the replacement values into a table variable.
For example
declare @ReplValues dbo.NestedReplaceTbl;
with rplPairs as (
select *
from ( values
( 'Abbreviated','Abbrv')
,('Mini','Mn')
,(' - ','-')
,('Side','Sd')
,('Door','Dr')
,('Down','Dw')
,('Spot','Sp')
,('Destination','Dst')
,('Fluorescent','Flrsc')
,('Aluminum','Alm')
,('Back','Bk')
,('Black','Blk')
,('Yellow','Ylw')
) rplPairs ( SearchFor, ReplaceWith )
), replMap as (
select
SearchFor
, ReplaceWith
, ApplyOrder = ROW_NUMBER() over (order by len (SearchFor) desc )
from rplPairs
)
insert @ReplValues (SearchFor, ReplaceWith, ApplyOrder)
select SearchFor, ReplaceWith, ApplyOrder from replMap;
with SourceStrings as (
select *
from ( values
( 'Abbreviated')
,( 'Black Side Door')
,( 'Back Spot')
,( 'Yellow Dog')
) vls (OriginalText)
)
select distinct OriginalText, Abbreviated = tgt.Item
from
SourceStrings
cross apply [dbo].[NestedReplaceWithMap] (OriginalText, @ReplValues) tgt
Ouputs
OriginalText Abbreviated
--------------- ---------------------------
Abbreviated Abbrv
Back Spot Bk Sp
Black Side Door Blk Sd Dr
Yellow Dog Ylw Dog
April 27, 2016 at 10:36 pm
zootie (4/27/2016)
-- Inspired by D. Camps NestedReplace () on http://www.sqlservercentral.com/Forums/Topic1542417-392-1.aspx
You've written a very interesting function sir, very clever. That line above stood out to me. I don't know if you are aware but Dwain passed away a few months back. Seeing this makes me miss him even more. He was a great guy and even better mentor. I have a lot of code out that was inspired by Dwain one way or another. He was truly one of the best.
-- Itzik Ben-Gan 2001
April 27, 2016 at 10:42 pm
Two year old thread but other languages such as PL/SQL, XSLT & SAS have a "translate" function which is a single-character "nested replace" function. This is one of those extremely rare occasions where I could not come up with an inline table valued function that performs better than the scalar function below.
CREATE FUNCTION dbo.Translate8K
(
@String varchar(8000),
@FromChar varchar(100),
@ToChar varchar(100)
)
/****************************************************************************************
Purpose:
Takes an input string (@string) and returns a new string with all occurrences of each
character in @FromChar replaced by its corresponding character in @ToChar. Take the
input string "1122", a @FromChar value of "12" and a @ToChar value of "AB"; the result
of dbo.Translate8K('1122', '12', 'AB') would return "AABB". Each "1" is replaced by "A",
each "2" is replaced by "B".
Characters in @string that don't exist in @FromChar remain unchanged. Changing the above
example where @string is "112233", @FromChar is "AB" and @ToChar is "12"; the result of
dbo.Translate8K('112233', '12', 'AB') returns "AABB33". When @FromChar contains more
characters than @ToChar, the extra characters at the end of @FromChar that have no
corresponding characters in @ToChar are simply removed from @String and don't appear in
the return value.
Parameters:
@String = varchar(8000); The input string to "translate"
@FromChar = varchar(100); Containing a series of characters to search for in @String
@ToChar = varchar(100); All characters in the @FromChar will be replaced with the
corresponding character in the @ToChar.
Returns: varchar(8000); the "tranlated string"
Developer notes:
1. Scalar user defined functions (udf) generally perform badly and "Inline" scalar UDFs
generally perform much better. The only way to get this logic into an "Inline scalar
udf" would be to use a recursive CTE which, for this task, performs very badly. For
more about "in scalar UDFs" see:
http://www.sqlservercentral.com/Forums/Topic1338291-203-1.aspx
2. When @ToChar is longer than @FromChar the replacement characters that
the characters in @ToChar past without a corresponding character in
@FromChar are ignored.
3. Translate8K is deterministic. For more about deterministic functions see:
https://msdn.microsoft.com/en-us/library/ms178091.aspx
Usage Examples:
--===== (1) basic replace characters/remove characters;
-- replace a with A, c with C, b with x and remove $ and #
DECLARE @string1 varchar(20)='###$$$aaabbbccc$$$###';
SELECToriginal = @string, translated = dbo.Translate8K(@string1,'acb#$','ACx');
--===== (2) Apply a specific format to a phone number
DECLARE @string varchar(8000) = '(425) 555-1212';
SELECT original = @string, Translated = dbo.Translate8K(@string,')(','-');
--===== (3) hide phone numbers, retain existing format
WITH phoneNbrs(n,pn) AS
(
SELECT 1, '(425) 555-1212' UNION ALL SELECT 2, '425.555.1212' UNION ALL
SELECT 3, '425-555-1212' UNION ALL SELECT 4, '4255551212'
)
SELECT n, pn AS before, [after] = dbo.Translate8K(pn,x,y)
FROM phoneNbrs
CROSS APPLY (VALUES('()-.0123456789','()-.**********')) t(x,y);
--===== (4) Replace accent characters with normal characters (note the "double translate")
DECLARE
@string varchar(100) = 'Thë Quìck Greeñ Fox jumpëd over thë lázy dogs back!',
@special1 varchar(32) = 'áâãäæèïéìëíîçåñòóôöõàøúüûùýÁÃÄ',
@normal1 varchar(32) = 'aaaaaeieieiicanoooooaouuuuyAAAAA',
@special2 varchar(32) = 'ÆÈÏÉÌËÍÎÅÑÒÓÔÖÕÀØÚÜÛÙÝ!',
@normal2 varchar(32) = 'EIEIEIIANOOOOOAOUUUUY.';
SELECT
original = @string,
newstring =
dbo.Translate8K(dbo.Translate8K(@string,@special1,@normal1),@special2,@normal2);
------------------------------------------------------------------------------
Revision History:
Rev 00 - 20150518 - Initial Development - Alan Burstein
Rev 01 - 20160401 - Reduced the number of rows in the tally table to only 100 numbers
(the length of @ToChar). - Alan Burstein
****************************************************************************************/
RETURNS varchar(8000) WITH SCHEMABINDING AS
BEGIN
SELECT @string = REPLACE
(
@string COLLATE Latin1_General_BIN,
SUBSTRING(@FromChar,N,1),
SUBSTRING(@ToChar,N,1)
)
FROM
(
SELECT TOP(DATALENGTH(@FromChar)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
FROM (VALUES
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) t(N)
) iTally(N) -- 100 row tally table
RETURN @string;
END;
-- Itzik Ben-Gan 2001
April 28, 2016 at 6:47 am
No, I didn't know about Dwain's passing. I just wanted to give back since his code helped me come up with the solution to our issue. May more of us leave as much of a legacy that we are remembered after we leave this world.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply