SQL_Sever2005字符串的应用

Question

SQL_Server2005中，表A的字段B有数据"idhaksjdnksjdnjsdjgbjdhjksdvbhjksdbhdkjhdcbjdscfb",我想只提取前面指定个数的字符（比如取20个），然后剩下的字符就用三个...来代替。怎样做啊？？？

Answer 1

declare @col varchar(100)='idhaksjdnksjdnjsdjgbjdhjksdvbhjksdbhdkjhdcbjdscfb'
select LEFT(@col,20)+'...'/*
-------------------------------------------
idhaksjdnksjdnjsdjgb...(1 行受影响)

Answer 2

declare @t table (col varchar(49))
insert into @t
select 'idhaksjdnksjdnjsdjgbjdhjksdvbhjksdbhdkjhdcbjdscfb' union all
select 'gbjdhjksdvbhjksdbhdkjhdcbjdscfbrr' union all
select 'ff' union all
select 'gbcfbeejdhjksdvbhjksdbhdkjhdcbjdscfbee'select col=
case when len(col)>20 then left(col,20)+'...' else col end from @t
/*
col
-------------------------------------------------
idhaksjdnksjdnjsdjgb...
gbjdhjksdvbhjksdbhdk...
ff
gbcfbeejdhjksdvbhjks...
*/

Answer 3

select left(b,20) + '...' from a where len(b) >= 20