How to remove the extra data

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

i have use this query to get data.

SELECT t1.HId,
commands=STUFF   
(   
     (   
       SELECT DISTINCT ',' + t2.commands  
       FROM history_detail t2
       WHERE  t1.HId = t2.History_HID    
       FOR XML PATH('')   
     ),1,1,''   
)   
FROM history t1,  history_detail t2  
GROUP BY HId

but out put is this of commands columns

Send("this is your first testcase")

,Sleep(3000)

`�xD;`
,WinActive("notepad")
`�xD;`
,WinClose("notepad")
`�xD;`
,WinWaitActive("notepad")`�xD;`

i wnat to remove `�xD;` from data which is coming together with it.

What I have tried:

i have try but the extra data is not removed

Posted 3-Apr-18 1:07am

Member 11776570

Updated 4-Apr-18 7:00am

v5

Add a Solution

2 solutions

Solution 1

You need to look at your data: specifically at what is stored in your history_detail.commands column.
It looks like whatever put the data in there has added some rubbish to teh end - you may be able to remove it using the SQL CHARINDEX and SUBSTRING functions but you need to find out exactly what it is first.
CHARINDEX (Transact-SQL) | Microsoft Docs[^]
SQL Server SUBSTRING() Function[^]

Me? I'd look at the code that inserted the data, and see if I could either prevent the corruption, or add it as a different column.

Posted 3-Apr-18 1:41am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-04-04T07:02:00

You're getting XML-encoded output. Change the query to:

SQL

SELECT 
    t1.HId,
    commands = STUFF   
    (   
        (   
            SELECT DISTINCT ',' + t2.commands  
            FROM history_detail t2
            WHERE  t1.HId = t2.History_HID    
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)'), 1, 1, ''   
    )   
FROM 
    history t1
;

Add ,TYPE after PATH('');
Add .value('.', 'varchar(max)') after the nested query;

There's no need to include history_detail in the outer query; removing it will also remove the need for the GROUP BY clause.

Concatenating Row Values in Transact-SQL - Simple Talk[^]