March 31, 2016 at 6:28 pm
Ed Wagner (3/31/2016)
Between this and Sergiy's post, now I can't wait to start playing. 😉
Sergiy's method is the same method as "Section 7" of the outline we worked on for the Pre-Con.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 31, 2016 at 7:22 pm
Sergiy (3/31/2016)
drew.allen (3/31/2016)
It's actually MUCH faster to create a table using the VALUES expression, because most of the cost is writing and reading the temp table to tempdb. If you use this frequently, you might want to create an Inline Table-Valued Function to create the translation table.Drew
That's very questionable.
I have created 2 scalar functions, one reading mapping from a table, another one having it hardcoded:
I specifically said Inline Table-Valued Function, precisely because they are inlined. Scalar functions are never inlined, which is why it can make sense to create an inline table-valued function that returns a single row and column instead of creating the corresponding scalar function.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 31, 2016 at 7:44 pm
Sergiy (3/31/2016)
drew.allen (3/31/2016)
It's actually MUCH faster to create a table using the VALUES expression, because most of the cost is writing and reading the temp table to tempdb. If you use this frequently, you might want to create an Inline Table-Valued Function to create the translation table.Drew
Are you suggesting hardcoding the mapping data?
Yes, I am suggesting hard-coding the mapping data.
Instead of placing it into a static table updateable from an application?
Ah-tah-tah! Bad boy, bad boy!
:hehe:
Hard-coding the translation table is not necessarily a bad idea if the translation table is relatively small and the table rarely, if ever, changes. You need to know the business case when evaluating different alternatives, and we simply don't enough about the business case to discard this approach out-of-hand.
Also, I believe that it's possible to create a trigger on a physical table that will dynamically alter the inline table-valued function whenever the physical table changes, so that you can update the physical table from your application and still have the performance of an inline table-valued function. I'm not recommending that approach, but I think that it is possible.
Drew
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 31, 2016 at 8:10 pm
drew.allen (3/31/2016)
I specifically said Inline Table-Valued Function, precisely because they are inlined. Scalar functions are never inlined, which is why it can make sense to create an inline table-valued function that returns a single row and column instead of creating the corresponding scalar function.Drew
To be honest, I cannot see how to fit the keyword UPDATE into a ITF.
Therefore I just ignored the speculations about using ITF in this case as irrelevant.
If you can give at least a hint how can it be done, I'd really appreciate it, as those scalar functions executed once per row make me somehow sad too.
_____________
Code for TallyGenerator
March 31, 2016 at 8:43 pm
drew.allen (3/31/2016)
Hard-coding the translation table is not necessarily a bad idea if the translation table is relatively small and the table rarely, if ever, changes. You need to know the business case when evaluating different alternatives, and we simply don't enough about the business case to discard this approach out-of-hand.
Still bad.
If you have any kind of change control on the system then a smallest change in the mapping would require going through the whole development-deployment cycle.
Those BA's will be quick on adding up the overhead costs of the hardcoding approach.
And if you don't have a change control than your system most likely is not worth anything anyway, so its performance does not really matter.
Also, I believe that it's possible to create a trigger on a physical table that will dynamically alter the inline table-valued function whenever the physical table changes, so that you can update the physical table from your application and still have the performance of an inline table-valued function. I'm not recommending that approach, but I think that it is possible.
Drew
Drew
Yep, it's totally possible.
I like to play these games myself very much.
But it does not go well when it comes to code audit.
Those guys do not easily accept dynamically created code.
With all the bells and whistles they require to put into such triggers (data validation, error handling, logging, etc.) the trigger would become so complicated that I would not let its maintenance to a random DBA/DBdev.
_____________
Code for TallyGenerator
March 31, 2016 at 9:09 pm
Sergiy (3/31/2016)
drew.allen (3/31/2016)
I specifically said Inline Table-Valued Function, precisely because they are inlined. Scalar functions are never inlined, which is why it can make sense to create an inline table-valued function that returns a single row and column instead of creating the corresponding scalar function.Drew
To be honest, I cannot see how to fit the keyword UPDATE into a ITF.
Therefore I just ignored the speculations about using ITF in this case as irrelevant.
If you can give at least a hint how can it be done, I'd really appreciate it, as those scalar functions executed once per row make me somehow sad too.
This suggestion is specifically for the case:
SELECT @string = REPLACE(@string, from_char, to_char)
FROM dbo.YourTranslationTable();
No UPDATE required.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 31, 2016 at 9:22 pm
Sergiy (3/31/2016)
drew.allen (3/31/2016)
Hard-coding the translation table is not necessarily a bad idea if the translation table is relatively small and the table rarely, if ever, changes. You need to know the business case when evaluating different alternatives, and we simply don't enough about the business case to discard this approach out-of-hand.Still bad.
If you have any kind of change control on the system then a smallest change in the mapping would require going through the whole development-deployment cycle.
Those BA's will be quick on adding up the overhead costs of the hardcoding approach.
And if you don't have a change control than your system most likely is not worth anything anyway, so its performance does not really matter.
Also, I believe that it's possible to create a trigger on a physical table that will dynamically alter the inline table-valued function whenever the physical table changes, so that you can update the physical table from your application and still have the performance of an inline table-valued function. I'm not recommending that approach, but I think that it is possible.
Drew
Drew
Yep, it's totally possible.
I like to play these games myself very much.
But it does not go well when it comes to code audit.
Those guys do not easily accept dynamically created code.
With all the bells and whistles they require to put into such triggers (data validation, error handling, logging, etc.) the trigger would become so complicated that I would not let its maintenance to a random DBA/DBdev.
Again, you need to know the business requirements. If your table changes once every 5 years, 10 years, 20 years, ...., but the replacement is made billions of times a day, it may very well be worth the relatively small amount of code review required to change it.
Also, there are cases where table values are critical enough that they should undergo change management, so the fact that it's in a table doesn't mean that you won't have change management to go through.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 31, 2016 at 10:05 pm
Ed Wagner (3/31/2016)
Alan.B (3/30/2016)
This is the kind of thing that you can do with a Translate function (which T-SQL does not have). I generally don't like scalar UDFs but this guy is faster than anything I could do with an inline table valued function.
CREATE FUNCTION dbo.Translate8K(
@String varchar(8000),
@SearchPattern varchar(100),
@ReplacePattern varchar(100)
)
/****************************************************************************************
Purpose:
Function takes and input string (@string) and replaces all instances of each each
character in @string with that it exists in @SearchPattern with the corresponding
character in @ReplacePattern. For exmample, given the string "abc123abc",
@SearchPattern "ab" and @ReplacePatern of "XZ". Translate8K will replace each letter "a"
that exists in "abc123abc" with the letter X. Then every "b" that exists with a "Z".
This: SELECT dbo.Translate8K('ba!!!ab', 'ab', 'XZ'); will return ZX!!!XZ.
Parameters:
@String = varchar(8000); The input string to translate
@SearchPattern = varchar(100); The string that will be searched for in @String
@ReplacePattern = varchar(100); All characters in the @SearchPattern are replaced with
their corresponding character in the @SearchPattern
Returns: varchar(8000)
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 @SearchPattern is longer than @ReplacePattern then characters in @SearchPattern
that have no corresponding characters in @ReplacePattern will be removed. Using the
above example, if we remove the letter "Z" from like this:
SELECT dbo.Translate8K('ba!!!ab', 'ab', 'X') -- returns: X!!!X
3. When @ReplacePattern is longer than @SearchPattern the replacement characters that
the characters in @ReplacePattern past without a corresponding character in
@SearchPattern are ignored.
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) Format a phone number
-- format phone (atomic value)
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
****************************************************************************************/
RETURNS varchar(8000) WITH SCHEMABINDING AS
BEGIN
WITH E1(N) AS
(
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)
) t(N)
),
iTally(N) AS
(
SELECT TOP(DATALENGTH(@SearchPattern)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
FROM E1 a CROSS JOIN E1 b
)
SELECT
@string = REPLACE
(
@string COLLATE Latin1_General_BIN,
SUBSTRING(@SearchPattern,n,1),
SUBSTRING(@ReplacePattern,n,1)
)
FROM iTally;
RETURN @string;
END;
GO
Now that it the Oracle TRANSLATE function. Very nice, Alan. Thanks.
Between this and Sergiy's post, now I can't wait to start playing. 😉
Thanks Ed.
A few RDBMS have a TRANSLATE function in addition to Oracle such as Postgres, DB2 and Teradata as well as other programming
languages and applications such like XSLT, SAS, Hive, Python (just removes strings), Informatica and Infopath. It's something I've been playing around with for a couple years.
-- Itzik Ben-Gan 2001
March 31, 2016 at 10:49 pm
Alan.B (3/30/2016)
This is the kind of thing that you can do with a Translate function (which T-SQL does not have). I generally don't like scalar UDFs but this guy is faster than anything I could do with an inline table valued function.
CREATE FUNCTION dbo.Translate8K(
@String varchar(8000),
@SearchPattern varchar(100),
@ReplacePattern varchar(100)
)
/****************************************************************************************
Purpose:
Function takes and input string (@string) and replaces all instances of each each
character in @string with that it exists in @SearchPattern with the corresponding
character in @ReplacePattern. For exmample, given the string "abc123abc",
@SearchPattern "ab" and @ReplacePatern of "XZ". Translate8K will replace each letter "a"
that exists in "abc123abc" with the letter X. Then every "b" that exists with a "Z".
This: SELECT dbo.Translate8K('ba!!!ab', 'ab', 'XZ'); will return ZX!!!XZ.
Parameters:
@String = varchar(8000); The input string to translate
@SearchPattern = varchar(100); The string that will be searched for in @String
@ReplacePattern = varchar(100); All characters in the @SearchPattern are replaced with
their corresponding character in the @SearchPattern
Returns: varchar(8000)
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 @SearchPattern is longer than @ReplacePattern then characters in @SearchPattern
that have no corresponding characters in @ReplacePattern will be removed. Using the
above example, if we remove the letter "Z" from like this:
SELECT dbo.Translate8K('ba!!!ab', 'ab', 'X') -- returns: X!!!X
3. When @ReplacePattern is longer than @SearchPattern the replacement characters that
the characters in @ReplacePattern past without a corresponding character in
@SearchPattern are ignored.
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) Format a phone number
-- format phone (atomic value)
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
****************************************************************************************/
RETURNS varchar(8000) WITH SCHEMABINDING AS
BEGIN
WITH E1(N) AS
(
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)
) t(N)
),
iTally(N) AS
(
SELECT TOP(DATALENGTH(@SearchPattern)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
FROM E1 a CROSS JOIN E1 b
)
SELECT
@string = REPLACE
(
@string COLLATE Latin1_General_BIN,
SUBSTRING(@SearchPattern,n,1),
SUBSTRING(@ReplacePattern,n,1)
)
FROM iTally;
RETURN @string;
END;
GO
Hi Alan
Looking at your function, I have 2 questions for my education.
The CROSS JOIN in iTally will thus result in TOP(DATALENGTH(@SearchPattern)) of 8100, when the max value of DATALENGTH(@SearchPattern) is 100.
Not sure if it will have a performance impact, but if E1(N) returned only 10 items, then the CROSS JOIN in iTally will result in TOP(DATALENGTH(@SearchPattern)) of 100.
April 1, 2016 at 8:02 am
Jeff Moden (3/31/2016)
Ed Wagner (3/31/2016)
Between this and Sergiy's post, now I can't wait to start playing. 😉
Sergiy's method is the same method as "Section 7" of the outline we worked on for the Pre-Con.
Sweet. I certainly have some performance testing to do and some plans to look at tonight. 😀
April 1, 2016 at 8:38 am
DesNorton (3/31/2016)
Alan.B (3/30/2016)
This is the kind of thing that you can do with a Translate function (which T-SQL does not have). I generally don't like scalar UDFs but this guy is faster than anything I could do with an inline table valued function.
CREATE FUNCTION dbo.Translate8K(
@String varchar(8000),
@SearchPattern varchar(100),
@ReplacePattern varchar(100)
)
/****************************************************************************************
Purpose:
Function takes and input string (@string) and replaces all instances of each each
character in @string with that it exists in @SearchPattern with the corresponding
character in @ReplacePattern. For exmample, given the string "abc123abc",
@SearchPattern "ab" and @ReplacePatern of "XZ". Translate8K will replace each letter "a"
that exists in "abc123abc" with the letter X. Then every "b" that exists with a "Z".
This: SELECT dbo.Translate8K('ba!!!ab', 'ab', 'XZ'); will return ZX!!!XZ.
Parameters:
@String = varchar(8000); The input string to translate
@SearchPattern = varchar(100); The string that will be searched for in @String
@ReplacePattern = varchar(100); All characters in the @SearchPattern are replaced with
their corresponding character in the @SearchPattern
Returns: varchar(8000)
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 @SearchPattern is longer than @ReplacePattern then characters in @SearchPattern
that have no corresponding characters in @ReplacePattern will be removed. Using the
above example, if we remove the letter "Z" from like this:
SELECT dbo.Translate8K('ba!!!ab', 'ab', 'X') -- returns: X!!!X
3. When @ReplacePattern is longer than @SearchPattern the replacement characters that
the characters in @ReplacePattern past without a corresponding character in
@SearchPattern are ignored.
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) Format a phone number
-- format phone (atomic value)
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
****************************************************************************************/
RETURNS varchar(8000) WITH SCHEMABINDING AS
BEGIN
WITH E1(N) AS
(
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)
) t(N)
),
iTally(N) AS
(
SELECT TOP(DATALENGTH(@SearchPattern)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT 1)))
FROM E1 a CROSS JOIN E1 b
)
SELECT
@string = REPLACE
(
@string COLLATE Latin1_General_BIN,
SUBSTRING(@SearchPattern,n,1),
SUBSTRING(@ReplacePattern,n,1)
)
FROM iTally;
RETURN @string;
END;
GO
Hi Alan
Looking at your function, I have 2 questions for my education.
- Is there a reason why E1(N) returns 90 items when @SearchPattern and @ReplacePattern have a length of 100?
The CROSS JOIN in iTally will thus result in TOP(DATALENGTH(@SearchPattern)) of 8100, when the max value of DATALENGTH(@SearchPattern) is 100.
Not sure if it will have a performance impact, but if E1(N) returned only 10 items, then the CROSS JOIN in iTally will result in TOP(DATALENGTH(@SearchPattern)) of 100.
- Is there a reason why you use DATALENGTH() instead of LEN()? The parameters are all varchar and not nvarchar
Regarding the first question - that's an excellent observation, good catch. When I was writing the function I hadn't decided how long the search pattern would be. You are correct, only 100 rows is certainly enough and I'must going to change that.
Having that many rows, however, would not hurt performance because, thanks to my TOP clause, no more than 100 rows would ever get generated. The optimizer is smart like that.
Regarding LEN vs DATALENGTH, LEN does not count trailing spaces. @searchpattern ended with a space then the space would be ignored.
-- Itzik Ben-Gan 2001
April 1, 2016 at 10:51 pm
I'm going to recommend that anyone using this "numbering" system have a very large bank account and a good lawyer. It's amazing how offense some of the words that will be spelled out of certain numbers will be and that's just in U.S. English.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2016 at 11:53 pm
Alan.B (4/1/2016)
Regarding the first question - that's an excellent observation, good catch. When I was writing the function I hadn't decided how long the search pattern would be. You are correct, only 100 rows is certainly enough and I'must going to change that.
Having that many rows, however, would not hurt performance because, thanks to my TOP clause, no more than 100 rows would ever get generated. The optimizer is smart like that.
Regarding LEN vs DATALENGTH, LEN does not count trailing spaces. @searchpattern ended with a space then the space would be ignored.
Thank you. I never knew, or bothered to read about LEN and the trailing space issue.
April 9, 2016 at 7:26 pm
You folks aren't listening. Send the translated number 4514100 to someone that's a little sensitive or just wants the extra cash and find out what your boss will think of you when the company gets sued. There are other offensive words in even this limited numbering system but I figure that one will get my point across. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply