Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replacing multiple characters in a string Dynamically Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 1:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
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 + ''')'
Select @sql = @sql + @sql1
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'




Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1542417
Posted Tuesday, February 18, 2014 7:55 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 83, Visits: 395
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'
Select @sql = @sql + @sql1
print @sql
Execute(@sql)

Post #1542589
Posted Tuesday, February 18, 2014 8:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 2,763, Visits: 5,909
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)




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1542609
Posted Tuesday, February 18, 2014 6:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 3,590, Visits: 5,099
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1542814
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse