Sql Query通过预先添加文本来更新数据

I have a table with following data :-

我有一张包含以下数据的表格： -

I want to change data of SetType Column as 'CATEGORY_3'. So the number will remain as it is, i need to prepend the number with text 'CATEGORY_'.

我想将SetType列的数据更改为“CATEGORY_3”。所以数字将保持原样，我需要在文本'CATEGORY_'前加上数字。

so SetType values will be like following :-

所以SetType值如下： -

CATEGORY_3
CATEGORY_3
CATEGORY_5
CATEGORY_5

How to update data by prepeding predefined text by SQL Query?

如何通过SQL Query预定义文本来更新数据？

5 个解决方案

#1

You use update. Here is one method:

您使用更新。这是一种方法：

update t
    set SetType = replace('Category_@val', '@val', SetType);

This assumes that SetType is a character field. If not, you will need to alter the type of the column before making such a change.

这假设SetType是一个字符字段。如果没有，您需要在进行此类更改之前更改列的类型。

#2

you can use CONCAT.

你可以使用CONCAT。

update table_name set SetType = CONCAT('CATEGORY_', SetType)

CONCAT will simply append strings to one another and you can use multiple strings in argument by separating it with , .

CONCAT只是简单地将字符串附加到另一个上，你可以在参数中使用多个字符串，用它分隔。

#3

Use UPDATE statement, But if your SetType columns Data Type is Int then first convert it into varchar.

使用UPDATE语句，但是如果您的SetType列数据类型是Int，则首先将其转换为varchar。

   UPDATE your_tablename 
   SET SetType = 'CATEGORY_'+ CONVERT(NVarchar(2),SetType)

#4

Use UPDATE statement of all rows :

使用所有行的UPDATE语句：

UPDATE your_tablename SET SetType = 'CATEGORY_'+SetType

IF SetType datatype is not in VARCHAR means use below query :

IF SetType数据类型不在VARCHAR中意味着使用以下查询：

UPDATE your_tablename SET SetType = 'CATEGORY_'+CAST(SetType AS VARCHAR)

#5

If SetType is currently an integer type, it is probably an int for a reason. If you only need certain queries to prepend 'Category_' modify your select queries instead of the updating the table.

如果SetType当前是整数类型，则原因可能是int。如果您只需要某些查询来添加“Category_”，请修改您的选择查询，而不是更新表。

select 
    SetName
  , SetType = convert(varchar(20),('Category_'+convert(varchar(11),SetType)))
  , FieldValue1
from t

#1