This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.
COURSE of the MONTH
10 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
odd characters inserted at end of string during sql server update
Posted on 2016-08-15
I have this query, it was part of another ee question but I cannot find the id now.
the query is working but ive spotted some odd additions at the end of the string output which shouldnt be there
the query is below. ive highlighted the attachment to show whats added
ive attached an xml output, i suspect its that space at the end causing this, can i get rid of that during the update sql, if indeed thats what it is.
the query is working but ive spotted some odd additions at the end of the string output which shouldnt be there
the query is below. ive highlighted the attachment to show whats added
ive attached an xml output, i suspect its that space at the end causing this, can i get rid of that during the update sql, if indeed thats what it is.
<TblWords ClientCodeWordPosition="07130219__5" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &#x20;" />
<TblWords ClientCodeWordPosition="07130219_320_1" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &#x20;" />
<TblWords ClientCodeWordPosition="07130219_D_2" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &#x20;" />
<TblWords ClientCodeWordPosition="07130219_GT_3" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &#x20;" />
<TblWords ClientCodeWordPosition="07130219_LUXURY_4" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &#x20;" />
use Dictionary
go
WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
FROM tblwords w
LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
WHERE wt.word IS NULL)
UPDATE tblwords
SET StrShort = RTRIM((SELECT I.Word + ' '
FROM Filtered I
WHERE I.clientcode = tblwords.clientcode
ORDER BY i.WordPosition
FOR XML PATH('')))
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
10
4
14 Comments
Active today
I remember that question.
I don't think the issue is with the space since RTRIM is there to get rid of it. The issue might be with the last 6 chars so try this:
I don't think the issue is with the space since RTRIM is there to get rid of it. The issue might be with the last 6 chars so try this:
use Dictionary
go
WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
FROM tblwords w
LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
WHERE wt.word IS NULL)
UPDATE tblwords
SET StrShort = RTRIM((SELECT LEFT(I.Word, LEN(I.Word)-6) + ' '
FROM Filtered I
WHERE I.clientcode = tblwords.clientcode
ORDER BY i.WordPosition
FOR XML PATH('')))
Active 3 days ago
the output on this one had a where so as to produce 1 client code but what if that six vary in the other rows, anyway i will try it now see what happens.... it failed with this output message:
Msg 537, Level 16, State 3, Line 4
Invalid length parameter passed to the LEFT or SUBSTRING function.
Msg 537, Level 16, State 3, Line 4
Invalid length parameter passed to the LEFT or SUBSTRING function.
Active 3 days ago
so the sql is working fine, I just need to ensure that it doesnt update strshort on an unjoined row or maybe just look for the 6 characters and remove them after the event
Active today
Sorry, I just assumed by your example that all records have those 6 characters. So you might need to test it first:
use Dictionary
go
WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
FROM tblwords w
LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
WHERE wt.word IS NULL)
UPDATE tblwords
SET StrShort = RTRIM((SELECT CASE CHARINDEX(' ',I.Word)
WHEN 0 THEN I.Word + ' '
ELSE LEFT(I.Word, CHARINDEX(' ',I.Word)-1) + ' '
END Word
FROM Filtered I
WHERE I.clientcode = tblwords.clientcode
ORDER BY i.WordPosition
FOR XML PATH('')))
Active 3 days ago
Those odd characters are still returned and it returned tags which is not right it may be better for me to update the row after the function and remove those specific odd characters
heres a line its got tags of <word> and the offending characters as well.
heres a line its got tags of <word> and the offending characters as well.
ClientCodeWordPosition StrFull StrShort StrShortTag
13414B__4 VN1700 CLASSIC TOURER <Word>VN1700 </Word><Word>CLASSIC </Word><Word>TOURER </Word><Word> </Word> NULL
Active 3 days ago
i found a different type  A8 HYBRID  
so it can appear anywhere but always the same characters.
i am still reluctant to alter the working function and just have another update process to remove those
  without leaving duplicates spaces after
so it can appear anywhere but always the same characters.
i am still reluctant to alter the working function and just have another update process to remove those
  without leaving duplicates spaces after
Active 3 days ago
heres a xml for the one before.
<TblWords ClientCodeWordPosition="04134932__1" StrFull=" A8 HYBRID" StrShort="&#x20;A8 HYBRID &#x20;" />
<TblWords ClientCodeWordPosition="04134932__4" StrFull=" A8 HYBRID" StrShort="&#x20;A8 HYBRID &#x20;" />
<TblWords ClientCodeWordPosition="04134932_A8_2" StrFull=" A8 HYBRID" StrShort="&#x20;A8 HYBRID &#x20;" />
<TblWords ClientCodeWordPosition="04134932_HYBRID_3" StrFull=" A8 HYBRID" StrShort="&#x20;A8 HYBRID &#x20;" />
Active today
Okay, let try another approach. This one replaces all   with '' (empty string):
use Dictionary
go
WITH Filtered
AS ( SELECT W.Word, w.WordPosition, w.clientcode, w.ClientCodeWordPosition, w.StrShort
FROM tblwords w
LEFT JOIN tblwordtags wt ON W.clientcodeWordPosition = wt.clientcodeWordPosition
WHERE wt.word IS NULL)
UPDATE tblwords
SET StrShort = RTRIM((SELECT REPLACE(I.Word,' ','') + ' '
FROM Filtered I
WHERE I.clientcode = tblwords.clientcode
ORDER BY i.WordPosition
FOR XML PATH('')))
Active 3 days ago
as a trial i did an update on strfull with a left and right trim and the characters still appear so your right its not space related
Active 3 days ago
it didnt do the replace heres some output, i think its the right track though just a question as weather it can be done in function
ClientCodeWordPosition StrFull StrShort StrShortTag
04134932__1 A8 HYBRID  A8 HYBRID   NULL
04134932__4 A8 HYBRID  A8 HYBRID   NULL
04134932_A8_2 A8 HYBRID  A8 HYBRID   NULL
04134932_HYBRID_3 A8 HYBRID  A8 HYBRID   NULL
Active 3 days ago
ok using your idea I did this outside of that particular sql query
use Dictionary
update TblWords
set StrShort=REPLACE(StrShort,' ','')
Active 3 days ago
ok I took your approach and did it post update of strshort, the strings are now fine, its a pain but for now i think its ok, if it starts producing other strings then i will post back.
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed.
Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month10 days, 2 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.