SQLTeam.com | Weblogs | Forums

Replace prefix with other value in select statement

sql2008

#1

I have table called myAlert and column MyRiskview (Other columns are also present in the table). Below is the sample data for MyRiskview column.

MyRiskview
Analysis:ABC
Analysis:PQR
Analysis:XYZ
USA
India

Below is the expected output:

MyRiskview
My Analysis
My Analysis
My Analysis
USA
India

To summarize wherever we find Analysis: in the row it will replace with "My Analysis" in the table rest of the data should same. In SELECT STATEMENT ONLY NO UPDATE OR ALTER STATMENT. We need to filter this data for showing purpose only. Please reply ASAP.

Thanks in Advance.


#2

case when myriskview like 'Analysis:%' then 'My Analysis' else myriskview end


#3

Tried we cant use like in case when..Can you give me example .. I tried but no luck.


#4

You can't use LIKE in one format of CASE, but you can in the other.

You can write:
CASE WHEN THEN <new_value> WHEN <value2< THEN <new_value2> etc.
That format doesn't allow LIKE.

But you can also use format:
CASE WHEN <column_expression> THEN <new_value> WHEN <col_expr_2> THEN <new_value2> etc.
That format does allow LIKE.

SELECT CASE WHEN MyRiskview LIKE 'Analysis:%' THEN 'My Analysis' ELSE MyRiskview END AS MyRiskview, ...
FROM table_name
...