Replacing hidden characters
-
I'm to replace 2 hidden characters CHAR(0xC2) and CHAR(0x92).
SELECT CHAR(0xC2) CHAR1, CHAR(0x92) CHAR2
I can select them and replace them with this:SELECT REPLACE(Column1, CHAR(0xC2) + CHAR(0x92), '')
, but can't replace them when selecting from the table. Any ideas why?
There is an exception to every rule, except this one...
- SQLHeap - Tuesday, March 13, 2018 8:35 AM
Any chance that the characters are NOT contiguous? You could test for this as follows:
SELECT REPLACE(REPLACE(Column1, CHAR(0xC2), ''), CHAR(0x92), '')Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Tried that already, didn't work. Thanks for the reply though.
There is an exception to every rule, except this one...
-
Try doing a varbinary on the column in question and make sure that data is actually what you expect.
-
I don't know what the issue is here but you could use ngrams8k to perform an character by character analysis like this:
-- sample data
DECLARE @table table(someId int identity, someString varchar(100));
INSERT @table VALUES ('xxx123'),(CONCAT('abc',CHAR(0xC2),CHAR(0x92),'!'))-- character-by-character analysis
SELECT
t.someId,
t.someString,
ng.position,
ng.token,
ascii_value = ASCII(ng.token),
unicode_value = UNICODE(ng.token)
FROM @table t
CROSS APPLY dbo.ngrams8k(t.someString,1) ng;Returns:
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- SQLHeap - Tuesday, March 13, 2018 8:35 AM
Can you post the actual code that you're using when trying to select from the table along with the datatype and collation of the column in question, please?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, March 13, 2018 8:06 PM
Sure, but I realized late yesterday that since it's Unicode, I can just convert to Varchar and be done with it :Whistling:
DECLARE @varTable TABLE (ServerName NVARCHAR(20), JobName NVARCHAR(1000), Step INT, StepName NVARCHAR(1000), Message NVARCHAR(MAX)
, Status NVARCHAR(20), RunDate INT, RunTime INT, Duration INT)
DECLARE @Result NVARCHAR(MAX)= '', @Nchar NCHAR(1), @Position INT, @ServerName NVARCHAR(20) = 'COM01-SQL02', @JobName NVARCHAR(1000)
, @Step INT, @StepName NVARCHAR(1000), @Message NVARCHAR(MAX), @status NVARCHAR(20), @RunDate INT, @RunTime INT, @Duration INT
DECLARE curMessage CURSOR FOR
SELECT @ServerName, JobName = JOB.name, Step = HIST.step_id, StepName = HIST.step_name
, Message = LEFT(REPLACE(HIST.Message, '?', ''), 4000)
, Status = CASE WHEN HIST.run_status = 0 THEN 'Failed'
WHEN HIST.run_status = 1 THEN 'Succeeded'
WHEN HIST.run_status = 2 THEN 'Retry'
WHEN HIST.run_status = 3 THEN 'Canceled'
END, RunDate = HIST.run_date, RunTime = HIST.run_time, Duration = HIST.run_duration
FROM msdb.dbo.sysjobs JOB
INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id
WHERE HIST.Message LIKE '%?%'
ORDER BY HIST.run_date, HIST.run_time
OPEN curMessage
FETCH NEXT FROM curMessage INTO @ServerName, @JobName, @Step, @StepName, @Message, @status, @RunDate, @RunTime, @Duration
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Position = 1
SET @Result = ''
WHILE @Position <= LEN(@Message)
BEGIN
SET @Nchar = SUBSTRING(@Message, @Position, 1)
IF UNICODE(@Nchar) BETWEEN 32 AND 255
SET @Result = @Result + @Nchar
SET @Position = @Position + 1
END
select @Result
--INSERT INTO @varTable (ServerName, JobName, Step, StepName, Message, Status, RunDate, RunTime, Duration)
--SELECT @ServerName, @JobName, @Step, @StepName, @Result, @status, @RunDate, @RunTime, @Duration
--SELECT 'COM01-SQL02' AS ServerName, JobName = JOB.name, Step = HIST.step_id, StepName = HIST.step_name
--, Message = @Result
--, Status = CASE WHEN HIST.run_status = 0 THEN 'Failed'
--WHEN HIST.run_status = 1 THEN 'Succeeded'
--WHEN HIST.run_status = 2 THEN 'Retry'
--WHEN HIST.run_status = 3 THEN 'Canceled'
--END, RunDate = HIST.run_date, RunTime = HIST.run_time, Duration = HIST.run_duration
--FROM msdb.dbo.sysjobs JOB
--INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id
--WHERE HIST.Message LIKE '%?%'
FETCH NEXT FROM curMessage INTO @ServerName, @JobName, @Step, @StepName, @Message, @status, @RunDate, @RunTime, @Duration
END
CLOSE curMessage
DEALLOCATE curMessage
SELECT *
FROM @varTable
UNION
SELECT 'COM01-SQL02' AS ServerName, JobName = JOB.name, Step = HIST.step_id, StepName = HIST.step_name
, Message = LEFT(HIST.Message, 4000)
, Status = CASE WHEN HIST.run_status = 0 THEN 'Failed'
WHEN HIST.run_status = 1 THEN 'Succeeded'
WHEN HIST.run_status = 2 THEN 'Retry'
WHEN HIST.run_status = 3 THEN 'Canceled'
END, RunDate = HIST.run_date, RunTime = HIST.run_time, Duration = HIST.run_duration
FROM msdb.dbo.sysjobs JOB
INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id
WHERE HIST.Message NOT LIKE '%?%'
There is an exception to every rule, except this one...
- SQLHeap - Wednesday, March 14, 2018 6:22 AM
Such a conversion might not be the right thing to do. Also, that While Loop is going to cause performance to suffer a bit if you use it on something larger.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, March 14, 2018 2:17 PM
Just curious, why wouldn't the conversion be the right thing to do? And yes, a while loop in a cursor typically is horrible performance. It's RBAR^2 lol.
There is an exception to every rule, except this one...
- SQLHeap - Thursday, March 15, 2018 5:46 AM
The Conversion (appears to be a simple RBAR exclusion of characters here) may not be the thing to do because there actually could be data that needs to be NVARCHAR() to accommodate special characters in this world wide computing environment. You also have some hidden datatype mismatches and at least one of them may cause an error someday in the future. No object name in SQL Server will ever be longer than NVARCHAR(128) so things like having NVARCHAR(1000) for a job name or step name is serious overkill. That's why the "SYSNAME" alias datatype is an NVARCHAR(128) under the hood. That also means that a ServerName could also be much longer than just 20 characters. Because of the datatype mismatches, you're also doing implicit conversions all over the place and even the easy ones take extra clock cycles and a bit of memory.
I realize that you might think that "performance isn't important" here but it's always important and a very close second only to producing correct results. MSDB history can actually become very large thanks to job/step history and then there's the idea that however you've written this code might be what someone else uses as a model in a pinch.
And please understand that I'm not slamming you. Please consider this to be a peer review with some tips for not only this code, but for other code. Implicit conversions due to datatype/length matches (especially when it comes to join and filter criteria) and excluding characters just to make something work can and will become very serious problems in the future.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff wrote:
And please understand that I'm not slamming you. Please consider this to be a peer review with some tips for not only this code, but for other code. Implicit conversions due to datatype/length matches (especially when it comes to join and filter criteria) and excluding characters just to make something work can and will become very serious problems in the future.
Jeff, get off that high horse, why are you so mean to all the posters?
- Chris Hurlbut - Friday, March 16, 2018 6:52 AM
Heh... you shouldn't do such things to me before I've consumed an adequate supply of coffee. Having been shell-shocked by some of the folks over on StackOverflow in the past, I originally took that the wrong way. :blush:
It did wake me up for the meeting I have to go to today! 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply