May 1, 2009 at 7:09 am
Jeff,
Who needs pretty colours anyway?
Black-and-white courier new 10pt FTW!
I find it just as easy to read and it copies just fine into SSMS, every time.
The effort going into the server-side colouring pens seems out of proportion to the benefit.
code="plain" or "text" works fine too.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 7:22 am
With the prettifier, you get the option, but I wrote it originally just to get SQL Code into blogs that only allowed FONT tags. However, it was very quick to adapt to other uses, such as doing IF codes for SSC.
It is all in SQL and uses WHILE loops. They're faster!
You'll probably be allowed to turn off the colored stuff for your postings if you don't like it or it isn't appropriate for the code.
Best wishes,
Phil Factor
May 1, 2009 at 7:25 am
Jeff Moden (5/1/2009)
Florian Reischl (4/30/2009)
We had a performance issue in conjunction with STUFF? Maybe, I'm just gone blank... :doze:Maybe it's just me and I'm thinking of the wrong person. It was a post about using the Tally table to clean a string using the ol' split and concatenate method which, of course, is going to be quite a bit slower than a loop that uses STUFF because concatenation is so slow. I figured out a really simple way to use the Tally with STUFF. The problem was that I had to figure out a way make it work in the face of a string that was constantly changing length from left to right where the actual position of the bad characters was also constantly changing during the cleaning process.
I wanted to refer to that particular thread and I can't it.
Anyway, here's the code...
Nice approach!
I just don't understand one thing:
SELECT @CompanyName = LEFT(@CompanyName,6)
This causes only "ABClea" to be returned. Maybe a typo?
I tried another way using FOR XML PATH. I'm not sure if it is faster because it does not STUFF which reallocates the complete string or if it is slower because of the internal XML handling. I tried to setup a small test environment, but I have no idea how to bring your solution into a UPDATE statement :unsure:
[font="Courier New"]--===== Company name is in a variable
DECLARE @CompanyName NVARCHAR(256)
SELECT @CompanyName = 'A & B Cleaners'
PRINT @CompanyName --Just for verification... you can remove this line
--=== Use the Tally table to get ONLY the characters within the range of A to Z.
-- Use FOR XML to get them into one row
SELECT @CompanyName =
(
SELECT
SUBSTRING(@CompanyName, N, 1)
FROM dbo.Tally t
WHERE t.N <= LEN(@CompanyName)
AND SUBSTRING(@CompanyName, t.N, 1) LIKE '[A-Z]'
FOR XML PATH('')
)
--===== Display the result (just for verification... you can remove this line)
PRINT @CompanyName
[/font]
Greets
Flo
May 1, 2009 at 7:25 am
Phil Factor (5/1/2009)
It is all in SQL and uses WHILE loops. They're faster!
:laugh:
Very good.
Though I notice that you didn't say what they were faster than - flowing amber, perhaps? 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 7:29 am
Phil Factor (5/1/2009)
With the prettifier, you get the option, but I wrote it originally just to get SQL Code into blogs that only allowed FONT tags. However, it was very quick to adapt to other uses, such as doing IF codes for SSC.
Great tool Phil! I really like it and I know even non SSC active people using it. Thanks!
It is all in SQL and uses WHILE loops. They're faster!
:laugh::-D:laugh:
Faster than?
Greets
Flo
May 1, 2009 at 7:47 am
Jeff, Flo, Phil
You will be pleased to see me using a customized tally table here together with a WHILE loop...
[font="Courier New"]SETNOCOUNT ON;
--DROP TABLE dbo.UnwantedChars
CREATE TABLE dbo.UnwantedChars (BadChar VARCHAR(1) NOT NULL);
-- Populate a table with the chars we want to remove
DECLARE@char_codeINT,
@charVARCHAR(1);
SET@char_code = -1
WHILE@char_code <= 255
BEGIN
SELECT@char_code = @char_code + 1,
@char = CHAR(@char_code);
INSERTdbo.UnwantedChars(BadChar)
SELECT@char
WHEREPATINDEX('[^A-Z]', @char) = 1;
END;
-- Do the replace
DECLARE @CompanyName VARCHAR(256)
SET@CompanyName = 'A & B Cleaners';
UPDATEdbo.UnwantedChars
SET@CompanyName = CONVERT(VARCHAR(256), REPLACE(@CompanyName, BadChar, ''))
WHERECHARINDEX(BadChar, @CompanyName) != 0; -- Optional, delete this if you like
PRINT@CompanyName
[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 7:51 am
Forgot this bit:
[font="Courier New"]Table 'UnwantedChars'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0
CPU time = 0 ms, elapsed time = 0 ms.
[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 7:57 am
Paul, did you try your code? If I execute it I get "A & B Cleaners" as result.
May 1, 2009 at 8:07 am
Florian Reischl (5/1/2009)
Paul, did you try your code? If I execute it I get "A & B Cleaners" as result.
I did, and it worked.
Sadly I added the 'WHERE CHARINDEX' bit as I was posting :blush:
The parameters to the CHARINDEX function are the wrong way around.
I'll edit the post...
:sigh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 8:24 am
I also figured out the swapped parameters. Great solution!
Your bad-char look-up table takes zero milliseconds like Jeff's and mine. But your version needs only one logical read instead of 4 for the others. We can say your function takes only 0/4 milliseconds :-P. This will be a huge improvement for many data or many users!
May 1, 2009 at 8:29 am
:laugh:
I seem to be attracted to solutions which misuse the language somehow... :w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 9:13 am
Don't know if it is worth anything, but here is an In-Line TVF that will return 256 ASCII characters:
alter function dbo.ufnChar256()
returns table
as return(
with Numbers16 (
N
) as (
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 union all
select 16
),
Tally256 (
N
) as (
select
row_number() over (order by n1.N)
from
Numbers16 n1
cross join Numbers16 n2
)
select
char(N - 1) as AChar
from
Tally256)
;
go
May 1, 2009 at 9:31 am
Great idea Lynn!
Since Paul was 0/4 time due to only one logical read and your inline tally don't need any logical reads we can say this version needs (0/4)/8 milliseconds to process :hehe:
Statistics:
A & B Cleaners
--======================================================
-- Paul's Version
Table 'UnwantedChars'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
ABCleaners
--======================================================
-- Jeff's Version
Table 'Tally'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
ABCleaners
--======================================================
-- Lynn's Version
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
ABCleaners
BTW: Who writes the article about string cleanups? :laugh:
Greets
Flo
May 1, 2009 at 9:32 am
Lynn,
I'm thinking that that TVF might be adapted to accept a string of characters to keep as a parameter.
Returning a set of characters to strip would allow Jeff's solution to be set based on a table of strings to process.
Wouldn't work with my hack though I think 'cos I rely on operating on a variable, not a real table.
My attempt sucks badly on real tables, because it does n REPLACE operations per row.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 9:38 am
Paul White (5/1/2009)
Lynn,I'm thinking that that TVF might be adapted to accept a string of characters to keep as a parameter.
Returning a set of characters to strip would allow Jeff's solution to be set based on a table of strings to process.
Wouldn't work with my hack though I think 'cos I rely on operating on a variable, not a real table.
My attempt sucks badly on real tables, because it does n REPLACE operations per row.
Paul
Ah, padawan, set-based solution to populate your table I created, hmm.
Look for the good in the bad, you may see it.
Viewing 15 posts - 346 through 360 (of 522 total)
You must be logged in to reply to this topic. Login to reply