Menu
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.
556
To replace dot with comma on SELECT, you can use REPLACE().
The REPLACE built-in function does not support patterns or wildcards; only LIKE and PATINDEX do. Assuming that you really just want the simple single-character replacement as shown in the question, then you can call REPLACE twice, one nested in the other, as follows: SELECT REPLACE( REPLACE('A B x 3 y Z x 943 yy!
You can use the INSERT OR REPLACE statement to write new rows or replace existing rows in the table. The syntax and behavior of the INSERT OR REPLACE statement is similar to the INSERT statement. Unlike the INSERT statement, the INSERT OR REPLACE statement does not generate an error if a row already exists.
Using the REPLACE() function will allow you to change a single character or multiple values within a string, whether working to SELECT or UPDATE data.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
