如何在SQL Developer的列值中删除隐藏的双引号 [英] How to remove hidden double quotes in column values of SQL Developer

问题描述

我表中的某些列值都用双引号引起来，我需要删除这些双引号.问题是，我无法在SQL Developer(版本4.2.0.16.260)中查看双引号符号.但是，当我将数据复制到Notepad ++时，会出现双引号.我尝试instr来获取双引号的位置，其返回结果0.我也尝试将双引号替换为空白，但是没有将它们删除.

Some of the column values in my table are enclosed within double quotes and I need to remove those double quotes. The problem is, I am not able to view the double quotes symbol in SQL Developer (version 4.2.0.16.260). But when I copy the data to Notepad++, there I am getting double quotes. I tried instr to get the position of double quotes, its returning the result 0. Also I tried to replace double quotes with blank, but they are not being removed.

下面是复制到Notepad ++时看到的数据.显示2条记录.我正在尝试从这两个值中获取不同的值.但是我不能.

Below is the data I see when copied to Notepad++. 2 records are displayed. I'm trying to take the distinct value from these 2 values. But I am not able to.

"Testdata
"

Testdata

推荐答案

数据库表中的列值没有双引号.

The column value in the database table does not have double quotes.

从结果网格复制数据时，SQL Developer会将它们添加为复制操作的一部分，以帮助您. (也许有一种方法可以要求它不这样做，但我无法立即看到它.)之所以这样做，是因为您得到的第一个值具有尾随换行符.如果可以，我可以复制您看到的内容:

When you copy the data from the results grid, SQL Developer is adding them as part of the copy operation, to help you out. (There's probably way to ask it not to, but I can't immediately see one.) It's doing that because the first value you're getting has a trailing new line character. I can duplicate what you're seeing if I do:

select 'Testdata' || chr(10) from dual
union all
select 'Testdata' from dual;

如果我以脚本身份运行，则脚本输出窗口将显示:

If I run as a script, the script output window shows:

'TESTDATA
---------
Testdata
Testdata

这里换行符丢失了，从那里复制和粘贴不会保留换行符.如果我以语句形式运行，则查询结果窗口中的数据看起来是相同的:

Here the newline is lost, and copy and pasting from that doesn't preserve it. If I run as a statement the data in the query result window looks the same:

但是，如果我从网格中复制该数据并将其粘贴(在任何地方，不仅在Notepad ++中)，我还会看到:

but if I copy that data from the grid and paste it (anywhere, not just in Notepad++) I also see:

"Testdata
"
Testdata

...因此保留了换行符，并用双引号将其括起来，因此粘贴到其中的任何内容(我猜这都是针对Excel的)都理解它是单个值，包括该换行符. /p>

... so the newline is preserved, and is enclosed in double-quotes so whatever it is pasted into (I'm guessing this is targeted at Excel) understands that it is a single value, including that newline character.

我正在尝试从这两个值中获取不同的值

Im trying to take the distinct value from these 2 values

问题在于它们实际上并没有区别；一个有换行符，另一个没有换行符.

The problem is that they are not, in fact, distinct; one has a newline, the other does not.

如果您想忽略它们并将它们视为相同，可以修剪尾随的换行符:

If you want to ignore that and treat them as the same you can trim off the trailing newline:

select distinct rtrim(col, chr(10))
from your_table;

具有相同示例数据的演示

Demo with the same sample data:

-- CTE for sample data
with your_table (col) as (
  select 'Testdata' || chr(10) from dual
  union all
  select 'Testdata' from dual
)
select col
from your_table;

COL      
---------
Testdata
Testdata

-- CTE for sample data
with your_table (col) as (
  select 'Testdata' || chr(10) from dual
  union all
  select 'Testdata' from dual
)
select distinct rtrim(col, chr(10)) as col
from your_table;

COL      
---------
Testdata

这篇关于如何在SQL Developer的列值中删除隐藏的双引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！