CODEWITHSUNDEEP
sqlsql-serverselectreplace
Itération 122442
Itération 122442

Reputation: 2962

Replace in a select without adding a new column?

Consider this data:

col1  | col2
kk|kk | kjkjk

I want to replace the pipe in the col1 by a space during a select. Expected output:

col1  | col2
kk kk | kjkjk

So far I have this:

SELECT  *,  
replace(col1,'|',' ') as col1
FROM TABLE

However, this generates the following:

col1  | col2  | col1
kk|kk | kjkjk | kk kk

I do not want the third column but instead, replace the content of the first column by the result of the replace statement. Is this possible?

Upvotes: 0

Views: 533

Answers (2)

Thom A
Thom A

Reputation: 95554

Don't use SELECT *. That means return every column from the objects in the FROM, which includes Col1. You don't "replace" columns in a SELECT, you define then. SELECT *, replace(col1,'|',' ') as col1 effectively mean SELECT Col1, Col2, replace(col1,'|',' ') as col1.

Define your columns:

SELECT REPLACE(col1,'|',' ') AS col1,
       Col2
FROM dbo.[TABLE];

Upvotes: 1

Mureinik
Mureinik

Reputation: 311028

You'll have to lose the * wildcard and explicitly list out the columns you want to query:

SELECT REPLACE(col1, '|', ' ') AS col1, col2
FROM   mytable

Upvotes: 1

Related Questions