SQL Command to replace embedded spaces with another character

Question

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 DOE would become JOHN-DOE and ITSY BISTSY SPIDER would 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 DOE and JANE-DOE in the DB.

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.

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------------.

I need JOHN-DOE············.

I am thinking I need to use aliasing and the TRIM command but not sure how.

Answer 1

With whatever REPLACE function is built into your particular database.

MySQL: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

Oracle: http://psoug.org/reference/translate_replace.html

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

@output in this case contains your final value.

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.

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;

Answer 2

If you are using 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.

Answer 3

assuming SQL Server:

update TABLE set column = replace (column, ' ','-')