用另一个字符替换嵌入空格的 SQL 命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9365400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Command to replace embedded spaces with another character
提问by Adk Chris
I have a relational database with several fixed length character fields. I need to permanently replace all the embedded spaces with another character like -so JOHN DOEwould become JOHN-DOEand ITSY BISTSY SPIDERwould become ITSY-BISTSY-SPIDER. I can search before hand to make sure there are no strings that would conflict. I just need to be able to print the requested files with no embedded spaces. I would do the replacement in the C code but I want to make sure that there is never a future case where there is a JANE DOEand JANE-DOEin the DB.
我有一个包含几个固定长度字符字段的关系数据库。我需要与另一个字符永久取代所有的嵌入式空间，如-因此JOHN DOE将成为JOHN-DOE和ITSY BISTSY SPIDER将成为ITSY-BISTSY-SPIDER。我可以事先搜索以确保没有会发生冲突的字符串。我只需要能够打印没有嵌入空格的请求文件。我会在 C 代码中进行替换，但我想确保将来永远不会出现数据库中存在JANE DOEandJANE-DOE的情况。
By the way I have already made sure that there are no strings with more than one consecutive embedded space or leading spaces only trailing spaces to fill the fixed length fields.
顺便说一下，我已经确保没有包含多个连续嵌入空格的字符串或前导空格只有尾随空格来填充固定长度字段。
Edit: thanks for all the help!
编辑：感谢所有帮助！
It looks like when I cut & pasted my question from Word to StackOverflow the trailing spaces got lost so the meaning my question was lost a bit.
看起来当我将我的问题从 Word 剪切并粘贴到 StackOverflow 时，尾随空格丢失了，所以我的问题的意思有点丢失了。
I need to replace only the embedded spaces not the trailing spaces!
我只需要替换嵌入的空格而不是尾随空格！
Note: I am using middle dot to stand in for spaces that don't show well.
注意：我使用中间点来代表不能很好显示的空间。
Using:
使用：
SELECT REPLACE(operator_name, ' ', '-') FROM operator_info ;
the string JOHN·DOE············became JOHN-DOE------------.
字符串JOHN·DOE············变成了JOHN-DOE------------.
I need JOHN-DOE············.
我需要JOHN-DOE············。
I am thinking I need to use aliasing and the TRIMcommand but not sure how.
我想我需要使用别名和TRIM命令，但不确定如何使用。
回答by Timeout
With whatever REPLACE function is built into your particular database.
使用内置于您的特定数据库中的任何 REPLACE 函数。
MySQL: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace
MySQL：http: //dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace
Oracle: http://psoug.org/reference/translate_replace.html
甲骨文：http: //psoug.org/reference/translate_replace.html
SQLServer: http://msdn.microsoft.com/en-us/library/ms186862.aspx
SQLServer：http: //msdn.microsoft.com/en-us/library/ms186862.aspx
Edits below based on your comment.
根据您的评论在下面进行编辑。
I've done this in SQLServer syntax so please modify the example as needed. The first example really breaks down what's going on and the second one bunches it all into a single ugly query :D
我已经在 SQLServer 语法中完成了此操作，因此请根据需要修改示例。第一个示例确实分解了正在发生的事情，第二个示例将其全部组合成一个丑陋的查询：D
@outputin this case contains your final value.
在这种情况下，@output包含您的最终值。
DECLARE @input VARCHAR (100) = ' some test ';
DECLARE @trimmed VARCHAR (100);
DECLARE @replaced VARCHAR (100);
DECLARE @output VARCHAR (100);
-- Get just the inner text without the preceding / trailing spaces.
SET @trimmed = LTRIM (RTRIM (@input));
-- Replace the spaces *inside* the trimmed text with a dash.
SET @replaced = REPLACE (@trimmed, ' ', '-');
-- Take the original text and replace the trimmed version (with the inner spaces) with the dash version.
SET @output = REPLACE (@input, @trimmed, @replaced);
-- Show each step of the process!
SELECT @input AS INPUT,
@trimmed AS TRIMMED,
@replaced AS REPLACED,
@output AS OUTPUT;
And as a SELECT statement.
并作为 SELECT 语句。
DECLARE @inputTable TABLE (Value VARCHAR (100) NOT NULL);
INSERT INTO @inputTable (Value)
VALUES (' some test '),
(' another test ');
SELECT REPLACE (Value,
LTRIM (RTRIM (Value)),
REPLACE (LTRIM (RTRIM (Value)), ' ', '-'))
FROM @inputTable;
回答by Philip Fourie
If you are using MSSQL:
如果您使用的是 MSSQL：
SELECT REPLACE(field_name,' ','-');
Edit:After the requirement about skipping the trailing spaces. You can try this one-liner:
编辑：在关于跳过尾随空格的要求之后。你可以试试这个单线：
SELECT REPLACE(RTRIM(@name), ' ', '-') + SUBSTRING(@name, LEN(RTRIM(@name)) + 1, LEN(@NAME))
However I would recommend that you put it into a user defined function instead.
但是，我建议您将其放入用户定义的函数中。
回答by Diego
assuming SQL Server:
假设 SQL Server：
update TABLE set column = replace (column, ' ','-')
回答by Dharmendra
SELECT REPLACE(field_name,' ','-');
Edit:After the requirement about skipping the trailing spaces. You can try this one-liner:
编辑：在关于跳过尾随空格的要求之后。你可以试试这个单线：
SELECT REPLACE(RTRIM(@name), ' ', '-') + SUBSTRING(@name, LEN(RTRIM(@name)) + 1;
