SQL Replace comma in results without using replace

Question

I feel like this should be simple enough to do, but have not found any solutions that didn't use replace so far. I have the following select statement I am running, and for some of the columns there are commas separating the values. I would like to replace these commas with semicolons, however I only want to do it in the select statement. I don't want it to alter the values in the tables at all. This is not a one off statement either, or I'd just replace all the commas with semicolons and then revert back.

SELECT a.Category_Id, a.Category_Name, ISNULL(b.Category_Alias, '') as Category_Alias, 
ISNULL(b.SUPPORT_NAMES, '') as SUPPORT_NAMES
FROM Categories a
    INNER JOIN CategoryInfo b on b.Category_Id=a.Category_Id

For the Category_Alias column, the records are actually stored like CS, Customer Support and I want that to show up as CS; Customer Support just for the select statement.

Out of interest, why not use REPLACE, as it does exactly what you're asking for? — Alan
– Alan, Commented Dec 1, 2014 at 16:20

Alan · Accepted Answer · 2014-12-01 16:24:06Z

23

I believe you may be confused as to what the REPLACE function is doing. You can use REPLACE within your SELECT statement without altering the data in the database:

SELECT REPLACE(MyField, ',', ';') AS NewFieldName
FROM MyTable

answered Dec 1, 2014 at 16:24

Alan

3,0022 gold badges17 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gvee Over a year ago

Don't forget the As keyword! Can be a little confusing to some without.

huMpty duMpty · Accepted Answer · 2014-12-01 16:23:48Z

0

I believe you don't want to replace the value physically in the table, but ok to replace on select

So you can

Select REPLACE(ColumnName,',',';') 
From TableName

answered Dec 1, 2014 at 16:23

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

Comments

Kenneth Aalberg · Accepted Answer · 2014-12-01 16:23:59Z

0

Most SQL servers implement an inline replace function. Most of them are named replace(), and can also be used in a select statement. Example from MySQL:

SELECT field, REPLACE(field,',',';') FROM my_table;

answered Dec 1, 2014 at 16:23

Kenneth Aalberg

1266 bronze badges

1 Comment

gvee Over a year ago

Check your Syntax on Replace() - only 2 parameters?

Collectives™ on Stack Overflow

SQL Replace comma in results without using replace

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related