Question

I want to update a row which is in format like this ABC - DEF - GHI - ABD,
Using a cmd like this UPDATE tbl SET col = Replace(col,@var, ) WHERE Pk=something. How can I remove the trailing or leading hyphens? For exmaple if @var is GHI, after replace, row value should be ABC-DEF-ABD or if @var is ABC then value should be DEF - GHI - ABD.

Answer 1

首先,完全同意上述意见。改变数据ema,在数据库一栏中不储存有限价值清单。

但是,如果无法做到这一点,请说明:

Update Table Set 
   colName = Replace(Replace(colName,  -   + @var,   ), @var +   - ,   )
Where pk = @pk

Answer 2

添加外加——在座尾,用所附的干ash取代@var,然后拆除拖车——如果你重新做的话。

UPDATE tbl
    SET col = LEFT(REPLACE(col +   -  , @var+  -  ,   ), LEN(col)-6)
    WHERE Pk = something

Answer 3

如何:

UPDATE tbl SET col = replace(replace(col, @var +   –  ,   ),   –   + @var,   )

内部取代所有中间和“牵头”的事例,外部取而代之的是任何“最终”的事例。

如果有单例,例如GHI,则添加一个外在字:

UPDATE tbl SET col = replace(replace(replace(col, @var +   –  ,   ),   –   + @var,   ), @var,   )

Answer 4

您可以使用:

-- 1: replace(col,@var,   ) => your replacement
-- 2: replace(#1,   -  ,  - ) => remove spaces
-- 3: replace(#2,  -  ,   ) => remove first hyphen and space
-- 4: replace(#3,   - ,   ) => remove last hyphen and space

UPDATE tbl
   SET col = replace(replace(replace(replace(col,@var,   ),   -  ,  - ),  -  ,   ),   - ,   )        
 WHERE Pk=something

友情链接