I've imported a tab delimited into excel. I've got a column with a data type of VARCHAR2(255) dumped from an Oracle database.
我已经导入了一个分隔为excel的制表符。我有一个从Oracle数据库转储的数据类型为VARCHAR2(255)的列。
The data formatted in the column has data that look like this: (colon separated values)
列中格式化的数据具有如下所示的数据:(冒号分隔值)
info 1: info 2: info 3
After importing the file to excel worksheet, I have a few records where there is a problem in the formatting. Basically white spaces in between colons, causing the problems with the tabs.
将文件导入excel工作表后,我有一些记录格式存在问题的记录。冒号之间基本上是空白区域,导致标签出现问题。
The bad record would look like this. The record imports, but that data is misaligned within the spreadsheet.
坏记录看起来像这样。记录导入,但该数据在电子表格中未对齐。
info<...whitespace here...>1: info 2: info 3
Is there a way in Oracle syntax, sql that I can somehow select that column and remove the embedded white spaces so that it wouldn't break my tab import?
有没有办法在Oracle语法,sql中,我可以以某种方式选择该列并删除嵌入的空格,以便它不会破坏我的选项卡导入?
3 个解决方案
#1
1
This is just a pointer. If I understood you correctly, you want to replace tabs(\t) with in selected column with spaces(\b) so that tab delimited import works in excel.
这只是一个指针。如果我理解正确,你想用选中的列替换选项卡(\ t)和空格(\ b),以便制表符分隔导入在excel中工作。
You may use replace funcation to remove tab. (or any other character as it may apply)
您可以使用替换功能删除选项卡。 (或任何其他可能适用的角色)
Example:
SELECT REPLACE(YOUR_COLUMN_NAME,CHR(9),' ') FROM ...
Assuming this meets your data requirements, this would replace tabs with spaces (or any character you mention instead of ' ')
假设这符合您的数据要求,这将用空格(或您提到的任何字符而不是'')替换制表符
#2
1
This might work for you:
这可能对你有用:
SELECT regexp_replace('info :1 info 2: info 3','[[:space:]]{2,}', ' ') FROM dual;
Gives:
info :1 info 2: info 3
信息:1信息2:信息3
Assuming you want to leave one space between 'info' and ':'
假设你想在'info'和':'之间留一个空格
#3
0
problem: Basically white spaces in between colons
问题:冒号之间基本上是空白区域
If the "data" (I use the term loosely, given the dreck you're working with) do not contain spaces, you could use the replace function to replace SPACE with "" (empty string) in a UDF. Or you could replace all instances of SPACE:COLON and COLON:SPACE with COLON.
如果“数据”(我使用松散的术语,给定你正在使用的dreck)不包含空格,你可以使用replace函数在UDF中用“”(空字符串)替换SPACE。或者您可以用COLON替换所有SPACE实例:COLON和COLON:SPACE。
#1
1
This is just a pointer. If I understood you correctly, you want to replace tabs(\t) with in selected column with spaces(\b) so that tab delimited import works in excel.
这只是一个指针。如果我理解正确,你想用选中的列替换选项卡(\ t)和空格(\ b),以便制表符分隔导入在excel中工作。
You may use replace funcation to remove tab. (or any other character as it may apply)
您可以使用替换功能删除选项卡。 (或任何其他可能适用的角色)
Example:
SELECT REPLACE(YOUR_COLUMN_NAME,CHR(9),' ') FROM ...
Assuming this meets your data requirements, this would replace tabs with spaces (or any character you mention instead of ' ')
假设这符合您的数据要求,这将用空格(或您提到的任何字符而不是'')替换制表符
#2
1
This might work for you:
这可能对你有用:
SELECT regexp_replace('info :1 info 2: info 3','[[:space:]]{2,}', ' ') FROM dual;
Gives:
info :1 info 2: info 3
信息:1信息2:信息3
Assuming you want to leave one space between 'info' and ':'
假设你想在'info'和':'之间留一个空格
#3
0
problem: Basically white spaces in between colons
问题:冒号之间基本上是空白区域
If the "data" (I use the term loosely, given the dreck you're working with) do not contain spaces, you could use the replace function to replace SPACE with "" (empty string) in a UDF. Or you could replace all instances of SPACE:COLON and COLON:SPACE with COLON.
如果“数据”(我使用松散的术语,给定你正在使用的dreck)不包含空格,你可以使用replace函数在UDF中用“”(空字符串)替换SPACE。或者您可以用COLON替换所有SPACE实例:COLON和COLON:SPACE。