September 4, 2015 at 5:45 pm
Hi Guys,
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
guidance is much appreciated. thanks!!
September 4, 2015 at 8:25 pm
If REPLICATE ('x', LEN(<columnname>)) is returning 'x' repeated to the field's maximum length, I'd check your data doesn't have trailing or leading spaces, and the data type is actually varchar (not char(30)).
September 4, 2015 at 9:22 pm
DataGuy (9/4/2015)
Hi Guys,I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
guidance is much appreciated. thanks!!
How are you making the string of "x" 's?
This code works, even on a column specified as a CHAR vs. VARCHAR:
DECLARE @test-2 TABLE (RowID INTEGER IDENTITY, TextCol CHAR(8000));
INSERT INTO @test-2
(TextCol)
VALUES ('thisisavalue'),
('thisisanothervalue'),
('thisisanothervalueagain'),
('shortval');
SELECT *,
REPLICATE('x', LEN(TextCol))
FROM @test-2
This returns:
RowID TextCol
----------- ----------------------- --------------------------
1 thisisavalue xxxxxxxxxxxx
2 thisisanothervalue xxxxxxxxxxxxxxxxxx
3 thisisanothervalueagain xxxxxxxxxxxxxxxxxxxxxxx
4 shortval xxxxxxxx
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 5, 2015 at 5:16 am
Quick thought, make certain that you use an inline table value function and NOT a scalar function, the performance of the latter is horrendous, see the simple test harness and the comparison below.
😎
Test harness
USE tempdb;
GO
SET NOCOUNT ON;
GO
DECLARE @SAMPLE_SIZE INT = 1000000; -- Size of the test set
DECLARE @BASE_LENGTH INT = 50;
DECLARE @BASE_VARIANCE INT = 49;
DECLARE @WORD_LENGTH INT = 7;
DECLARE @WORD_VARIANCE INT = 6;
DECLARE @DELIMITER CHAR(1) = CHAR(32);
DECLARE @INT_BUCKET INT = 0;
DECLARE @CHR_BUCKET VARCHAR(8000) = '';
DECLARE @timer TABLE (T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
/* View wrapper for using the NEWID() function
within a table value function
*/
IF OBJECT_ID(N'dbo.VNEWID') IS NULL
BEGIN
DECLARE @CREATE_VIEW NVARCHAR(MAX) = N'
CREATE VIEW dbo.VNEWID
AS
SELECT NEWID() AS NID;
'
EXEC (@CREATE_VIEW);
END
/* Test set generator, inspired by Lynn Pettis's random
string function
*/
IF OBJECT_ID(N'dbo.ITVFN_DO_SHAKESPEARE') IS NULL
BEGIN
DECLARE @CREATE_FUNCTION NVARCHAR(MAX) = N'
/*
Sample text set generator, having an infinite number of code
monkeys calling this function for infinite number of times
explains the name ;-)
2015-01-18
Eirikur Eiriksson
*/
CREATE FUNCTION dbo.ITVFN_DO_SHAKESPEARE
(
@BASE_LENGTH INT
,@BASE_VARIANCE INT
,@WORD_LENGTH INT
,@WORD_VARIANCE INT
,@ROWCOUNT INT
,@DELIMITER CHAR(1)
)
RETURNS TABLE
AS
RETURN
WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP (@BASE_LENGTH + @BASE_VARIANCE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4)
,RNUM(R) AS (SELECT TOP (@ROWCOUNT) ROW_NUMBER() OVER (ORDER BY (SELECT NULL) DESC) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
RN.R
,((SELECT TOP(@BASE_LENGTH + ((SELECT CHECKSUM(NID) FROM dbo.VNEWID) % @BASE_VARIANCE))
CASE
WHEN (NM.N + RN.R + (CHECKSUM(X.NID) % @WORD_LENGTH)) % @WORD_VARIANCE = 0 THEN @DELIMITER
ELSE CHAR(65 + (ABS(CHECKSUM(X.NID)) % 26))
END
FROM NUMS NM
CROSS APPLY dbo.VNEWID X
FOR XML PATH(''''), TYPE).value(''.[1]'',''VARCHAR(8000)'')) AS RND_TXT
FROM RNUM RN;
';
EXEC (@CREATE_FUNCTION);
END
IF OBJECT_ID(N'dbo.TBL_SAMPLE_STRING') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_STRING;
CREATE TABLE dbo.TBL_SAMPLE_STRING
(
SST_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_STRING_SST_ID PRIMARY KEY CLUSTERED
,SST_VALUE VARCHAR(500) NOT NULL
);
/* Populate the text sample */
INSERT INTO dbo.TBL_SAMPLE_STRING (SST_ID, SST_VALUE)
SELECT
X.R
,X.RND_TXT
FROM dbo.ITVFN_DO_SHAKESPEARE(@BASE_LENGTH,@BASE_VARIANCE,@WORD_LENGTH,@WORD_VARIANCE,@SAMPLE_SIZE,@DELIMITER) AS X;
/* iTVFN function */
IF OBJECT_ID(N'dbo.ITVFN_MASK_TEXT_TO_LENGTH') IS NOT NULL
BEGIN
DROP FUNCTION dbo.ITVFN_MASK_TEXT_TO_LENGTH;
END
SELECT @CREATE_FUNCTION = N'CREATE FUNCTION dbo.ITVFN_MASK_TEXT_TO_LENGTH
(
@INPUT_STR VARCHAR(8000)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT REPLICATE(''X'',LEN(@INPUT_STR)) AS MASKED_STRING
';
EXEC (@CREATE_FUNCTION);
/* Scalar function */
IF OBJECT_ID(N'dbo.SFN_MASK_TEXT_TO_LENGTH') IS NOT NULL
BEGIN
DROP FUNCTION dbo.SFN_MASK_TEXT_TO_LENGTH;
END
SELECT @CREATE_FUNCTION = N'CREATE FUNCTION dbo.SFN_MASK_TEXT_TO_LENGTH
(
@INPUT_STR VARCHAR(8000)
)
RETURNS VARCHAR(8000)
WITH SCHEMABINDING
AS
BEGIN
RETURN (SELECT REPLICATE(''X'',LEN(@INPUT_STR)))
END
';
EXEC (@CREATE_FUNCTION);
/* Run the test */
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
SELECT TOP (1000000)
@INT_BUCKET = SS.SST_ID
,@CHR_BUCKET = REPLICATE('X',LEN(SS.SST_VALUE))
FROM dbo.TBL_SAMPLE_STRING SS
INSERT INTO @timer(T_TEXT) VALUES('DRY RUN');
INSERT INTO @timer(T_TEXT) VALUES('REPLICATE');
SELECT TOP (1000000)
@INT_BUCKET = SS.SST_ID
,@CHR_BUCKET = REPLICATE('X',LEN(SS.SST_VALUE)) --AS MASKED_STR
FROM dbo.TBL_SAMPLE_STRING SS
INSERT INTO @timer(T_TEXT) VALUES('REPLICATE');
INSERT INTO @timer(T_TEXT) VALUES('SCALAR FUNCTION');
SELECT TOP (1000000)
@INT_BUCKET = SS.SST_ID
,@CHR_BUCKET = dbo.SFN_MASK_TEXT_TO_LENGTH(SS.SST_VALUE) --AS MASKED_STR
FROM dbo.TBL_SAMPLE_STRING SS
INSERT INTO @timer(T_TEXT) VALUES('SCALAR FUNCTION');
INSERT INTO @timer(T_TEXT) VALUES('ITV FUNCTION');
SELECT TOP (1000000)
@INT_BUCKET = SS.SST_ID
,@CHR_BUCKET = MASKED.MASKED_STRING --AS MASKED_STR
FROM dbo.TBL_SAMPLE_STRING SS
CROSS APPLY dbo.ITVFN_MASK_TEXT_TO_LENGTH(SS.SST_VALUE) AS MASKED
INSERT INTO @timer(T_TEXT) VALUES('ITV FUNCTION');
SELECT
T.T_TEXT
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @timer T
GROUP BY T.T_TEXT
ORDER BY DURATION;
Results (old i5 laptop)
+----------------+-----------+
|T_TEXT | DURATION |
+----------------+-----------+
|DRY RUN | 461026 |
|ITV FUNCTION | 471026 |
|REPLICATE | 508029 |
|SCALAR FUNCTION | 4425254 |
+----------------+-----------+
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply