How to replace a string of a column with space or ''

Question

I have a columns in tbl in which i have values like

X Y Z 1 4 123/1232221 2 3 234/3454455 3 2 UNKNOWN 123234

Now what i want is when i trigger a query 'UNKNOWN' will replace with space

Like below:

X Y Z 1 4 123/1232221 2 3 234/3454455 3 2 123234

Any help will be appreciated.

Thanks In advance !!!

I put your code waiting for output as it is very large database it will take 2 hours to run it completly — amitesh
– amitesh, Commented May 12, 2017 at 13:15

LukStorms · Accepted Answer · 2017-05-12 12:49:25Z

2

To correct the table :

update tbl set z = replace(z,'UNKNOWN','') where z like '%UNKNOWN%';

To query the table with the UNKNOWN replaced

select x, y, replace(z,'UNKNOWN','') as z from tbl;

answered May 12, 2017 at 12:49

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

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

1 Comment

LukStorms Over a year ago

Uhm, so is that good or bad news? If you ran that first update statement then yeah, there shouldn't be any "UNKNOWN" in that z field in the table.

jfs · Accepted Answer · 2017-05-12 12:55:33Z

Would this suffice? UPDATE table SET column_name = REPLACE(column_name, 'UNKNOWN', ' ');

zlidime · Accepted Answer · 2017-05-13 10:11:21Z

use replace function, REPLACE ( string_expression , string_pattern , string_replacement ) in your case string expression in value of your column z. If in some row column doesn't have value with string 'UNKNOWN', value of that column in row will remain the same. you can find more on https://learn.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql

select x,y,replace(z,'UNKNOWN',' ') as z from you_table

or if you want to update

update your_table set z=replace(z,'UNKNOWN',' ')

From review queue: May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post.

Collectives™ on Stack Overflow

How to replace a string of a column with space or ''

3 Answers 3

1 Comment

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Related