数据转换求助，高手请进，在线等
select Web_CustomerID from CustomerData
在Web_CustomerID字段中有如下数据（排除前后多余的0之后再取6位数字
0000213612
0000212628
2185790000
2187760000
0002126310
0002126330
结果
213612
212628
218579
218776
212631
212633
SQL如何写，在线等
[解决办法]
select replace(Web_CustomerID, "0 ", " ") as Web_CustomerID from CustomerData
[解决办法]
如果只有 0-9 这类的数字, 则可以用:
SELECT REVERSE(RIGHT(CONVERT(bigint, REVERSE(CONVERT(bigint, Web_CustomerID))), 5))
FROM CustomerData
[解决办法]
create table t1(c varchar(20))
insert t1
select '0000213612 '
union all select '0000212628 '
union all select '2185790000 '
union all select '2187760000 '
union all select '0002126310 '
union all select '0002126330 '
go
select c=left(REVERSE(cast(REVERSE(cast(c as bigint)) as bigint)),6) from t1
go
drop table t1
/*
c
------------------------
213612
212628
218579
218776
212631
212633
*/
[解决办法]
参考leo_lesley(leo) ( )的方法，写一下：
create table t1(c varchar(20))
insert t1
select '0000213612 '
union all select '0000212628 '
union all select '2185790000 '
union all select '2187760000 '
union all select '0002126310 '
union all select '0002126330 '
go
--select c=left(REVERSE(cast(REVERSE(cast(c as bigint)) as bigint)),6) from t1
--中间出现0的就错了
--修改后方法：
SELECT CAST(REVERSE(CAST(CAST(REVERSE(c) AS bigint) AS nvarchar(50))) AS bigint) FROM t1
go
drop table t1
/*
c
------------------------
213612
212628
218579
218776
212631
212633
*/
[解决办法]
select replace(Web_CustomerID, '0 ', ' ') Web_CustomerID from CustomerData
返回：
Web_CustomerID
-------------------
213612
212628
218579
218776
212631
212633
（所影响的行数为 6 行）
