Solved

odd characters inserted at end of string during sql server update

Posted on 2016-08-15
14
29 Views
Last Modified: 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.

<TblWords ClientCodeWordPosition="07130219__5" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &amp;#x20;" />
<TblWords ClientCodeWordPosition="07130219_320_1" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &amp;#x20;" />
<TblWords ClientCodeWordPosition="07130219_D_2" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &amp;#x20;" />
<TblWords ClientCodeWordPosition="07130219_GT_3" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &amp;#x20;" />
<TblWords ClientCodeWordPosition="07130219_LUXURY_4" StrFull="320 D GT LUXURY " StrShort="320 D GT LUXURY &amp;#x20;" />

Open in new window


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('')))

Open in new window

ee.JPG
0
Comment
Question by:PeterBaileyUk
[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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41756054
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:
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('')))

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41756125
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.
0
 

Author Comment

by:PeterBaileyUk
ID: 41756132
I believe its doing this where there are no rows in the join table.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:PeterBaileyUk
ID: 41756133
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
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41756134
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('&#x20;',I.Word)
			WHEN 0 THEN I.Word + ' '
			ELSE LEFT(I.Word, CHARINDEX('&#x20;',I.Word)-1) + ' '
         		END Word
		FROM Filtered I
		WHERE I.clientcode = tblwords.clientcode
		ORDER BY i.WordPosition
		FOR XML PATH('')))

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41756137
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.
ClientCodeWordPosition	StrFull	StrShort	StrShortTag
13414B__4	VN1700 CLASSIC TOURER	<Word>VN1700 </Word><Word>CLASSIC </Word><Word>TOURER </Word><Word>&#x20;</Word>	NULL

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41756139
i found a different type &#x20;A8 HYBRID &#x20;

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
&#x20; without leaving duplicates spaces after
0
 

Author Comment

by:PeterBaileyUk
ID: 41756140
heres a xml for the one before.

<TblWords ClientCodeWordPosition="04134932__1" StrFull=" A8 HYBRID" StrShort="&amp;#x20;A8 HYBRID &amp;#x20;" />
<TblWords ClientCodeWordPosition="04134932__4" StrFull=" A8 HYBRID" StrShort="&amp;#x20;A8 HYBRID &amp;#x20;" />
<TblWords ClientCodeWordPosition="04134932_A8_2" StrFull=" A8 HYBRID" StrShort="&amp;#x20;A8 HYBRID &amp;#x20;" />
<TblWords ClientCodeWordPosition="04134932_HYBRID_3" StrFull=" A8 HYBRID" StrShort="&amp;#x20;A8 HYBRID &amp;#x20;" />

Open in new window

0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41756143
Okay, let try another approach. This one replaces all &#x20; 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,'&#x20;','') + ' '
			FROM Filtered I
			WHERE I.clientcode = tblwords.clientcode
			ORDER BY i.WordPosition
			FOR XML PATH('')))

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41756145
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
0
 

Author Comment

by:PeterBaileyUk
ID: 41756147
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	&#x20;A8 HYBRID &#x20;	NULL
04134932__4	A8 HYBRID	&#x20;A8 HYBRID &#x20;	NULL
04134932_A8_2	A8 HYBRID	&#x20;A8 HYBRID &#x20;	NULL
04134932_HYBRID_3	A8 HYBRID	&#x20;A8 HYBRID &#x20;	NULL

Open in new window

0
 

Assisted Solution

by:PeterBaileyUk
PeterBaileyUk earned 0 total points
ID: 41756172
ok using your idea I did this outside of that particular sql query

use Dictionary


update TblWords
set StrShort=REPLACE(StrShort,'&#x20;','')

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41756182
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.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41756189
Strange that it didn't work in the original query but glad that you sorted it out.
Cheers
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question