SQL Server-将字符串添加到文本列(等效于concat)
技术问答
316 人阅读
|
0 人回复
|
2023-09-14
|
如何在SQL Server的列中添加字符串?
/ Y/ K; w6 s8 t; U5 [5 m; zUPDATE [myTable] SET [myText]=' '+[myText]
; D. T/ i& u/ ?$ t) v那是行不通的:9 ^4 t" [3 Y* |0 s
/ a( I# N, C3 C! a G
数据类型varchar和text在add运算符中不兼容。& H- h3 \7 |# y, R3 M
! v: N* h+ |, |1 j5 c2 w/ E您将在MySQL上使用concat,但是如何在SQL Server上使用concat?" z" n/ _7 z7 M
; A4 v: K: D" @% n( x
解决方案:
8 P, H8 F9 P' i4 S; W3 A+ u * L- Z2 ]* n" v3 d
6 d$ j2 |2 [2 F
* C. a' M% s3 l 如前所述,最好将列的数据类型设置为nvarchar(max),但如果无法做到,则可以使用cast或convert进行以下操作:1 U/ [* E; D( m- g0 `9 \
-- create a test table
& e1 K, E( `/ Wcreate table test (
( E- w( ^0 ]8 i: B a text; z$ P/ I4 ^( |
) 8 \+ ?4 c- C" ~ @# M9 u
-- insert test value5 D+ F7 g8 u0 D* U9 F* {, k
insert into test (a) values ('this is a text'). i: G2 s! @3 @$ C: ~: d
-- the following does not work !!!
: f% m3 Z: F0 \. y: H8 ~. Tupdate test set a = a + ' and a new text added'
) @" Z8 a- f9 Z-- but this way it works: 9 ^ m$ D: Q3 j0 J) K6 i
update test set a = cast ( a as nvarchar(max)) + cast (' and a new text added' as nvarchar(max) )
/ z }5 g! Q+ J& O! C-- test result! d: G% E1 m4 x% Y# i4 G
select * from test6 q* F# I: s4 Z" ~* o
-- column a contains:2 [; D* y. B. X @$ }
this is a text and a new text added) _4 w6 H* B h
希望能有所帮助 |
|
|
|
|
|