asked on
<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('')))
ee.JPG
ASKER
ASKER
ASKER
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('')))
ASKER
ClientCodeWordPosition StrFull StrShort StrShortTag
13414B__4 VN1700 CLASSIC TOURER <Word>VN1700 </Word><Word>CLASSIC </Word><Word>TOURER </Word><Word> </Word> NULL
ASKER
ASKER
<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;" />
ASKER
ASKER
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
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
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:
Open in new window